By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,438 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

ADO Importing from an unknown named Excel worksheet

P: n/a
I have the following code that imports from an Excel workbook but I
need to know the worksheet name. If I always want to import from just
the first worksheet in the collection how can I do this?

Ideally I would like the code to refer to something like
"Worksheets(1)" rather than the specific worksheet "Sample data" in my
example.

Or is there a neater way of doing this?
Sub ImportExcel()

Dim cPath As String

cPath = Application.CurrentProject.Path

Dim cSource As String
cSource = cPath & _
IIf(Right$(cPath, 1) <> "\", "\", "") & _
"sample import.xls"

Dim cTarget As String
cTarget = cPath & _
IIf(Right$(cPath, 1) <> "\", "\", "") & _
"imported excel.mdb"

Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection
Dim cSQL As String

With oCon
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& _
"Data Source=" & cSource & ";" & _
"Extended Properties=Excel 8.0"
.Open

' Creates Table and adds records from Excel
cSQL = "SELECT * INTO [tblImport1] " & _
"IN '" & cTarget & "' " & _
"FROM [Sample data$]"

.Execute cSQL
End With

End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
First of all, use Application.Filesearch and then you can simply assign
a string variable to the filename.

Then run your code using the variable as your source file.

Look up filesearch in vba help.

Nov 13 '05 #2

P: n/a
I might have missed something here. Its the worksheets and the first
one in particular I am after not the workbook.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.