Excel Macros

[joemc]

Hello everyone!

I think this will be an easy one.

I want to set up a macro to run everyday so I can update my database (by executing an excel macro).

I found this VBS

Use the Run method of the Excel Application object to run Excel macros/VBA routines.

VBSTART
Sub ExcelExample
Dim xlApp
Dim xlBook

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("d:\ est.xls")

xlApp.visible = True
xlApp.Run "Macro3"

xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
VBEND
VBRun>ExcelExample

The problem that im having with it is that as soon as i open excel a pop up window appears asking me if i want to update the links (which i do), but i have to do it manually.

I tried to divide the script so I can first run excel, then press> enter and after that run the macro. The problem is that i cant seem to divide the macro part correctly.

Thanks for your help!

ps plz dont tell me to set up excel so that window doesnt pop up.

[JRL]

I'm sure there's a way to split up the script so it runs in two parts... someone else might be able to tell you how.

One way (my favorite way) to handle this kind of situation is to use a second Macro Scheduler script. In this case the second script will wait for the pop up window to pop up, then immediately press enter to close it, the script then ends. As a failsafe the script will automatically quit after a few seconds. As written the script uses OnEvent> to wait for a window with a name that you need to provide.

Create a script similar to this: Replace "Pop_Up_Window_Title" with the real window title.

Code: Select all

OnEvent>WINDOW_OPEN,Pop_Up_Window_Title,2,CloseWindow

SRT>CloseWindow
  SetFocus>Pop_Up_Window_Title
  Press Enter
  Wait>0.5
  Exit>0
END>CloseWindow

Let>kk=0

Label>Loop
  Add>kk,1
  If>kk>500
    Exit>0
  EndIf
  Wait>0.01
Goto>Loop

At the top of the Excel Macro running script insert an ExecuteFile> line with the file to be executed being the aforementioned second Macro Scheduler script.

Code: Select all

ExecuteFile>C:\path\Close_Pop_Up_Window.scp

VBSTART
Sub ExcelExample
Dim xlApp
Dim xlBook

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("d:\ est.xls")

xlApp.visible = True
xlApp.Run "Macro3"

xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
VBEND
VBRun>ExcelExample

Your main script opens excel and the excel macro, the executed second script should close the popup window. If the window never pops up or if some other error occurs, the second script will terminate after a few seconds. The number "500" If>kk>500 controls how long the script will wait before terminating itself. "500" on my computer is about 12 seconds. Make the number larger or smaller to suit.

[kriemer]

I'm curious why you would not want to get rid of the nag.

I've had the same issue, and my solution was a registry mod to get past the window, but if I am doing something wrong or ill advised I'd like to know.

Thanks

k

[joemc]

Can you tell me more about that registry mod plz.

The problem that im having is that till now the update of the database was done manually, and anyone could do it (well... they still can) The point is that is that the excel is a shared file and sometimes you want to update it and sometimes you dont.

Im not sure if you get my point. But basically I dont want to set the file to update automatically every time you enter cause it will be kind of annoying (it takes long).

The problem has a lot to do with how they work here, in the firm, and with the fact that the one who takes care of the database is not me. Im just the one who makes use of it and i got tired of updating it.

I forgot, theres another reason. I work with a lot of linked files and I dont want to set up excel to update the links everytime I open them.

[joemc]

Well... I will be taking out the pop-up by excel config. I coudnt seem to make the macro work consistently. Thanks a lot anyway, I discovered new possibilities in the macro world.

I found this in the internet but I cant make it work.

Code: Select all

Sub OpenUpdate()

    Workbooks.Open FileNmae:= _

        "C:\My Documents\LinkedBook.xls", UpdateLinks:=xlUpdateLinksAlways

End Sub



Sub OpenDontUpdate()

    Workbooks.Open FileNmae:= _

        "C:\My Documents\LinkedBook.xls", UpdateLinks:=xlUpdateLinksNever

End Sub

and this http://msdn.microsoft.com/es-es/library ... e.11).aspx