Beyond_avarice
Board Regular
- Joined
- Nov 13, 2012
- Messages
- 195
- Office Version
- 2007
- Platform
- Windows
Hello I've peeked into this website(and others) for years. I'm finally getting around to learning how to implement VBA. I have made a solution (by copying scripts available and learning how to write portions of it myself 
, code below) to automate collecting information from an .XLS file and appending it to the last row of information in a master workbook. This code works under the following situations:
In XL10, the new workbook opens then the code just stalls and I cannot get an error to throw. The original saved workbook is still open??
Now if I open a new instance of Xl07 ("Book1"), then I close the workbook(leaving the application open) and open a new workbook ("Book2"), the personal macro and addin work??? I do not have to save Book2, Book3, etc. There just seems to be something about Book1 that does not permit the personal macro or addin to work?
If I save Book1, then everything works(XL07)???
Some of my coworkers have XL2010 and Dual Monitors, I have attempted to turn off the second monitor and the problem still exists(details above).
I cannot continually bug them for access to their XL10 so I would at minimum like to focus on why Workbooks.Open() appears to be the problem. I have stepped through the code dozens of times and the problem occurs at the
This is the point that the activeworkbook("wb1") closes(XL07) or the new workbook opens and everything stalls (XL10).
I am on my company computer and I believe my Office applications are unit specific and not on the Citrix network.
I have made sure that the hotkeys do not include "shift", and the problem is still there.
Below is the full code, I am hoping someone can shed some light on my problem. Thank you in advance.
, code below) to automate collecting information from an .XLS file and appending it to the last row of information in a master workbook. This code works under the following situations:- On workbooks with macro(ie saved), in XL07.
- As an addin on saved workbooks, in XL07.
- As a Personal macro on saved workbooks, in XL07.
- With dual monitors on a saved workbooks, in XL07.
- Active unsaved workbooks that are not "Book1".
- On a new workbook ("Book1"), using either the personal macro or the addin, in XL07 or XL10.
- On dual monitors with XL10???
In XL10, the new workbook opens then the code just stalls and I cannot get an error to throw. The original saved workbook is still open??
Now if I open a new instance of Xl07 ("Book1"), then I close the workbook(leaving the application open) and open a new workbook ("Book2"), the personal macro and addin work??? I do not have to save Book2, Book3, etc. There just seems to be something about Book1 that does not permit the personal macro or addin to work?
If I save Book1, then everything works(XL07)???
Some of my coworkers have XL2010 and Dual Monitors, I have attempted to turn off the second monitor and the problem still exists(details above).
I cannot continually bug them for access to their XL10 so I would at minimum like to focus on why Workbooks.Open() appears to be the problem. I have stepped through the code dozens of times and the problem occurs at the
HTML:
Set wb2 = Workbooks.Open(Ret1)This is the point that the activeworkbook("wb1") closes(XL07) or the new workbook opens and everything stalls (XL10).
I am on my company computer and I believe my Office applications are unit specific and not on the Citrix network.
I have made sure that the hotkeys do not include "shift", and the problem is still there.
Below is the full code, I am hoping someone can shed some light on my problem. Thank you in advance.
HTML:
Sub Collect_BG_Files()
Application.ScreenUpdating = False
'Identify active workbook and workbook to open and select information from.
Dim wb1 As Workbook, wb2 As Workbook
Dim Ret1
Dim ePoint As Variant 'define the endpoint to append to.
Set wb1 = ActiveWorkbook
If Range("A2") = "" Then
ePoint = Range("A2").Address
Else: Range("A2").Select
ActiveCell.End(xlDown).Select
ePoint = ActiveCell.Offset(1, 0).Address
End If
'~~> Get the first File
Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "Please select first file")
If Ret1 = False Then Exit Sub
Set wb2 = Workbooks.Open(Ret1)
If Range("A3").Value = "" Then
wb2.Sheets(1).Range("A2:K2").Select
Selection.Copy wb1.Sheets(1).Range(ePoint)
wb2.Close SaveChanges:=False
Else
wb2.Sheets(1).Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Copy wb1.Sheets(1).Range(ePoint)
wb2.Close SaveChanges:=False
End If
Set wb2 = Nothing
Set wb1 = Nothing
Application.ScreenUpdating = True
End Sub
Last edited:
