Pause VB for excel to finish

[aromano789]

Heres my situation.

I am trying to import an excel file into access. I get invalid file format with docmd.TransferSpreadsheet.

When I open the excel file and copy and past the data to a new excel file, I can import the new excel file with no problems with transferspreadsheet.

I have no control over the original excel file. Its given to me and I cant get them to fix it. (im stuck with it)

So what i did......
I created an autoexec macro in excel to copy the file to a readable excel file. I also have the excel macro close excel when completed

In my access database i want to create an import button.
I would like the import button to open my excel file with the autoexec macro, wait until excel does its thing and then do the tranferspeadsheet

is there a way to pause or wait until excel does its thing before vb runs the next line?

Or if there is an alt solution

Thanks In advance

…………………………………………………
Private Sub Command45_Click()

Shell "excel.exe PATH\ExcelConvertMacro.xls", vbNormalFocus

????.....Wait or Pause for excel to close……????

docmd.TransferSpreadsheet

End Sub
………………………………………….

 

[pbaldy]

Try one of these:

http://www.mvps.org/access/api/api0004.htm

http://www.mvps.org/access/api/api0021.htm