Hi all,
I am trying to import variable data from excel into a database and in particular a table. However the data in Excel is in column and the table in rows. I have the following code borrowed from here:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#WriteFileRst
But I am unsure how to transpose the data, can anyone help please?
Code
Private Sub Command4_Click()
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean
blnEXCEL = False
' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True
' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file from which you will read the data
Set xlw = xlx.Workbooks.Open("C:\Documents and Settings\all users\Desktop", , True) ' opens in read-only mode
' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets("sheet1")
' Replace A1 with the cell reference from which the first data value
' (non-header information) is to be read
Set xlc = xls.Range("E3") ' this is the first cell that contains data
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Set dbs = CurrentDb()
' Replace QueryOrTableName with the real name of the table or query
' that is to receive the data from the worksheet
Set rst = dbs.OpenRecordset("table1", dbOpenDynaset, dbAppendOnly)
' write data to the recordset
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 2
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
' Close the EXCEL file without saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
I am trying to import variable data from excel into a database and in particular a table. However the data in Excel is in column and the table in rows. I have the following code borrowed from here:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#WriteFileRst
But I am unsure how to transpose the data, can anyone help please?
Code
Private Sub Command4_Click()
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean
blnEXCEL = False
' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True
' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file from which you will read the data
Set xlw = xlx.Workbooks.Open("C:\Documents and Settings\all users\Desktop", , True) ' opens in read-only mode
' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets("sheet1")
' Replace A1 with the cell reference from which the first data value
' (non-header information) is to be read
Set xlc = xls.Range("E3") ' this is the first cell that contains data
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Set dbs = CurrentDb()
' Replace QueryOrTableName with the real name of the table or query
' that is to receive the data from the worksheet
Set rst = dbs.OpenRecordset("table1", dbOpenDynaset, dbAppendOnly)
' write data to the recordset
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 2
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1, 0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
' Close the EXCEL file without saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
