请指点如何让user 通过excel convert去mdb的file根据excel file的sheet name

Question

   

我篇写了有关exel file convert 去mdb file 的代码。 以下是我的代码？

 ' delete the file with the same and create a new access file
        If File.Exists("D:\mydata.mdb") Then
            File.Delete("D:\mydata.mdb")
        End If

        Dim _accessData As Access.Application
        _accessData = New Access.Application()
        _accessData.Visible = False
        _accessData.NewCurrentDatabase("D:\mydata.mdb", Access.AcNewDatabaseFormat.acNewDatabaseFormatAccess2000, , , )

        _accessData.CloseCurrentDatabase()
        _accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll)
        _accessData = Nothing

        ' initialize the connect string
        Dim ComboBox As String
        ComboBox = ComboBox1.Text
        Dim Path As String
        Path = OpenFile()
         
        Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        Dim excelBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open(Path)
        Dim excelSheets As String() = New String(excelBook.Worksheets.Count - 1) {}
        Dim i As Integer = 0

        For Each wSheet As Microsoft.Office.Interop.Excel.Worksheet In excelBook.Worksheets
            excelSheets(i) = wSheet.Name
            i += 1
        Next
        ComboBox1.Items.Clear()
        ComboBox1.Items.AddRange(excelSheets)
    
        Dim _conn As String
        _conn = "provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;';"
        Dim _connection As OleDbConnection = New OleDbConnection(_conn)

        'Use OledbCommand object to select all the data from sheet1 and execute an ExecuteNonQuery to import data into .mdb.
        Dim _command As OleDbCommand = New OleDbCommand()
        _command.Connection = _connection

        Try
            '_command.CommandText = "SELECT * INTO [MS Access;Database=D:\mydata.mdb].[Sheet1] FROM [Sheet1$A1:S4]"
            _command.CommandText = "SELECT * INTO [MS Access;Database=D:\mydata.mdb]" + ComboBox + ";From" + ComboBox + "[$A1:S4]"
            _connection.Open()
            _command.ExecuteNonQuery()
            _connection.Close()
            MessageBox.Show("Convert File Successful!")

        Catch e1 As Exception
            MessageBox.Show("Import Failed, correct Column name in the sheet!" & Environment.NewLine & "Error Message:" & Environment.NewLine & e1.Message)
        End Try

想问看不知能否做到excel convert 去mdb file 时，可以选择要convert那个excel sheet file吗 。想了解每个convert excel sheet file都能通过代码设定是什么file名以及是sheet1吗。感恩留言的每一位

Answer 1

Hi christing,

我注意到你在VB英文论坛问了相同的问题，我会在那里提供我的建议。

Best Regards,

Xingyu Zhao

---

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 [e-mail].