Can a wild card character be used to select and import all Ecel Worksheets to Access Tables ?

[VBShaper]

Can a wild card character be used to select and import all Excel Worksheets to Access Tables without having to type each one. * did not work.

Dim DBImportConn As New OleDbConnection
Dim DBImportCmd As New OleDbCommand
Dim ImpAccessData As Application
Dim FilePath As String
ImpAccessData = Nothing　　
FilePath = TextBox1.Text
If DBImportConn.State = ConnectionState.Open Then DBImportConn.Close()
DBImportConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
DBImportConn.Open()
DBImportCmd = DBImportConn.CreateCommand

DBImportCmd.CommandText = "SELECT * INTO [MS Access;Database=" & "C:\TestCode\TEST.accdb" & "].[SectionA] FROM [SectionA$]"
DBImportCmd.ExecuteNonQuery()

DBImportCmd.CommandText = "SELECT * INTO [MS Access;Database=" & "C:\TestCode\TEST.accdb" & "].[SEC1234] FROM [SEC1234$]"
DBImportCmd.ExecuteNonQuery()

DBImportConn.Close()








Continue reading...