Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
call multiple macros in workbook_open?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
call multiple macros in workbook_open?
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" a écrit dans le message de ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
call multiple macros in workbook_open?
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 ------------------------------------------------------------------------ slimswol's Profile: http://www.excelforum.com/member.php...o&userid=32014 View this thread: http://www.excelforum.com/showthread...hreadid=520282 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
call multiple macros in workbook_open?
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 ------------------------------------------------------------------------ slimswol's Profile: http://www.excelforum.com/member.php...o&userid=32014 View this thread: http://www.excelforum.com/showthread...hreadid=520282 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
call multiple macros in workbook_open?
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 ------------------------------------------------------------------------ slimswol's Profile: http://www.excelforum.com/member.php...o&userid=32014 View this thread: http://www.excelforum.com/showthread...hreadid=520282 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
call multiple macros in workbook_open?
Any ideas on what to do next to run / call multiple macros when opening the excel document? -- slimswol ------------------------------------------------------------------------ slimswol's Profile: http://www.excelforum.com/member.php...o&userid=32014 View this thread: http://www.excelforum.com/showthread...hreadid=520282 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
call multiple macros in workbook_open?
slimswol Wrote: 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 -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=520282 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to call other macros | Excel Worksheet Functions | |||
Macros - Force execution using workbook_open | Excel Discussion (Misc queries) | |||
Workbook_Open - Multiple Events not all working | Excel Programming | |||
Call VBA-Subs From Old XLM-Macros | Excel Programming | |||
Call macros from another XL file | Excel Programming |