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:

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

I even tried inserting

Expand|Select|Wrap|Line Numbers

 
    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:

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
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.Wait..?

Try using Sleep API..

Expand|Select|Wrap|Line Numbers

  
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
 
Sub MyDelay(TSec As Integer)
 
    Sleep (1000 * TSec)    'delay in milliseconds

    DoEvents
 
End Sub

Regards
Veena

NeverLift

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.

5ahen

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

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

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

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

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

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

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

Similar Visual Basic 4 / 5 / 6 bytes

/bytes/development

/bytes/it

/bytes/about