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

  • Question

  • Can a wild card character be used to select and import all Excel Worksheets to Access Tables without having to type each one. The following code works, but I have to repeat the following  for each sheet. DBImportCmd.CommandText = "SELECT * INTO [MS Access;Database=" & "C:\TestCode\TEST.accdb" & "].[*] FROM [*]" DBImportCmd.ExecuteNonQuery() . * 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()
    









    • Edited by VBShaper Monday, March 9, 2020 12:57 AM
    Sunday, March 8, 2020 11:25 PM

All replies

  • For wildcard did you try %  ?

    A code sample which is untested, try if interested tinkering with this, first alter the connection string to adapt to your data provider then look at tacking on a WHERE to the INSERT statement.

    Imports System.Data.OleDb
    
    Public Class Demo
        Public Sub ExcelToAccess(
             accessPathAndFileName As String,
             excelPathAndFileName As String,
             sheetName As String)
    
            '
            ' Change connection string to match your providers
            '
            Dim connectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
                                    excelPathAndFileName &
                                    ";Extended Properties=Excel 8.0;"
    
            Using conn As New OleDbConnection(connectString)
                Using cmd As New OleDbCommand()
                    cmd.Connection = conn
    
                    cmd.CommandText =
                        "INSERT INTO [MS Access;Database=" &
                        accessPathAndFileName &
                        $"].[Persons] SELECT * FROM [{sheetName}$]"
    
                    conn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 9, 2020 12:27 AM
    Moderator
  • I tried % it did not work.
    Monday, March 9, 2020 1:07 AM
  • I tried % it did not work.

    If you can 

    • Create a mock up (meaning with realistic data) of the Excel file, place it in a .zip file (must be a .zip file) and upload (must be this site) OneDrive.
    • Provide the SELECT...WHERE query and a sample of what might be placed in the WHERE condition.

    I will look at it in the morning, if not a .zip and not on OneDrive I will not look at the file.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 9, 2020 1:54 AM
    Moderator
  • Hi,

    Is your problem solved? If so, It would be appreciated if you could share us your solution and then mark it as an answer. If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you will help others find the answer faster.

    If you have anything else about this issue, please feel free to contact us.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 16, 2020 9:04 AM
    Moderator
  • I did not resolve that issue.
    Monday, March 16, 2020 11:18 AM
  • Hi,

    What's the problem you haven't solved yet?

    Please feel free to connect us.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 17, 2020 1:08 AM
    Moderator