call multiple macros in workbook_open?

slimswol · #1 03-08-2006, 04:34 PM

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

#2 03-08-2006, 05:15 PM

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" <slimswol.24dbka_1141850102.2339@excelforum-nospam.com> a écrit
dans le message de
news:slimswol.24dbka_1141850102.2339@excelforum-nospam.com...
>
> 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
>
>
> --
> slimswol
> ------------------------------------------------------------------------
> slimswol's Profile:
http://www.excelforum.com/member.php...o&userid=32014
> View this thread: http://www.excelforum.com/showthread...hreadid=520282
>

slimswol · #3 03-08-2006, 05:46 PM

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 · #4 03-08-2006, 06:11 PM

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 · #5 03-08-2006, 06:25 PM

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 · #6 03-09-2006, 01:46 PM

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

Desert Piranha · #7 03-09-2006, 11:29 PM

Quote:

Originally Posted by slimswol

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

Welcome to the Excel Forum

Forum Statistics