求助：HTTP认证失败缓存问题及Excel VBA QueryTables参数异常

Hey there, let's break down practical solutions for both your issues clearly:

This is a super common gotcha with HTTP authentication—browsers and many HTTP clients love to cache auth credentials or failure states, even when the target URL changes. Here are actionable fixes tailored to different scenarios:

• Clear cached credentials in the browser: If you're accessing the URL via a browser, head to your browser's saved passwords/credentials settings and delete entries for the target domain. For example, in Chrome: Settings > Privacy and security > Site Settings > Additional permissions > Saved passwords.
• Force no-cache headers in code requests: If you're making requests via code (like VBA), explicitly add headers to bypass auth caching. Here's how to do it with VBA's XMLHTTP:

  Dim req As Object
  Set req = CreateObject("MSXML2.XMLHTTP")
  req.Open "GET", exportUrl, False
  ' Add headers to disable caching
  req.setRequestHeader "Cache-Control", "no-cache, no-store, must-revalidate"
  req.setRequestHeader "Pragma", "no-cache"
  req.setRequestHeader "Expires", "0"
  ' Add your authentication logic here if needed
  req.send
  

• Append a unique query parameter: If the server allows it, add a timestamp or random string to the URL (e.g., ?t=1698765432) every time you request it. This tricks the client into treating it as a new resource, skipping cached auth states entirely.

Ugh, Excel's habit of "remembering" previous operation settings is so frustrating! The root issue is that Excel stores text parsing preferences globally (or per workbook) after you use Text to Columns, and these settings leak into your QueryTables. Here's the elegant fix:

• Explicitly define ALL critical parsing parameters: Never rely on default values—set every parameter that matters to your use case. Your existing code starts this, but ensure you cover all relevant options. For example:

  With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & exportUrl, Destination:=destCell)
      .TextFileStartRow = 1
      .TextFileParseType = xlDelimited
      .TextFileDecimalSeparator = "."
      .TextFileThousandsSeparator = "," ' Note: This should be a string, not False
      .TextFileTextQualifier = xlTextQualifierDoubleQuote
      .TextFileCommaDelimiter = True ' Set your actual delimiter here
      .TextFileTabDelimiter = False
      .TextFileSemicolonDelimiter = False
      .TextFileSpaceDelimiter = False
      .Refresh BackgroundQuery:=False
      .Delete ' Optional: Remove the QueryTable after loading data if not needed
  End With
  

• Reset parsing defaults programmatically: For extra safety, reset Excel's Text to Columns settings with a dummy operation before creating your QueryTable. This wipes out any leftover preferences:

  ' Use a blank cell to reset settings without affecting your data
  Dim dummyRng As Range
  Set dummyRng = destCell.Parent.Range("A1")
  dummyRng.TextToColumns Destination:=dummyRng, DataType:=xlDelimited, _
      TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, _
      Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
      FieldInfo:=Array(Array(1, xlGeneralFormat)), _
      DecimalSeparator:=".", ThousandsSeparator:=","
  

This ensures your QueryTable uses your intended parameters, regardless of past Text to Columns usage.

内容的提问来源于stack exchange，提问作者Alexander Gran