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

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

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

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!

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

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

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