VBA, Excel spreadsheet to Access Database
When a column in excel contains cells that contain only digits, and cells
that contain strings then one of these gets ditched when imported. Both with
TransferSpreadsheet and the DAO version.
In my last import the strings were preserved, while all integer fields in
the column were replaced with "#NUM" in the Access Table.
So, how on earth can I import a worksheet with columns something like
123
Olle
Sand
Gruva
475
923
Without this turning into:
#NUM
Olle
Sand
Gruva
#NUM
#NUM
in Access. It is not possible with TransferSpreadsheet, and even with the
DAO solution proposed it looks the same.
Below is the code used to transfer: (Note db and sourcefile are known before
this block.)
Dim xlApp As Object
Dim xlWorkBook As Object
Dim xlWorkSheet As Object
Dim xlWrkNamr As String
Dim tdf As TableDef
Set xlApp = CreateObject("Excel.Application")
Set xlWorkBook = xlApp.Workbooks.Open(fileName:=sourcefile)
Set xlWorkSheet = xlWorkBook.Worksheets(1)
xlWrkName = CStr(xlWorkSheet.Name) & "$"
Set tdf = db.CreateTableDef("LinkedCustomerData")
tdf.Connect = "Excel 8.0;DATABASE=" & sourcefile
tdf.sourceTableName = xlWrkName
db.TableDefs.Append tdf
SQLStr = "SELECT * INTO SourceData FROM LinkedCustomerData"
db.Execute SQLStr
Quote:
> Or you could do something like this, Mathias. This will give you the
first
> row of each column of each worksheet too.
> Dim oExcel As Object
> 'Dim oExcel As Excel.Application
> Dim oExcelWkbk As Object
> Dim oExcelWksht As Object
> Dim oExcelRange As Object
> Dim intColCnt As Integer
> Dim i As Integer
> Set oExcel = CreateObject("Excel.Application")
> oExcel.Workbooks.Open OFFICES_XLS
> oExcel.Application.Visible = True
> Debug.Print "Number of WorkBooks=" & CStr(oExcel.Workbooks.Count)
> For Each oExcelWkbk In oExcel.Workbooks
> Debug.Print "Excel WorkBook Name=" & oExcelWkbk.Name
> Debug.Print " Number of WorkSheets=" &
CStr(oExcelWkbk.WorkSheets.Count)
Quote:
> For Each oExcelWksht In oExcelWkbk.WorkSheets
> Debug.Print " Excel WorkSheet Name=" & oExcelWksht.Name
> Set oExcelRange = oExcelWksht.UsedRange
> intColCnt = oExcelRange.Columns.Count
> Debug.Print " Number of Columns=" & CStr(intColCnt)
> If intColCnt > 0 Then
> For i = 1 To intColCnt
> Debug.Print " Value of Row 1, Column " & CStr(i) & "=" &
> CStr(oExcelRange.Cells(1, i).Value)
> Next i
> End If
> Next
> Next
> > This is becoming something of a periodic posting. :)
> > I want to programatically import the data from an Excel Spreadsheet to
an
> > Access Database table. The user should only need to supply the file name
> for
> > the excel file. The excel file contains only one worksheet.
> > Now, without using the mediocre bugfeast that is TransferSpreadsheet
> method
> > I want to get all the data from the Excel file into Access.
> > This can be done with DAO, connecting a table to the excel file. Now it
> gets
> > interesting, we don't know the name of the worksheet itself, we only
know
> > that it is the only worksheet in the file. Is there a way to get say an
> > enumeration of the worksheets? (Litterature seems to be hinting at the
> fact
> > that you can get all kinds of data out of this connection, if only you
> know
> > the proper things to ask for. Unfortunately no one seems to list the
> proper
> > things...)
> > So, how can I get a known excel file containing only one worksheet, the
> name
> > of which is unknown into a table in Access?
> > TIA
> > Mathias
