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

[NeverLift]

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:

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

I even tried inserting

    Windows(OtherBook).Activate
    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.

Gary

[janders468]

I believe something like this will be more directly what you want:

Sub Copy()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Set wb = Workbooks.Open(Application.GetOpenFilename)
    wb.Worksheets(1).Cells.Copy
    ws.Range("A1").PasteSpecial
    wb.Close
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.

[NeverLift]

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:

Sub Copy()
'
' Copy Macro
' Macro recorded 3/15/2008 by Gary
'
' Keyboard Shortcut: Ctrl+Shift+H
'
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim fn As String
    fn = Worksheets("Parameters").Range("A1").Value
    Set ws = Worksheets("Data")
    Set wb = Workbooks.Open(fn)
    wb.Worksheets(1).Cells.Copy
    ws.Range("A1").PasteSpecial
    Application.DisplayAlerts = False
    wb.Close SaveChanges:=False
    Application.DisplayAlerts = True
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.

[QVeen72]

Hi,

How have you Paused..? with Application.Wai t..?

Try using Sleep API..

[code=vb]

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub MyDelay(TSec As Integer)

Sleep (1000 * TSec) 'delay in milliseconds
DoEvents

End Sub

[/code]

Regards
Veena

[NeverLift]

Used Application.Wai t. It did pause (tried 10 seconds) when I stepped through the code in debug, but never reached the wait when it ran without stepping.

[5ahen]

Hii

Hii...

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!!!

[Anamor]

I had the same problem & I find the best answer here.. Thanks :)

[junzhang]

Please follow this link: http://support.microsoft.com/kb/555263
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(SHI FT_KEY) < 0
End Function

Sub Demo()
Do While ShiftPressed()
DoEvents
Loop
Workbooks.Open ="C:\My Documents\Shift KeyDemo.xls"
End Sub