Hi:)

First time poster here, so apologies in advance....;)

I am looking for code to import multiple spreadsheets into on single sheet from different locations on the network.

The spreadsheets will all have similar column headers but may have multiple tabs.

Also, is there a way of making the import start at a specific row?

Many thanks

Hi

The easies way is to have query tables. This will be the dynamic replica of the data from individual sheets

Here is an example that you can finetune for your requirement

Sub Excel_QueryTable()

Dim oCN As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String

Dim qt As QueryTable

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SubFile.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCN = New ADODB.Connection
oCN.ConnectionString = ConnString
oCN.Open

SQL = "Select * from [Sheet1$]"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCN
oRS.Open

Set qt = Worksheets(1).QueryTables.Add(Connection:=oRS, _
        Destination:=Range("B1"))

qt.Refresh

If oRS.State <> adStateClosed Then
    oRS.Close
End If


If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCN Is Nothing Then Set oCN = Nothing

End Sub

Regards
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Hi,

Thanks heaps for your reply, however I get a Compile Error; User Defined type not defined.

Thanks man

Have you added the ActiveX Data Objects in your reference. It might be probably because of that.

Try adding the reference and if the error still comes, please post the line where the error comes

Cheers
Shasur


http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Hey, thanks for that Shasur,

However, it will only get the file I specify, but will bring in all fields of that file ok, so next question;

Many thanks again;)

Sub Excel_QueryTable()

Dim oCN As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String

Dim qt As QueryTable

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\A555714\Desktop\New App\spreadsheet import\try\1copy.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCN = New ADODB.Connection
oCN.ConnectionString = ConnString
oCN.Open

SQL = "Select * from [RCM$]"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCN
oRS.Open

Set qt = Worksheets(1).QueryTables.Add(Connection:=oRS, _
        Destination:=Range("B1"))

qt.Refresh

If oRS.State <> adStateClosed Then
    oRS.Close
End If


If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCN Is Nothing Then Set oCN = Nothing

End Sub

You can filter the columns using the query

If you want to select a range here is the hint

"Select Min([Entry Date]) as MinDate From [" & sSheetName & "$A13:J" & CStr(lMaxRow) & "]"

where lMAxRow is my ending row and sSheetName is the sheet I wanted.

WildCards .. hmm .. I haven't tried it yet

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)