By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,993 Members | 1,984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,993 IT Pros & Developers. It's quick & easy.

Opening, copying from, another workbook from VBA Excel macro fails

P: 3
This is probably answered elsewhere, but I've searched the Web and VBA for Excel manual, find no answers.

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
  1. Sub Go()
  2. '
  3. ' Go Macro
  4. ' Test of getting data from another spreadsheet
  5. '
  6. ' Keyboard Shortcut: Ctrl+Shift+G
  7. '
  8.     Dim SourceFile As String
  9.     Dim HomeBook As String
  10.     Dim OtherBook As String
  11.     Sheets("Parameters").Select
  12.     SourceFile = Range("A1").Value
  13.     HomeBook = ActiveWorkbook.Name
  14.     Workbooks.Open Filename:=SourceFile
  15.     OtherBook = ActiveWorkbook.Name
  16.     Cells.Select
  17.     Selection.Copy
  18.     Windows(HomeBook).Activate
  19.     Sheets("Data").Select
  20.     Range("A1").Select
  21.     ActiveSheet.Paste
  22.     Application.DisplayAlerts = False
  23.     Workbooks(OtherBook).Close SaveChanges:=False
  24.     Application.DisplayAlerts = True
  25. End Sub
I even tried inserting

Expand|Select|Wrap|Line Numbers
  1.     Windows(OtherBook).Activate
  2.     Sheets("Sheet1").Select
right after the open, hoping that might cause a dwell for the open to complete.

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.

Mar 15 '08 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 112
I believe something like this will be more directly what you want:
Expand|Select|Wrap|Line Numbers
  1. Sub Copy()
  2.     Dim wb As Workbook
  3.     Dim ws As Worksheet
  4.     Set ws = ActiveSheet
  5.     Set wb = Workbooks.Open(Application.GetOpenFilename)
  6.     wb.Worksheets(1).Cells.Copy
  7.     ws.Range("A1").PasteSpecial
  8.     wb.Close
  9. End Sub
the worksheets property of the Workbook object will take a string or a number in this case I just had it copy all of the cells from the first worksheet in the workbook. You can change this by hard coding it or using it as a parameter to the copy Subroutine. Same goes for what you want to copy, and where you want to place it. The wb.close method also takes arguments which will suppress any messages you might not want when running the subroutine. Let me know if you'd like me to explain further. I do believe what you were getting was a timing issue, using the object variables to control the workbooks and worksheets will mitigate that.
Mar 15 '08 #2

P: 3
My slighly different version -- to get the file to be opened from A1 on the Parameters worksheet of the current workbook, paste from the source workbook to the Data worksheet of that book:
Expand|Select|Wrap|Line Numbers
  1. Sub Copy()
  2. '
  3. ' Copy Macro
  4. ' Macro recorded 3/15/2008 by Gary
  5. '
  6. ' Keyboard Shortcut: Ctrl+Shift+H
  7. '
  8.     Dim wb As Workbook
  9.     Dim ws As Worksheet
  10.     Dim fn As String
  11.     fn = Worksheets("Parameters").Range("A1").Value
  12.     Set ws = Worksheets("Data")
  13.     Set wb = Workbooks.Open(fn)
  14.     wb.Worksheets(1).Cells.Copy
  15.     ws.Range("A1").PasteSpecial
  16.     Application.DisplayAlerts = False
  17.     wb.Close SaveChanges:=False
  18.     Application.DisplayAlerts = True
  19. End Sub
Still had the same effect: Works fine manually, when I set the breakpoint at the first executable statement, then use Ctrl-Shift-H to start it, F8 to step through. Without the breakpoint, simply starting it with Ctrl-ShiftH stalls when the source workbook has opened, with it being the active book.

As an experiment: Even put in a 10 second pause after the open. Didn't help. Almost as if it stops executing the code as the source workbook opens.
Mar 16 '08 #3

Expert 100+
P: 1,445

How have you Paused..? with Application.Wait..?

Try using Sleep API..

Expand|Select|Wrap|Line Numbers
  2. Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
  4. Sub MyDelay(TSec As Integer)
  6.     Sleep (1000 * TSec)    'delay in milliseconds
  7.     DoEvents
  9. End Sub
Mar 16 '08 #4

P: 3
Used Application.Wait. It did pause (tried 10 seconds) when I stepped through the code in debug, but never reached the wait when it ran without stepping.
Mar 16 '08 #5

P: 3

After reading NeverLift's query I realised that my requirement is much similar.I am all new to VB and need to automate a process.Can anyone please guide me how to do this in step by step manner.

My requirement is :
1) I have many multi-tabbed excel workbooks. (workbook with many sheets).
2) I need to copy the data from first column of one specific sheet say 1.1 of workbook one and copy it in the first column of a new excel workbook say trial.xls.
3) Repeat the step no. 2 for other workbooks and finally getting the data from first column of 1.1 sheet of all the workbooks aggregated in trial.xls.
4)finally deleting the multiple entries from trial.xls to get unique set of records.

Kindly help!!!
Jan 29 '09 #6

P: 1
I had the same problem & I find the best answer here.. Thanks :)
Apr 29 '12 #7

P: 1
Please follow this link:
It is about the bug related Shift key.
Have tried microsoft walkaround solution, it works fine!

'Declare API
Declare Function GetKeyState Lib "User32" _
(ByVal vKey As Integer) As Integer
Const SHIFT_KEY = 16

Function ShiftPressed() As Boolean
'Returns True if shift key is pressed
ShiftPressed = GetKeyState(SHIFT_KEY) < 0
End Function

Sub Demo()
Do While ShiftPressed()
Workbooks.Open ="C:\My Documents\ShiftKeyDemo.xls"
End Sub
Nov 16 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.