+ Reply to Thread
Results 1 to 4 of 4

Export excel data to another excel workbook

Hybrid View

  1. #1
    Registered User
    Join Date
    04-22-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    94

    Export excel data to another excel workbook

    I have a macro that writes data from one worksheet to another inside the same workbook. I want to write the data to another workbook if this is possible using my code below, i was wondering could i change the first line of the code "Set wsData = Sheet2" to the pathname of the workbook and worksheet i want to write the data to, i dont think this will work though, anyone have any ideas?

    Sub Save_Data()
    
    Dim wsData     As Worksheet
    Dim wsForm     As Worksheet
    Dim rNextCl    As Range
    
    
        'variables
        Set wsData = Sheet2
        Set wsForm = Sheet1
        'find the next empty row for data input
        Set rNextCl = wsData.Cells(65536, 1).End(xlUp).Offset(1, 0)
    
    
        With rNextCl
            .Value = wsForm.Cells(2, 2).Value
            .Offset(0, 1).Value = wsForm.Cells(3, 2).Value
            .Offset(0, 2).Value = wsForm.Cells(4, 2).Value
            .Offset(0, 3).Value = wsForm.Cells(5, 2).Value
            .Offset(0, 4).Value = wsForm.Cells(6, 2).Value
        End With
        
         'clear input cells, note use of Named Range
        wsForm.Range("Data").ClearContents
        'clear variables
        Set wsData = Nothing
        Set wsForm = Nothing
        Set rNextCl = Nothing
    
        'data transfer message
        MsgBox "Data transferred" & vbCrLf & "", vbInformation, "Data transfer"
    
    
    End Sub

  2. #2
    Registered User
    Join Date
    11-18-2009
    Location
    The Netherlands
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    7

    Re: Export excel data to another excel workbook

    you can use the workbooks.open function to open a workbook (didn't check the code, it's just to give you an idea)

    dim wbTarget as workbook 
    dim wsTarget as worksheet
    
    set wbTarget = workbooks.open("filename")
    set wsTarget = wbTarget("sheetname")
    don't forget to close the workbook after you're done with it!

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Export excel data to another excel workbook

    set wsTarget = wbTarget.Worksheets("sheetname")
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    11-18-2009
    Location
    The Netherlands
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    7

    Unhappy Re: Export excel data to another excel workbook

    Ah, yes of course. Sorry about that ....

+ 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