Problem with Linking Excel Spreadsheet to Access

[RogerCooper]

Everyday, I download a list of recently received orders from a website using a Selenium script. The site always downloads the file as "Export.xlsx ". If I try to link/download it directly, I receive the error, "The wizard is unable to access information in the file <filename>. Please check that the file exists and is in the correct format."

If I open the file manually and then do a SaveAs, the file becomes linkable. I would like to avoid doing that manual step.

I have tried using VBA code, but it fails half the time because the file is not showing as existing in the folder, even with a 10 second wait.

Call DeleteFile("g:\TC_Sent.xlsx")
bot.Wait 10000
If FileExists("g:\Export.xlsx") Then
Set WB = Workbooks.Open("g:\Export.xlsx")
WB.SaveAs FileName:="g:/TC_Sent.xlsx", FileFormat:=xlOpenXMLWorkbook
Workbooks.Close
Else
MsgBox "Open g:\Export.xlsx, save manually to g:\TC_Sent.xlsx and close"
Stop
End If

I tried turning off protection from downloaded files, but it did not help.

Any ideas as to how I can reliably automate this or avoid the error when linking/downloading.

 

[theDBguy]

Just curious, would it still fail half the time if you increase the wait to 20 or 30 seconds?

 

[LarryE]

Everyday, I download a list of recently received orders from a website using a Selenium script. The site always downloads the file as "Export.xlsx ". If I try to link/download it directly, I receive the error, "The wizard is unable to access information in the file <filename>. Please check that the file exists and is in the correct format."

If I open the file manually and then do a SaveAs, the file becomes linkable. I would like to avoid doing that manual step.

I have tried using VBA code, but it fails half the time because the file is not showing as existing in the folder, even with a 10 second wait.

Call DeleteFile("g:\TC_Sent.xlsx")
bot.Wait 10000
If FileExists("g:\Export.xlsx") Then
Set WB = Workbooks.Open("g:\Export.xlsx")
WB.SaveAs FileName:="g:/TC_Sent.xlsx", FileFormat:=xlOpenXMLWorkbook
Workbooks.Close
Else
MsgBox "Open g:\Export.xlsx, save manually to g:\TC_Sent.xlsx and close"
Stop
End If

I tried turning off protection from downloaded files, but it did not help.

Any ideas as to how I can reliably automate this or avoid the error when linking/downloading.

I don't think you can link any file to ACCESS unless it is saved somewhere first. Otherwise, how could ACCESS possibly know where the file is?

 

[RogerCooper]

I don't think you can link any file to ACCESS unless it is saved somewhere first. Otherwise, how could ACCESS possibly know where the file is?

The file is saved before I link. There seems to be an unpredictable delay before I can open the Export.xlsx file, the error when I link always occurs, even the next day.

 

[Gasman]

Try copying it to another folder first.

 

[RogerCooper]

Try copying it to another folder first.

A good idea, but it doesn't help.

 

[Isaac]

forward slash where backward slash is needed

 

[Gasman]

forward slash where backward slash is needed

Seriously?

Well spotted @Isaac

 

[RogerCooper]

forward slash where backward slash is needed

It doesn't hurt to try it. Everything worked fine today.