3
I have a VBA-coded macro in an Excel workbook that is to open another existing workbook -- a .xls file, not .csv -- copy data from it, paste that into the original workbook where the macro resides, then close the source workbook.
If I use the debugger to step through the macro -- putting a breakpoint at its first executable line, run the macro, then F8 step through it, it works fine. (With delays at the open, close statements.) Free running, it doesn't. I suspect it's a timing issue, that the macro runs full tilt even though the new workbook isn't yet open -- but I don't know how to remedy that.
Here is the situation and the simplified code (the actual macro does a lot more, of course):
Main workbook: Two sheets, "Parameters" that has the fully-qualified filename for the desired source .xls in A1; and "Data", into which I want to paste data from that source.
Source workbook: Single sheet, "Sheet1"
Macro code. For simplicity, it is trying to copy/paste all of the source worksheet's content:
Expand|Select|Wrap|Line Numbers
- Sub Go()
- '
- ' Go Macro
- ' Test of getting data from another spreadsheet
- '
- ' Keyboard Shortcut: Ctrl+Shift+G
- '
- Dim SourceFile As String
- Dim HomeBook As String
- Dim OtherBook As String
- Sheets("Parameters").Select
- SourceFile = Range("A1").Value
- HomeBook = ActiveWorkbook.Name
- Workbooks.Open Filename:=SourceFile
- OtherBook = ActiveWorkbook.Name
- Cells.Select
- Selection.Copy
- Windows(HomeBook).Activate
- Sheets("Data").Select
- Range("A1").Select
- ActiveSheet.Paste
- Application.DisplayAlerts = False
- Workbooks(OtherBook).Close SaveChanges:=False
- Application.DisplayAlerts = True
- End Sub
Expand|Select|Wrap|Line Numbers
- Windows(OtherBook).Activate
- Sheets("Sheet1").Select
When I step through it, the Locals view has all the parameters, getting set properly. If I try to simply run it, it stalls at the opened source workbook, the Locals view is empty, no data has been transferred into the initiating workbook.
If I place the breakpoint after the open, it is not reached. (I have the VBA open.) Hitting F8 then yields a VB error window, "Compile Error" "Expected: To"
Thanks for your help on this.
Gary
