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

If the macro name was "Test"

Application.Run "C:\Book1.xls!Test"

Also check out the "How can I execute a subroutine that's in a
different workbook?" section of this page:

http://j-walk.com/ss/excel/faqs/xl95faq3.htm

HTH,
JP

On Jan 11, 4:40*pm, March wrote:
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)


March wrote:

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

--

Dave Peterson

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

"Dave Peterson" wrote:

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)


March wrote:

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

--

Dave Peterson

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!"

March wrote:

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

"Dave Peterson" wrote:

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)


March wrote:

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

--

Dave Peterson


--

Dave Peterson

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



Dave Peterson wrote:

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!"

March wrote:

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

"Dave Peterson" wrote:

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)


March wrote:

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

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

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" wrote:

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



Dave Peterson wrote:

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!"

March wrote:

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

"Dave Peterson" wrote:

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)


March wrote:

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

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

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????


March wrote:

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" wrote:

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



Dave Peterson wrote:

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!"

March wrote:

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

"Dave Peterson" wrote:

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)


March wrote:

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

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

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" wrote:

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????


March wrote:

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" wrote:

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



Dave Peterson wrote:

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!"

March wrote:

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

"Dave Peterson" wrote:

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)


March wrote:

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

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

Nope. Sorry.

March wrote:

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" wrote:

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????


March wrote:

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" wrote:

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



Dave Peterson wrote:

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!"

March wrote:

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

"Dave Peterson" wrote:

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)


March wrote:

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

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

March

"Dave Peterson" wrote:

Nope. Sorry.

March wrote:

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" wrote:

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????


March wrote:

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" wrote:

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



Dave Peterson wrote:

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!"

March wrote:

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

"Dave Peterson" wrote:

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)


March wrote:

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

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson