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

  • 问题

  •    

    我篇写了有关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吗。感恩留言的每一位



    2020年3月21日 7:19

全部回复

  • 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 MSDNFSF@microsoft.com.

    2020年3月23日 6:14
    版主