Importing data from other spreadsheets

Barlew · May 10, 2011

Hello from a First time poster, but long time Excel lover!

Generally I can Google my VBA issues and cobble together the answers, but in this instance I’m just way over my head and need help.

Scenario
I've about 300 separate spreadsheet (testing scripts). The user of these scripts must complete various mandatory fields once the test is complete. The location of these mandatory fields is fixed i.e. the layout of all 300 spreadsheets is identical.

I want to create a Summary spreadsheet to pull in details from the 300 scripts. In this Summary Spreadsheet each of the 300 spreadsheets file names are listed and the ‘pulled in’ data will appear in cells adjacent to the file name.

SO, the VBA needs to look at the file name in the spreadsheet and search of that file (without [FONT=&quot]extension[/FONT]), silently open it, extract the required data and close it, move to the next file name until a blank cell. Is it possible for the user to specify the location of the 300 spreadsheet via a input message box? Or do the files need to be in the same location as the Summary Spreadsheet?

File name in Summary Spreadsheet=A1:A300 ish
Location of cells that need capturing from the 300 and pulling into Summary Spreadsheet= C2, B8, B9, F8 and F9

Any help will be appreciated

daverunt · May 10, 2011

Hi,

here's some code you can try.

1. You select the directory from the file open dialog.
2. I've assumed the source files are .xls
3. I've assumed thios macro will be in the Summary Workbook
4. I've assumed the listed filenames are like 'Test1, Test2, without the extension. They are listed in sheet1 column A1:A300 and the results of the 5 cells you posted will be copied to Cell B2 to B6 of the relevant row

Code:

Sub Copy_Data()
'Display Open Dialog to select  file directory
  filenames = Application.GetOpenFilename("Excel Files (*.xls*)," & _
    "*.xls*", 1, "Select Files", "Open", False)
 
'If the user cancels file selection then exit
     If TypeName(filenames) = "Boolean" Then
        Exit Sub
     End If
 
'Set xls file as SourceFile
        SourceFile = Dir("*.xls")
 
Do While SourceFile <> ""
ThisWorkbook.Sheets("Sheet1").Activate
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
 
For Each Dn In Rng
If InStr(Dn.value & ".xls", SourceFile) > 0 Then
Addr = Dn.address(0, 0)
'Open the  File
        Workbooks.Open SourceFile
        Set XLSFile = ActiveWorkbook
 
 
  Range("C2").Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range(Addr).Offset(0, 1)
  Range("B8").Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range(Addr).Offset(0, 2)
  Range("B9").Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range(Addr).Offset(0, 3)
  Range("F8").Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range(Addr).Offset(0, 4)
  Range("F9").Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range(Addr).Offset(0, 5)
 
 
'Close open file
    Windows.Application.CutCopyMode = False
    XLSFile.Close False
 End If
  Next
  SourceFile = Dir
  Loop
 
End Sub

Barlew · May 10, 2011

Hi daverunt, thanks for taking the time to reply. Unfortunatly it falls at the first hurdle. The initial part opens a Select File prompt instead of Browse.

Think it needs something like this, but not sure where to intergrate it?

Code:

Application.FileDialog(msoFileDialogFolderPicker)

Also, although some of the 300ish files will be .xls, some could be xlsm or xlsx. Will your code find all?

daverunt · May 11, 2011

Sorry I haven't used Application.FileDialog(msoFileDialogFolderPicker) so I don't know anything about it.

The file open dialog gets you to a directory where the source files reside. Navigate to the source file directory, select any file in the directory and it will open each Excel file in turn in that directory for processing that matches the filename you are checking.

The Instr func can be reversed:
Rather than compare the source file name with the column A file name with .xls added, it compares the filename in Column A to the source file name so the extension is of no consequence.

Code:

If InStr(SourceFile, Dn.value) > 0 Then

Importing data from other spreadsheets

Barlew

New Member

Excel Facts

daverunt

Well-known Member

Barlew

New Member

daverunt

Well-known Member

Similar threads

Forum statistics

Share this page

Importing data from other spreadsheets

Barlew

New Member

Excel Facts

daverunt

Well-known Member

Barlew

New Member

daverunt

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock