Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Open macro not running correctly
Hi. I'm trying to get the code below to run when the workbook is
opened. It runs just fine when I step through the code, but when I put the code into the workbook to run when it's opened, it messes up. This macro is supposed to open another workbook on our network (QueryBuster 5.21.15b.xlsm), and then do a couple processes to update the local workbook (QB Launcher), and then close the network workbook. It does open the network workbook just fine, but it's supposed to copy column H from the local workbook and insert it into the network workbook. But it's just copying the column from the local workbook and inserting it into itself. The macro is also supposed to then copy cells from the network workbook and paste them into the local workbook, and then close the network workbook. But it's closing the local workbook instead. Why would this macro run just fine when stepped through or as a stand alone macro, but then mess up when put inside the Workbook Open routine? Thanks for any advice. Sub UpdateQB() ' ' UpdateQB Macro ' Dim LastRow As Long ' Turn display alerts off temporarily Application.DisplayAlerts = False ' Unfilter data in Personal QB file and rename sheet to QueryBuster1. Sheet will ' be deleted later after updated QB data is imported. Sheets("QueryBuster").Select On Error Resume Next ActiveSheet.ShowAllData ' Open main QB, copy ProView column from local QB file to main QB file. This will leave the sheet macro alone. Workbooks.Open Filename:= _ "QueryBuster 5.21.15b.xlsm" _ , UpdateLinks:=0 Windows("QB Launcher.xlsm").Activate Sheets("QueryBuster").Select Columns("H:H").Copy Windows("QueryBuster 5.21.15b.xlsm").Activate Columns("H:H").Insert Shift:=xlToRight ' Finds the last row and copies the formula in column H down from row 2 to the last row LastRow = Range("H" & Rows.Count).End(xlUp).Row Range("H2:H" & LastRow).Formula = "=I2" Range("A1").Select ' Copies all cells from main QB to local QB Cells.Copy Windows("QB Launcher.xlsm").Activate Cells.Select ActiveSheet.Paste ' Close main QB Windows("QueryBuster 5.21.15b.xlsm").Activate Sheets("QueryBuster").Select ActiveWindow.Close ' Turn display alerts back on Application.DisplayAlerts = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Open macro not running correctly
The problem with not using *fully qualified object refs* to the
workbooks/worksheets your code acts on is unexpected results that may not be readily comprehensible. You need to declare and assign object refs something like... Dim wkbSource As Workbook, wkbTarget As Workbook Dim wksSource As Worksheet, wksTarget As Worksheet Const sSourceFile$ = "<fullpath\QueryBuster 5.21.15b.xlsm" Set wkbSource = Workbooks.Open(Filename:=sSourceFile, UpdateLinks:=0) Set wksSource = wkbSource.Sheets("QueryBuster") Set wkbTarget = ThisWorkbook Set wksTarget = wkbTarget.Sheets("QueryBuster") ...where you need to provide the FullName of the file to open. This makes your code better self-documenting and much easier to understand/maintain going forward!! Optionally, you can obviate need for wkbTarget since ThisWorkbook refs the file your code is in... Set wksTarget = ThisWorkbook.Sheets("QueryBuster") ...so you can just *ref* the sheets in code since there's no reason to 'select' or 'activate' anything! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Open macro not running correctly
I forgot to mention...
Stop using Workbook_Open events! Instead, use Excel AutoMacros... Sub Auto_Open() 'file open code... End Sub Sub Auto_Close() 'file before close code... End Sub ...in a standard module named something like "m_OpenClose". This is where you should also make your global declarations and intialize values at startup... Option Explicit Public gsAppPath$, gsNetPath$, gsSourceFile$ Public Const gsSourceFilename$ = "QueryBuster 5.21.15b.xlsm" Public Const gsDataSheetname$ = "QueryBuster" Sub Auto_Open() InitGlobals 'CreateMenus 'other startup stuff End Sub Sub Auto_Close() 'DeleteMenus 'other shutdown cleanup End Sub Sub InitGlobals() gsAppPath = ThisWorkbook.Path & "\" gsNetPath = "\\netshare\folder\" '//edit to actual gsSourceFile = gsNetPath & gsSourceFilename 'other initializations End Sub ...so your code to set ref to wkbSource could be... Set wkbSource = Workbooks.Open(gsSourceFile, UpdateLinks:=0) Set wksSource = wkbSource.Sheets(gsDataSheetname) Set wksTarget = ThisWorkbook.Sheets(gsDataSheetname) ...so your routine could be rewritten as... Sub Update_QueryBuster() ' Updates ThisWorkbook.Sheets("QueryBuster") ' with data from network "QueryBuster" file. Dim lLastRow&, wksTarget As Worksheet Dim wkbSource As Workbook, wksSource As Worksheet Set wksTarget = ThisWorkbook.Sheets(gsDataSheetname) 'Unfilter data in Personal QB file and rename sheet to QueryBuster1. 'Sheet will be deleted later after updated QB data is imported. With wksTarget If .AutoFilterMode Then .ShowAllData _ Else .Rows.Hidden = False: .Columns.Hidden = False End With 'wksTarget 'Open main QB, copy ProView column from local QB file to main QB file. 'This will leave the sheet macro alone. Set wkbSource = Workbooks.Open(gsSourceFilename, UpdateLinks:=0) Set wksSource = wkbSource.Sheets(gsDataSheetname) wksTarget.Columns("H:H").Copy With wksSource .Columns("H:H").Insert Shift:=xlToRight 'Find the last row and copy the formula 'in column H down from row2 to the last row lLastRow = .Cells(.Rows.Count, "H").End(xlUp).Row .Range("H2:H" & lLastRow).Formula = "=I2" 'Copy all cells from main QB to local QB wksTarget.Cells.ClearContents .Cells.Copy wksTarget.Cells(1) End With 'wksSource 'Close main QB wkbSource.Close False ErrExit: Set wksTarget = Nothing Set wkbSource = Nothing: Set wksSource = Nothing If Err < 0 Then _ MsgBox "An error occured while update being processed!", vbCritical End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Open macro not running correctly
Guess I've still got a lot to learn! :) Thank you for the advice.
Frank On Sun, 24 May 2015 15:39:51 -0400, GS wrote: The problem with not using *fully qualified object refs* to the workbooks/worksheets your code acts on is unexpected results that may not be readily comprehensible. You need to declare and assign object refs something like... Dim wkbSource As Workbook, wkbTarget As Workbook Dim wksSource As Worksheet, wksTarget As Worksheet Const sSourceFile$ = "<fullpath\QueryBuster 5.21.15b.xlsm" Set wkbSource = Workbooks.Open(Filename:=sSourceFile, UpdateLinks:=0) Set wksSource = wkbSource.Sheets("QueryBuster") Set wkbTarget = ThisWorkbook Set wksTarget = wkbTarget.Sheets("QueryBuster") ..where you need to provide the FullName of the file to open. This makes your code better self-documenting and much easier to understand/maintain going forward!! Optionally, you can obviate need for wkbTarget since ThisWorkbook refs the file your code is in... Set wksTarget = ThisWorkbook.Sheets("QueryBuster") ..so you can just *ref* the sheets in code since there's no reason to 'select' or 'activate' anything! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Open macro not running correctly
Wow! And thank you VERY much for the time it took to put these
answers together! I'll be doing this going forward. Much appreciated! Frank On Sun, 24 May 2015 16:53:37 -0400, GS wrote: I forgot to mention... Stop using Workbook_Open events! Instead, use Excel AutoMacros... Sub Auto_Open() 'file open code... End Sub Sub Auto_Close() 'file before close code... End Sub ..in a standard module named something like "m_OpenClose". This is where you should also make your global declarations and intialize values at startup... Option Explicit Public gsAppPath$, gsNetPath$, gsSourceFile$ Public Const gsSourceFilename$ = "QueryBuster 5.21.15b.xlsm" Public Const gsDataSheetname$ = "QueryBuster" Sub Auto_Open() InitGlobals 'CreateMenus 'other startup stuff End Sub Sub Auto_Close() 'DeleteMenus 'other shutdown cleanup End Sub Sub InitGlobals() gsAppPath = ThisWorkbook.Path & "\" gsNetPath = "\\netshare\folder\" '//edit to actual gsSourceFile = gsNetPath & gsSourceFilename 'other initializations End Sub ..so your code to set ref to wkbSource could be... Set wkbSource = Workbooks.Open(gsSourceFile, UpdateLinks:=0) Set wksSource = wkbSource.Sheets(gsDataSheetname) Set wksTarget = ThisWorkbook.Sheets(gsDataSheetname) ..so your routine could be rewritten as... Sub Update_QueryBuster() ' Updates ThisWorkbook.Sheets("QueryBuster") ' with data from network "QueryBuster" file. Dim lLastRow&, wksTarget As Worksheet Dim wkbSource As Workbook, wksSource As Worksheet Set wksTarget = ThisWorkbook.Sheets(gsDataSheetname) 'Unfilter data in Personal QB file and rename sheet to QueryBuster1. 'Sheet will be deleted later after updated QB data is imported. With wksTarget If .AutoFilterMode Then .ShowAllData _ Else .Rows.Hidden = False: .Columns.Hidden = False End With 'wksTarget 'Open main QB, copy ProView column from local QB file to main QB file. 'This will leave the sheet macro alone. Set wkbSource = Workbooks.Open(gsSourceFilename, UpdateLinks:=0) Set wksSource = wkbSource.Sheets(gsDataSheetname) wksTarget.Columns("H:H").Copy With wksSource .Columns("H:H").Insert Shift:=xlToRight 'Find the last row and copy the formula 'in column H down from row2 to the last row lLastRow = .Cells(.Rows.Count, "H").End(xlUp).Row .Range("H2:H" & lLastRow).Formula = "=I2" 'Copy all cells from main QB to local QB wksTarget.Cells.ClearContents .Cells.Copy wksTarget.Cells(1) End With 'wksSource 'Close main QB wkbSource.Close False ErrExit: Set wksTarget = Nothing Set wkbSource = Nothing: Set wksSource = Nothing If Err < 0 Then _ MsgBox "An error occured while update being processed!", vbCritical End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Open macro not running correctly
This works brilliantly, by the way. Thank you!!
Frank On Sun, 24 May 2015 16:53:37 -0400, GS wrote: I forgot to mention... Stop using Workbook_Open events! Instead, use Excel AutoMacros... Sub Auto_Open() 'file open code... End Sub Sub Auto_Close() 'file before close code... End Sub ..in a standard module named something like "m_OpenClose". This is where you should also make your global declarations and intialize values at startup... Option Explicit Public gsAppPath$, gsNetPath$, gsSourceFile$ Public Const gsSourceFilename$ = "QueryBuster 5.21.15b.xlsm" Public Const gsDataSheetname$ = "QueryBuster" Sub Auto_Open() InitGlobals 'CreateMenus 'other startup stuff End Sub Sub Auto_Close() 'DeleteMenus 'other shutdown cleanup End Sub Sub InitGlobals() gsAppPath = ThisWorkbook.Path & "\" gsNetPath = "\\netshare\folder\" '//edit to actual gsSourceFile = gsNetPath & gsSourceFilename 'other initializations End Sub ..so your code to set ref to wkbSource could be... Set wkbSource = Workbooks.Open(gsSourceFile, UpdateLinks:=0) Set wksSource = wkbSource.Sheets(gsDataSheetname) Set wksTarget = ThisWorkbook.Sheets(gsDataSheetname) ..so your routine could be rewritten as... Sub Update_QueryBuster() ' Updates ThisWorkbook.Sheets("QueryBuster") ' with data from network "QueryBuster" file. Dim lLastRow&, wksTarget As Worksheet Dim wkbSource As Workbook, wksSource As Worksheet Set wksTarget = ThisWorkbook.Sheets(gsDataSheetname) 'Unfilter data in Personal QB file and rename sheet to QueryBuster1. 'Sheet will be deleted later after updated QB data is imported. With wksTarget If .AutoFilterMode Then .ShowAllData _ Else .Rows.Hidden = False: .Columns.Hidden = False End With 'wksTarget 'Open main QB, copy ProView column from local QB file to main QB file. 'This will leave the sheet macro alone. Set wkbSource = Workbooks.Open(gsSourceFilename, UpdateLinks:=0) Set wksSource = wkbSource.Sheets(gsDataSheetname) wksTarget.Columns("H:H").Copy With wksSource .Columns("H:H").Insert Shift:=xlToRight 'Find the last row and copy the formula 'in column H down from row2 to the last row lLastRow = .Cells(.Rows.Count, "H").End(xlUp).Row .Range("H2:H" & lLastRow).Formula = "=I2" 'Copy all cells from main QB to local QB wksTarget.Cells.ClearContents .Cells.Copy wksTarget.Cells(1) End With 'wksSource 'Close main QB wkbSource.Close False ErrExit: Set wksTarget = Nothing Set wkbSource = Nothing: Set wksSource = Nothing If Err < 0 Then _ MsgBox "An error occured while update being processed!", vbCritical End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Open macro not running correctly
Glad to help! I appreciate the feedback...
-- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open & update another file (through macro) while running macro | Excel Programming | |||
Running macro on Open | Excel Programming | |||
Macro not running correctly | Excel Programming | |||
Macro to open files isn't updating correctly | Excel Discussion (Misc queries) | |||
Auto Running a macro on open | Excel Programming |