Import Workbook and append data

Hi, desibabuji,

If it is the first sheet to be imported then add as a new worksheet.

You give the new sheet a unique name in the code displayed - how can we find out if it is a new sheet or records need to be updated?

I personally would strongly recommend not to let the user do a Multi-Select of Files and the just assign one identical name to the worksheet - maybe you can be a bit more specific about the names of the worksheets to use as well (I would perhaps use part of the workbook´s name or from any column where unique data is stored).

Ciao,
Holger

Hi, desibabuji,

will the data be always on the same columns or will that vary (or will even the headings and contents vary?) The last question may be of concern as you can´t simply copy the contents across but need to make sure to fill the correct columns. Will the workbooks consist of one or more sheets of data for copying?

Could you please attach a workbook with a sample of data to be attached and a copy of where to attach (maybe point out if anything special has to be ztaken care of when copying data)? TIA.

Ciao,
Holger

Hi Holger,

Sorry I was out of town so couldnt reply earlier.

The data will always have the same column (even the headings will be the same) and the workbook will only have 1 sheet per workbook.

I came across this link on the forums for a page that is owned by one of the gentleman on this forum and this looks really close and interesting to what I want to do but instead of pulling all the files in a folder, I want to be able to choose the files that I want to be able to merge.
https://sites.google.com/a/madrocket...s-to-one-sheet

Also, please find attached 3 sheets, 2 of them are of sample data and the third one is of the consolidated sample.

Once again thanks for all your help.

Hi, desibabuji,

if you change the name of the worksheet to suit (from Master to Export_1104) and adjust the path to the folder accordingly, the macro from Jerry will import the contents of any wortkbook found in the location into the sheet. I would recommend a sorting on the data after the import and maybe run a search for duplicates on the data assembled. If you need help on that code I think JBeaucaire would be the best person to ask but maybe I can handle that (not as good as Jerry but anyhow make it work - somehow).

If you want the user to take care of which workbook to amend or copy to a new worksheet in the macro workbook please feel free to come back and ask - I could supply a code which lets the user choose the file and then ask via MsgBox whether to amend or insert into a new worksheet.

Ciao,
Holger

This macro should be installed into your Consolidation workbook in a standard code module.

When you run it, it will first detect/create an Export sheet with today's date, then let you select as many files as you want, one at a time, and import them.

Just edit the default path so the file selector opens in the best "starting folder" for your searches.

Option Explicit

Sub ImportTodaysChoices()
'requires reference to Microsoft Office 11.0 Object Library
Dim wsNEW As Worksheet, wbDATA As Workbook, fNAME As String

Application.ScreenUpdating = False

With ThisWorkbook
    fNAME = "Export_" & Format(Date, "MMDD")
    If Not Evaluate("ISREF(" & fNAME & "!A1)") Then
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = fNAME
        .Sheets(fNAME).Range("A1:H1").Value = [{"ID","Name","Billable","Total Billed","Area","Pending","Begin","End"}]
    End If
    Set wsNEW = .Sheets(fNAME)
End With

Do
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = "C:\2012\Test\"      'default path to open to
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xl*", 1  'default
        .Title = "Select a file to Import"
        .Show
        If .SelectedItems.Count > 0 Then
            fNAME = .SelectedItems(1)
        Else
            Application.ScreenUpdating = True
            Exit Sub
        End If
    End With

    Set wbDATA = Workbooks.Open(fNAME)
    ActiveSheet.UsedRange.Offset(1).Copy wsNEW.Range("A" & Rows.Count).End(xlUp).Offset(1)
    wbDATA.Close False
    wsNEW.Columns.AutoFit
Loop

End Sub

Change the Offset(1).Copy to Offset(4).Copy

I would import all the data, then delete columns C and E at the end.

Hi, desibabuji,

you should reverse the columns to be deleted: first column E and thereafter Column C. If you delete Column C first you should adapt your code because Column E would have shifted to D instead.

The code should be inserted beetween Loop and End Sub to only be run once at the end:

    wsNEW.Range("E1").EntireColumn.Delete
    wsNEW.Range("C1").EntireColumn.Delete

Ciao,
Holger