Solved

Excel VBA Append Data From Closed Workbook to Active Workbook/worksheet

Posted on 2014-10-05
6
2,172 Views
Last Modified: 2014-10-07
I am trying to figure out a VBA macro to allow user to open a closed workbook, with the aid of FileOpen, select Sheet 1 which has data from range A7:G last row. Evaluate first row date to append the data to the active sheet in the first workbook without overwriting current data.
The opening of two workbooks and copy paste is taking too long considering there is one master and several workbooks to import from on a daily basis.
0
Comment
Question by:DougDodge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40364266
Sub Workbook_test()
    Dim wb As Workbook
    Dim rng As Range
    Dim lastRow As Long
    ' turn off the screen updating
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open("C:\FOLDERNAME\yourfile.xls", True, True)
    'range of data from the source workbook
    Set rng = wb.Worksheets("sheet1").Cells(.Rows.Count, "A").End(xlUp).Row + 1
    'the sheet in this workbook to copy to
    With ThisWorkbook.Worksheets("sheet1")
        'find last used row
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        'copy range to it
        rng.Copy .Range("A" & lastRow)
    End With
    ' close the source workbook without saving any changes
    wb.Close False
    ' free memory
    Set wb = Nothing
    ' turn on the screen updating
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:DougDodge
ID: 40364854
Sorry, the code does not seem to work. It issues an "Invalid or unqualified Reference"

It hangs up here:

 Set rng = wb.Worksheets("sheet1").Cells(.Rows.Count, "A").End(xlUp).Row + 1
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40365279
While I suspect your instructions are not complete in what you wish to do, this code will do the following:
* identify the next available cell in the active sheet in which to append data (based on column A)
* prompt the user to open an Excel workbook (presumably to import data from)
* copy the range in the first sheet of that workbook from A7:Glastrow
* paste that copied selection to the original workbook.
* close the import workbook.
Option Explicit
Sub Append_Data()
    Dim strDest As String
    Dim lngLastSourceRow As Long
    Dim strFilePath, strImportFileName As String
    Dim strDestFile, strDestSheet As String
    
    Application.ScreenUpdating = False
    strDestFile = ActiveWorkbook.Name
    strDestSheet = ActiveSheet.Name
    strDest = Range("A2").End(xlDown).Offset(1, 0).Address
    
    strFilePath = Application.GetOpenFilename("Excel Files (*.xls;*.xlsx), *.xls;*.xlsx", , "Open Excel File to Import")
    If strFilePath = "False" Then Exit Sub
    Workbooks.Open (strFilePath)
    strImportFileName = ActiveWorkbook.Name
    Sheets(1).Select 'assumes data to be copied is on the first sheet, regardless of name
    lngLastSourceRow = Range("A" & Cells.Rows.Count).End(xlUp).Row
    Sheets("Sheet1").Range("A7:G" & lngLastSourceRow).Copy
    Workbooks(strDestFile).Sheets(strDestSheet).Range(strDest).PasteSpecial
    Application.CutCopyMode = False
    Workbooks(strImportFileName).Close savechanges:=False
    
    Application.ScreenUpdating = True
End Sub

Open in new window


Regards,
-Glenn
0
 

Author Closing Comment

by:DougDodge
ID: 40366066
Thanks.... A couple of slight changes and it works perfectly.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40366133
You're welcome; I'm glad I could help.

-Glenn
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question