Auto run macro

  • Thread starter Thread starter March
  • Start date Start date
M

March

Dear All,

I want to run automated macro in two workbooks. I means I have two workbooks
one centain macro and another one does not for now.

For the second one, I want to write VBA to call the Macro function from the
first one. I don't know what should I start now.


Please give me suggestion.


Thanks,

March
 
Inside the second workbook's project:

Dim OtherWkbk as workbook
set otherwkbk = workbooks("otherworkbookname.xls") '<-- it has to be open
application.run "'" & otherwkbk.name & "'!somemacronamehere"

or

application.run "'" & otherwkbk.name & "'!somemacronamehere", myargument

or to return something:

dim resp as long 'or string or whatever
resp = application.run("'" & otherwkbk.name & "'!somemacronamehere", _
myargument)
 
Here is my code

Sub Workbook_Open()

txttemp = ThisWorkbook.Path & Application.PathSeparator & "WorkBook2.xls"

Application.Run txttemp!Workbook_Analysis

End Sub

Workbook_Analysis is the macro name

I want the file is auto open too. The first line works. The second line
doesn't work now.

In my opionion the second line might need to put something for the
concatenetion.

Anyways, please give me suggestion.

Thanks,

March
 
The workbook must be open and you don't use the path in the application.run
line.

If workbook2.xls isn't open, you can open it in code:

Dim wkbk as workbook

set wkbk = nothing
on error resume next
set wkbk = workbooks.open _
(filename:=thisworkbook.path & application.pathseparator & "workbook2.xls")
on error goto 0

if wkbk is nothing then
msgbox "That file didn't open!"
 
Hmmm. I didn't finish the code...

Dim wkbk as workbook

set wkbk = nothing
on error resume next
set wkbk = workbooks.open _
(filename:=thisworkbook.path & application.pathseparator & "workbook2.xls")
on error goto 0

if wkbk is nothing then
msgbox "That file didn't open!"
else
application.run "'" & wkbk.name & "'!workbook_analysis"
end if
 
Hi Dave,

My problem is now I have to give the right sequence of my source code. I
don't know what to start with this. I try to do many ways, it still have the
problem.

I still have two excel workbooks ---> Book1 and Book2 for example. I write
vba codes to open Book2 (doesn't contain any macro) before open Book1 in ms
Access in order to export values from Access form to excel worksheet(Book1).
I have done this part well.

However, in Book1, if set "Thisworkbook" as "Sub Workbook_Open( )" in order
to auto_run macro in excel, I cannot get all values from access form to
excel. Tha means once Book2 opend, following with Book1 opened, the
Workbook_Open( ) run macro. It skips the rest of source codes in ms Access.

If I run the macro manually, all work well.

Book2 is a destinations to place the data form Book1.

Please give me suggestion.


Thanks,

March


Dave Peterson said:
Hmmm. I didn't finish the code...

Dim wkbk as workbook

set wkbk = nothing
on error resume next
set wkbk = workbooks.open _
(filename:=thisworkbook.path & application.pathseparator & "workbook2.xls")
on error goto 0

if wkbk is nothing then
msgbox "That file didn't open!"
else
application.run "'" & wkbk.name & "'!workbook_analysis"
end if
Click to expand...
 
Maybe it's an access problem?

Maybe adding a delay into your code would help?

dim iCtr as long
for ictr = 1 to 1000
doevents
next ictr

But those are just guesses????

Hi Dave,

My problem is now I have to give the right sequence of my source code. I
don't know what to start with this. I try to do many ways, it still have the
problem.

I still have two excel workbooks ---> Book1 and Book2 for example. I write
vba codes to open Book2 (doesn't contain any macro) before open Book1 in ms
Access in order to export values from Access form to excel worksheet(Book1).
I have done this part well.

However, in Book1, if set "Thisworkbook" as "Sub Workbook_Open( )" in order
to auto_run macro in excel, I cannot get all values from access form to
excel. Tha means once Book2 opend, following with Book1 opened, the
Workbook_Open( ) run macro. It skips the rest of source codes in ms Access.

If I run the macro manually, all work well.

Book2 is a destinations to place the data form Book1.

Please give me suggestion.

Thanks,

March
Click to expand...
 
I have tried with delay function. It does not work at this point.

Once set Workbook_Open( ), it automatically runs macro.

So do you have any other idea.


Thanks,

March

Dave Peterson said:
Maybe it's an access problem?

Maybe adding a delay into your code would help?

dim iCtr as long
for ictr = 1 to 1000
doevents
next ictr

But those are just guesses????
Click to expand...
 
Nope. Sorry.
I have tried with delay function. It does not work at this point.

Once set Workbook_Open( ), it automatically runs macro.

So do you have any other idea.

Thanks,

March
Click to expand...
 
Thank you so much. This is really help me in some points. I've changed tha
way to get my project done.

March
 
Back
Top