Automation corrupting Excel file (1 Viewer)

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:48
Joined
Aug 30, 2003
Messages
36,118
Okay, I've stumped myself. I'm creating an automated routine to grab an attached Excel file out of an email and process it. I've done several successfully. The process up to saving the attachment is fine:

outAttachment.SaveAsFile sFile

If I go to Windows Explorer I can open the file. If I let the import routine run, it throws a runtime error 1004. The failing line is the third (using late binding):

Set xl = CreateObject("excel.application")
strFile = "C:\AccessAp\TravelDayTransactions.xlsx"
xl.Workbooks.Open (strFile)

If I then go back into Explorer, I can't open the file anymore, leading me to believe the automation corrupts the file. Anybody spot the bonehead error I must have made?
 

spikepl

Eledittingent Beliped
Local time
Today, 23:48
Joined
Nov 3, 2010
Messages
6,144
A wild stab in the dark: timing issue? Do you attempt to do something with the file right after saving it, but the poor "thing" is not done yet? This could be tested by steppign through with the debugger.
 

boblarson

Smeghead
Local time
Today, 15:48
Joined
Jan 12, 2001
Messages
32,059
A wild stab in the dark: timing issue? Do you attempt to do something with the file right after saving it, but the poor "thing" is not done yet? This could be tested by steppign through with the debugger.
That was my initial thought too. I have experienced that error from someone who was saving to a PDF from Citrix to their local machine when they were supposed to be doing it on the Citrix server. The save was going so slow that, when they tried to open it, it gave an invalid file error.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:48
Joined
Aug 30, 2003
Messages
36,118
Thanks for the thoughts. Interesting. The first line saving the file is in one function, the next 3 are in the import function, normally called immediately after the save line. Putting DoEvents and/or a message box between the save and the import function call don't change the error that I get. However, if I comment out the function call to the import routine and just let the file save, then do a debug/step into and run the import function, it runs without error.

That would lend credence to the timing issue, but it's a tiny 9k file, so it doesn't take long to save, and the DoEvents/message box should have resolved that. Plus I have other automated imports that run fine without, with bigger files. Well, at least I can come up with a workaround if nobody has other thoughts.
 

boblarson

Smeghead
Local time
Today, 15:48
Joined
Jan 12, 2001
Messages
32,059
Can you put in something like this (we use it to make sure the pdf file is created before moving on):
Code:
    While Len(Dir(prmPdfName)) = 0              ' Wait for the PDF to actually exist
        DoEvents
    Wend
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:48
Joined
Aug 30, 2003
Messages
36,118
Good idea Bob, but sadly didn't work.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:48
Joined
Aug 30, 2003
Messages
36,118
Update. You guys got me thinking about timing issues, and even though what "should have" worked didn't, I removed the function call from the loop of emails (check inbox looking for certain subject line) and added this after the loop completes:

Code:
  If Len(Dir(sFile)) > 0 Then
    ImportTravelDay
    Kill sFile
  End If

and the code completes successfully. I can only speculate that the email still being open by the automation code is somehow interfering, though I have others where this works fine. This work-around will work as long as I don't get multiple emails at once, which I'm not supposed to for this vendor. The reason the code was like it was is that I have others vendors where I get multiple emails at once, so I have to fully process each before moving on to the next.

I appreciate you guys getting me on the right track!
 

Users who are viewing this thread

Top Bottom