Geek asks and answers

VBA to import Excel spreadsheet in Access in turn

I am debugging the code, and I need to find out where DoCmd.TransferSpreadsheet acImport, , ".....
so I decided to import it “manually” in turn to see where it falls.

I assume this is what I am looking for:

mySpreadSheet = ConnectTo(Spreadsheet.xlsx)
while(!mySpreadSheet.EOF)
   get(mySpreadSheet.nextLine)
   SQL("UPDATE MyTable with mySpreadSheet.nextLine")

I tried Googling to no avail. Any help is much appreciated!

Additional Information:

  • The column names of the table and access table are identical.
  • Each data type is nvarchar (MAX) (or "Memo" because Access calls it)
  • The table is a linked table with SQL Server 2008
+3
source share
5 answers

ADO (. Excel 2007), ADO ( . fooobar.com/questions/1798701/...

SQL INSERT, .

strInsert = "INSERT INTO MyTable (first_field, second_field) VALUES ('" & -
    rs2.Field(0).Value & "', '" & rs2.Field(1).Value & "');"
Debug.Print strInsert
CurrentDb.Execute strInsert, dbFailonerror

, first_field, second_field - . , .

, , . , , , Access. ( , SQL Server.) , SQL Server Management Studio .

+5

ADO , ( HansUp). , Excel, . , .

Public Sub LoadExcelToAccess(xlPath As String)
'uses late binding to open excel workbook and open it line by line

'make reference to Microsoft Excel xx.x Object Model to use native functions, requires early binding however

    Dim xlApp As Object 'Excel.Application
    Dim xlWrk As Object 'Excel.Workbook
    Dim xlSheet As Object 'Excel.Worksheet
    Dim i As Long
    Dim sql As String

    Set xlApp = VBA.CreateObject("Excel.Application")

    'toggle visibility for debugging
    xlApp.Visible = False

    Set xlWrk = xlApp.Workbooks.Open(xlPath)
    Set xlSheet = xlWrk.Sheets("Sheet1") 'modify to your perticular sheet

    'depends on what your trying to do with the sheet
    For i = 1 To 10

        'quick and dirty:  best to load items into custom class collection, then do processing there
        sql = "Insert Into [Temp] (Col1) VALUES (" & xlSheet.Cells(i, 1).Value & ")"
        DoCmd.RunSQL sql
    Next i

    'make sure to dispose of objects
    xlWrk.Close
    xlApp.Quit

    Set xlSheet = Nothing
    Set xlWrk = Nothing
    Set xlApp = Nothing
End Sub
+10

:

  • Access . Access 2007 " " " Excel". datatable, datatable Excel. "Excel", Access ( ).

  • Docmd.TransferSpreadsheet. , , , ( ). , , , Access Excel.

, . .

0

To troubleshoot, try connecting to the spreadsheet and see what problems you will encounter, including incorrect data, when you view them in the data table view.

0
source

You can also try simply copying Excel data to the clipboard and pasting it into your Access table. No matter what fails, it will be written to the Insert Errors table through Access.

0
source

More articles:


All Articles