Consulting

Results 1 to 4 of 4

Thread: Copy data block from one workbook to another .. should be easy

  1. #1
    VBAX Newbie
    Joined
    Feb 2018
    Location
    England
    Posts
    3
    Location

    Copy data block from one workbook to another .. should be easy

    I am clearly missing something when it comes to ranges, copying and workbooks ... cannot seem to make this work. Simple task to copy a block of data from one spreadsheet to another. Any ideas?

    '------------------------------------------------------------------------------
    Private Sub mjsLoadOrigData()
    '------------------------------------------------------------------------------
    Dim RootFolder As String 'Where we start looking for the file
    Dim FileChoice As Integer 'Which file was chosen
    Dim FullName As String 'Full name of the file
    Dim RangeText As String

    Dim XLapp As Excel.Application 'Need a new instance of Excel
    Dim SrcBook As Excel.Workbook 'This will be the Quote Workbook object
    Dim ThisBook As Excel.Workbook 'This Rev Rec spreadsheet


    RootFolder = "C:\Users\Admin\Desktop"
    Set ThisBook = ThisWorkbook


    On Error GoTo CLEANUP2
    '------------------------------------------------
    'Open the file to copy data from
    '------------------------------------------------
    Set XLapp = New Excel.Application
    XLapp.Visible = True
    XLapp.FileDialog(msoFileDialogOpen).InitialFileName = RootFolder
    FileChoice = XLapp.FileDialog(msoFileDialogOpen).Show

    If FileChoice = 0 Then GoTo CLEANUP2

    FullName = XLapp.FileDialog(msoFileDialogOpen).SelectedItems(1)
    Set SrcBook = XLapp.Workbooks.Open(FullName)


    On Error GoTo 0
    '---------------------------------------------
    'work out the range and paste the data
    '---------------------------------------------

    RangeText = SrcBook.Worksheets("Original Data").Range("A1").End(xlDown).End(xlToRight).Address
    RangeText = "A1:" & Replace(RangeText, "$", "", 1)

    SrcBook.Worksheets("Original Data").Range(RangeText).Copy _
    Destination:=ThisBook.Worksheets("Data").Range(RangeText)


    CLEANUP2:
    '------------------------------------------------
    'lets close the file and release memory etc..
    '------------------------------------------------
    On Error Resume Next
    SrcBook.Close savechanges:=False
    XLapp.Quit
    Set XLapp = Nothing
    Set SrcBook = Nothing
    On Error GoTo 0
    End Sub

  2. #2
    VBAX Newbie
    Joined
    Feb 2018
    Location
    England
    Posts
    3
    Location
    I should have said this fails at the copy stage as follows
    Attached Images Attached Images

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I don't think you need to create another instance of Excel (esp. since you can't copy between them)

    Just have your macro open the other workbook, copy the data, and then save and close the second one
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Newbie
    Joined
    Feb 2018
    Location
    England
    Posts
    3
    Location
    Many Thanks Paul, removing the new instance of excel solvedmy problem.

    Stupid mistake and I was clearly looking in the wrong area.
    Really appreciate you taking the time to point this out,

    again thanks! Mike.



Tags for this Thread

Posting Permissions

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