Message to know that program is updating!

[haerion]

Hi all, I'm trying to get a message(Updating, please wait!) on my form while access is running a macro on excel that is a on open macro. The macro is working fine for excel, but I don't get anything in access to tell me that the program is doing a macro, and the message only appear at the end, even if the line is the first one in the code of the command button. And it will show off right at the end after the excel macro is finish. Also the code to open the excel file is a followlink.

Any idea how to do this?

Thanks

Haerion

 

[PHV]

Why not posting the code of the command button ?
I guess you may consider the DoEvents function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[haerion]

Hi PHV,

Thanks for responding to my post, here is the code i got on my command button:

Private Sub btnUpdateVersyss_Click()

'text on the form saying that it is actually updating the
'information, so patience would be a good idea :)
Me.txtOnUpdate.Visible = True

FollowHyperlink "\\mtlsns1\data\dfr\Macro Trane Order.xls"

DoCmd.RunSQL "DELETE * FROM SALE24V1"
DoCmd.RunSQL "DELETE * FROM SALE24V2"
DoCmd.RunSQL "DELETE * FROM INVDFR"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SALE24V1", "\\mtlsns1\data\DFR\SALE24V1.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SALE24V2", "\\mtlsns1\data\DFR\SALE24V2.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "INVDFR", "\\mtlsns1\data\DFR\INVDFR.xls", True
DoCmd.OpenQuery "Update - INVDFR"
DoCmd.OpenQuery "Update - SALE24V1"
DoCmd.OpenQuery "Update - SALE24V2"

Me.txtOnUpdate.Visible = False

MsgBox "The update is complete!"
End Sub

The excel file "Macro Trane Order" got a macro on it on the On Open that update the excel file SALE24V1, SALE24V2, INVDFR with new information from a txt file. Problem is that it does the followlink before showing the txtOnUpdate and after that does everything while still in excel, can't even got excel to close properly... Excel staying open is not the real problem, the problem is more that I want something to say that it is updating while staying on the access program.

Thanks for your help.

Haerion

 

[PHV]

you may try this:
...
Me.txtOnUpdate.Visible = True
[!]doEvents[/!]
FollowHyperlink "\\mtlsns1\data\dfr\Macro Trane Order.xls"
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[haerion]

OK, now the text appear, but I still got stuck on the excel sheet that stay out front, is there a way in vba to come back to access?