Import multiple excel files

[starlight87]

Hi all,

I have been trying to write a macro that will do the following:

- Look to a specific folder in my home drive (nb this may change)
- select all of the excel files that are in that folder
- select various cells in each of those spreadsheets - each spreadsheet is formatted the same with the same structure. The cells are random, e.g. D6, I22, H4, K4, D17, so I cannot select a whole range
- copy these cells and paste them into one row of a database

The problem that I keep coming across is selecting the various cells. If anyone has any suggestions then they would be greatfully received.

 

[vbaInet]

You will need to:
1. Select each cell and individually place them in a single row
2. Copy that row and paste into an Access table as a row

Welcome to the forum.

 

[GinaWhipp]

I use this...

Sub SummaryAll()
'From [URL]http://www.rondebruin.nl/win/s3/win003.htm[/URL]
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook
 
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
 
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
 
On Error Resume Next
Newsh.Name = "Summary-Sheet"
If Err.Number > 0 Then
MsgBox "The Summary sheet already exist in this workbook."
With Application
.DisplayAlerts = False
Newsh.Delete
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Exit Sub
End If
 
RwNum = 1
'The links to the first sheet will start in row 2
 
For Each Sh In Basebook.Worksheets
If Sh.Name <> Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
 
Newsh.Cells(RwNum, 1).Value = Sh.Name
'Copy the sheet name in the A column
 
'Note: Insert your specific cells below 
For Each myCell In Sh.Range("A4,J4,J8,C10:C11,J10:J18,B14:B15,C22,C26:C27,H24:H28,G14,A31:A46") ' <----Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell
End If
Next Sh
 
Newsh.UsedRange.Columns.AutoFit
 
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

To first create a Summary Sheet in from Excel in Excel, then set up a TransferSpreadsheet to import the Summary Page. If you don't want to open the Workbbok then you can run the macro from Access, see...
http://datapigtechnologies.com/blog...-macro-from-access-or-another-excel-workbook/. BUT you will still need to paste the above in Excel.

 

[happy1001]

I use this...

Thanks for the link GinaWhipp