call multiple macros in workbook_open?

[slimswol]

Hi,
I almost have a program running for excel. I am trying to call multiple
macros from "ThisWorkbook". The code below is supposed to delete the
current QueryTable and then after the QueryTable is deleted I call a
macro to add the new data. It currently does work for one
sheet/QueryTable/Macro. But I am not sure how to do this for multiple
sheets/QueryTables/Macros ?

MY QUESTION IS HOW DO I DELETE QUERYTABLES AND CALL MULTIPLE MACROS
FROM THE \"WORKBOOK_OPEN\" FUNCTION?

IF IT IS NOT POSSIBLE, IS THERE ANOTHER WAY TO DO THIS?


Private Sub Workbook_Open()


With Worksheets("Sheet1_RawData")

..QueryTables(1).ResultRange.ClearContents

End With


With Worksheets("Sheet1_RawData")

..QueryTables(1).Delete

End With


Dim qt As QueryTable

For Each qt In Sheets("Sheet1_RawData").QueryTables
'Clear QT data
qt.ResultRange.ClearContents

'Delete QT named range from workbook

Sheets("Sheet1_RawData").qt(1).Delete

'Delete query table
qt.Delete
Next qt

'Call macro to run new query
Call URL_Sheet1_Query

End Sub

 

[Ardus Petus]

Try this (untested) :

Private Sub Workbook_Open()
dim ws as worksheet
dim qt as querytable

for each ws in ThisWorkbook.Worksheets
for each qt in ws.querytables
.ResultRange.ClearContents
.Delete
next qt
next ws
Call URL_Sheet1_Query
call URL_Sheet2_Query
....

end sub

HTH
--
AP

"slimswol" <[email protected]> a écrit
dans le message de
news:[email protected]...

 

[slimswol]

If I change the code to what you posted above, I get the error:

"Invalid or unqualified reference"

I think there may be some syntax missing.

I think we are on the right track though.

hmmm...????

 

[slimswol]

Hey... I changed a few lines of code and now it deletes all of the
QueryTables but does not re - run the macros that are called at the
bottom of the code.



Private Sub Workbook_Open()

Dim ws As Worksheet
Dim qt As QueryTable

For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables

'Clear QT data
qt.ResultRange.ClearContents
'Delete QT named range from workbook

'Delete query table
qt.Delete

Next qt
Next ws

Call URL_Sheet1_Query
Call URL_Sheet2_Query
Call URL_Sheet3_Query
Call URL_Sheet4_Query


End Sub

 

[slimswol]

From my travels on the internet. I am reading that it is not possible
to Call more than one macro within the "Workbook_Open" function.

Is this true? If so then that may be the problem with this code.

Any assistance on getting this corrected would be great. Thanks ahead
of time.


Private Sub Workbook_Open()

Dim ws As Worksheet
Dim qt As QueryTable

For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables

'Clear QT data
qt.ResultRange.ClearContents
'Delete QT named range from workbook

'Delete query table
qt.Delete

Next qt
Next ws

Call URL_Sheet1_Query
Call URL_Sheet2_Query
Call URL_Sheet3_Query
Call URL_Sheet4_Query


End Sub

 

[slimswol]

Any ideas on what to do next to run / call multiple macros when opening
the excel document?

 

[Desert Piranha]

Any ideas on what to do next to run / call multiple macros when opening
the excel document?Hi,

You mean something like this?

Private Sub Workbook_Open()
Call Macro1
Call Macro2
Call Macro3
End Sub

Sub Macro1()
MsgBox "This is #1"
End Sub

Sub Macro2()
MsgBox "This is #2"
End Sub

Sub Macro3()
MsgBox "This is #3"
End Sub