0

I do have a weird issue here, looking for someone with an elegant solution. I use something like

 With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & exportUrl, Destination:=destCell)
        'all these have sane defaults, yes, but if the user used text to columns with something, this changes magically. So stupid
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileDecimalSeparator = "."
        .TextFileThousandsSeparator = False
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileCommaDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileConsecutiveDelimiter = False
        ' there is no way to set this to nothing, apparently.
        .TextFileOtherDelimiter = ","
        .TextFilePlatform = 65001 'UTF-8
        .Refresh BackgroundQuery:=False
    End With

To fetch data from a php web application. I'm having access keys in the URL as query parameters. If they are correct, all goes fine. If they are wrong, the php returns a http 403 page. This makes Excel raise error 1004, which I catch, all good. Now, any subsequent call to the code, with changed (and now correct) access keys stil raises 1004. Excel doesn't even fire up a http request any more according to server logs. Looks like it is caching the http response code.

As the user has to enter the keys, it can easily happen that they are wrong. I'd like to offer to enter correct ones, but than the above will make them look still wrong...

Ideas welcome! Running Excel 2016 if that makes a difference.

braX
  • 11,506
  • 5
  • 20
  • 33
Alexander Gran
  • 31
  • 3
  • Just thinking that it might be that MS is actually 100% following the logic here, that the http auth is unchanged (in that is still no http auth used), and as such the response might stick to 403. – Alexander Gran Mar 24 '18 at 21:30
  • maybe Power Query has some advanced options for that https://support.office.com/en-us/article/connect-to-a-web-page-power-query-b2725d67-c9e8-43e6-a590-c0a175bd64d8 – Slai Mar 24 '18 at 21:35
  • Actually, this is also reproducible when the reason for failure is loss of internet connectivity: Try to use a valid URL without internet, get a 1004. Re-establish Internet Connectivity: Still receive 1004. Restart Excel -> all fine. – Alexander Gran Mar 24 '18 at 21:38

1 Answers1

0

"Solution" is infact a workaround. I don't send http 403 any more, but handle the erorr in payload. This leaves only connection issues. As they should be rare, I now just ask the user to restart excel if that happens.

Alexander Gran
  • 31
  • 3