VBA calling IQY files. One works, one not

Question

I've done a project Excel VBA, which calls TWO iqy files, made in the same way from the same source Sharepoint (our internal server).

We use EXCEL 365 business. Well, suddenly One of the two iqy files stops connection and download updated data. I think at least one time it worked, and I changed nothing... I call the procedure, one sheet updates data with content of iqy_file#1, but other sheet does NOT connect, does NOT download anything... (if I empty the sheet, it stays empty, if I manually load data, no data is updated by the procedure)

Let me say this: notice that row: ThisWorkbook.Sheets("MWshp").Cells.Clear

Well, that sheet isn't cleared OR I'd suppose that it loads an "old" file, because data in the sheet REMAINS THE SAME, not reflecting changes I've done onto the sharepoint source file.

I've opened both iqy files, and they appear near the same:

WEB

1

https "our internal server"/FWASite/_vti_bin/owssvr.dll?XMLDATA=1&List={E468B74E-C89E-4573-81A8-5A879944C871}&View=5BC45208-D8E4-4E0E-BD4D-0CC72A260E35&RowLimit=0&RootFolder=%2fFWASite%2fLists%2fFWA%5fLINK%5fMW 

Selection={E468B74E-C89E-4573-81A8-5A879944C871}-5BC45208-D8E4-4E0E-BD4D-0CC72A260E35

EditWebPage=

Formatting=None

PreFormattedTextToColumns=True

ConsecutiveDelimitersAsOne=True

SingleBlockTextImport=False

DisableDateRecognition=False

DisableRedirections=False

SharePointApplication=https://our internal server/FWASite/_vti_bin

SharePointListView=5BC45208-D8E4-4E0E-BD4D-0CC72A260E35

SharePointListName={E468B74E-C89E-4573-81A8-5A879944C871}

RootFolder=/FWASite/Lists/FWA%5fLINK%5fMW

Other is very similar, of course different RootFolder

This is VBA call:

IQYFile = pathAllitems & MWquery 

    ThisWorkbook.Sheets("MWshp").Activate 

    ThisWorkbook.Sheets("MWshp").Cells.Clear 

    With ActiveSheet.QueryTables.Add(Connection:= _ 

       "FINDER;" & IQYFile, Destination:=Range("A1")) 

        .Name = "itemtable" 

        .BackgroundQuery = True 

        .TablesOnlyFromHTML = True 

        .Refresh BackgroundQuery:=False 

        .SaveData = True 

    End With

Other call is the same, of course different sheet (="AllItems") and different const (Not MWquery, but AllItemQuery) SAME PATH (=PathAllItems)

Both iqy files, if I open them directly, pop up this alert: MS Alert

I repeat, both files iqy are near the same, called in the same way, come from the same source, if I doubleclick them: BOTH open in an empty excel in the same way... BUT in my procedure one updates its sheet, other doesn't do it :-(

What should I check, now? :-)

Every file in this project IS in a trusted source

VBA calling IQY files. One works, one not

0 Answers