+ Reply to Thread
Results 1 to 6 of 6

[EXCEL] Open and copy from workbook specified in existing cells / 2010 upgrade problem

Hybrid View

  1. #1
    Registered User
    Join Date
    11-20-2012
    Location
    cameo trembled
    MS-Off Ver
    Excel 2010
    Posts
    8

    [EXCEL] Open and copy from workbook specified in existing cells / 2010 upgrade problem

    Hi.

    I am fairly new to VBA.

    I created a VBA code in Excel 2007 to automatically open and copy data from a workbook located on a network drive into an existing workbook. The name and location of the network worbook was specified in cells in the existing workbook (See below). It worked perfectly fine in 2007, but after upgrading to 2010 I get the error that the network worbook can not be found. I have checked that all file type specifications are correct (e.g. .xls and .xlsx. and the location of the newtwork has not changed. My guess is that it the code simply doesn't work in the new version, but how should it be changed then.

    Thank you so much

    My code

    ChDir Range("D49")
     Workbooks.Open Filename:=Range("B49")
     sWin = Application.Workbooks("Existing workbook.xlsm").Worksheets("Frontpage").Range("B49")
     Application.Windows(sWin).Activate
     sheets("Sheet1").Select
     Range("A1:II1200").Select
     Selection.Copy
     Windows("Existing workbook.xlsm").Activate
     sheets("Stress").Select
     Range("A1").Select
     ActiveSheet.Paste
     Application.CutCopyMode = False
    
     sWin = Application.Workbooks("Existing workbook.xlsm").Worksheets("Frontpage").Range("B49")
     Application.Windows(sWin).Activate
     ActiveWindow.Close

  2. #2
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: [EXCEL] Open and copy from workbook specified in existing cells / 2010 upgrade problem

    You might try shortening it a bit:

        Dim wbkImport                As Excel.Workbook
    
        Set wbkImport = Application.Workbooks.Open(Filename:=Range("D49") & Range("B49"))
    
        wbkImport.Worksheets("Sheet1").Range("A1:II1200").Copy _
            Destination:=Workbooks("Existing Workbook.xlsm").Worksheets("Stress").Range("A1:II1200")
        wbkImport.Close SaveChanges:=False
    
        Set wbkImport = Nothing
    You also might want to be specific with the two Range statements, ensuring you are referring to the right cell, something like:

    Filename:=worksheets("Master").Range("D49") & worksheets("Master").Range("B49")
    Assuming the cells are on your Master worksheet.

  3. #3
    Registered User
    Join Date
    11-20-2012
    Location
    cameo trembled
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: [EXCEL] Open and copy from workbook specified in existing cells / 2010 upgrade problem

    Thank you so much for your help. The first part seems to solve the problem.

    However, I would like to copy more sheets in the defined workbook "wbkImport". I have tried the following but get a syntax error regarding sheet 2. How do I seperate?

     Dim wbkImport                As Excel.Workbook
    
        Set wbkImport = Application.Workbooks.Open(Filename:=Range("D49") & Range("B49"))
    
        wbkImport.Worksheets("Sheet1").Range("A1:II1200").Copy _
            Destination:=Workbooks("Existing Workbook.xlsm").Worksheets("Stress").Range("A1:II1200")
        
    
        wbkImport.Worksheets("Sheet2").Range("A1:II1200").Copy _
            Destination:=Workbooks("Existing Workbook.xlsm").Worksheets("Stress1").Range("A1:II1200")
    
    wbkImport.Close SaveChanges:=False
    
        Set wbkImport = Nothing

  4. #4
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: [EXCEL] Open and copy from workbook specified in existing cells / 2010 upgrade problem

    The syntax looks ok to me, and it compiles ok. I would say you just need to ensure there is a sheet2 in the workbook being imported, and that there is a Stress1 in the Existing Workbook.

  5. #5
    Registered User
    Join Date
    11-20-2012
    Location
    cameo trembled
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: [EXCEL] Open and copy from workbook specified in existing cells / 2010 upgrade problem

    Well the destination part in the second import turns red, and when running I get "Compile erorr: Syntax error". What am I doing wrong?


    Dim wbkImport                As Excel.Workbook
    
        Set wbkImport = Application.Workbooks.Open(Filename:=Range("D49") & Range("B49"))
    
        wbkImport.Worksheets("Sheet1").Range("A1:II1200").Copy _
            Destination:=Workbooks("Existing Workbook.xlsm").Worksheets("Stress").Range("A1:II1200")
        
    
        wbkImport.Worksheets("Sheet2").Range("A1:II1200").Copy _
            Destination:=Workbooks("Existing Workbook.xlsm").Worksheets("Stress1").Range("A1:II1200")
    
    wbkImport.Close SaveChanges:=False
    
        Set wbkImport = Nothing

  6. #6
    Registered User
    Join Date
    11-20-2012
    Location
    cameo trembled
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: [EXCEL] Open and copy from workbook specified in existing cells / 2010 upgrade problem

    Oh, i figured it out. I accidentally left out the "_" after "copy" in the second import. Problem solved, and thank you so much for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1