Importing Excel

I am trying to import an excel file into Access 97. It looks perfectly all
right but, every time I try to import it, I get to the lst step and am told
that it was not imported due to an error. There is no further explanation.
What are the kinds of things that make this happen?

Thanks from an obvious rookie.

Gordon

You might have better luck if you defined the table you want the data
to go into instead of letting the wizard try to figure things out.
Then when you run the wizard, there's a lot less guessing going on.

How are you trying to import? File, Get External Data...?

I've been doing a lot of importing from Excel lately, and have noticed that
it is the rare spreadsheet that imports cleanly. I've gone to using automation
to loop through rows and insert into a table myself. Are you interested in
this solution? If so, I'll post the code, but it's not a trivial solution and will
require tweaking for each spreadsheet you plan to import.

If you post back, I'll try to clean up the code and post it here.

--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast/

"jillandgordon" <jillandgordon@sbcglobal.net> wrote ...[color=blue]
>I am trying to import an excel file into Access 97. It looks perfectly all right but, every time I try to import it, I
>get to the lst step and am told that it was not imported due to an error. There is no further explanation. What are
>the kinds of things that make this happen?
>
> Thanks from an obvious rookie.
>
> Gordon
>
>[/color]

Aww heck, I decided to write it up anyhow. You can download the code here ...

http://amazecreations.com/datafast/D...tfromexcel.zip

and the bulk of the logic is posted below. You'll need a table to map the columns
from Excel to Access. The code below is looking for one called ImportColumnSpecs.
The download doesn't have a form to launch it, so you'll have to run from the debug
window with a command like this ...

ProcessFileImport "C:\download\sales.xls","sales_import"

Good Luck

Option Compare Database
Option Explicit

Public Function ProcessFileImport(ByVal sFile As String, ByVal sTable As String) As String
On Error GoTo ProcessFileImport_Error

' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

' Access object variables
Dim dbs As DAO.Database
Dim rstRead As DAO.Recordset
Dim rstWrite As DAO.Recordset
Dim fld As DAO.Field

' Declared variables
Dim bytWks As Byte
Dim bytMaxPages As Byte
Dim intStartRow As Integer
Dim strData As String
Dim intMaxRow As Integer
Dim strSQL As String
Dim strMsg As String
Dim intLastCol As Integer
Dim intRow As Integer
Dim strCurrFld As String
Dim intCol As Integer
Dim intLen As Integer
Dim varValue As Variant
Dim lngErrs As Long

Const cPassword As String = "xxx999"

DoCmd.Hourglass True

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sFile)
Set dbs = CurrentDb

' You could loop through sheets, but for this example, we'll just do one.
bytMaxPages = 1

' Sometimes there is header info, so the "Start Row" isn't the first one.
' Set this variable to the first row that contains actual data.
intStartRow = 2

For bytWks = 1 To bytMaxPages
' Initialize variables on each pass
Set wks = Nothing
Set rstRead = Nothing
intRow = intStartRow

' Load current worksheet. Find used range to determine row count.
Set wks = appExcel.Worksheets(bytWks)

' Optionally, you can protect / unprotect with a password
'wks.Unprotect (cPassword)

' You need to figure out how many rows this sheet contains, so to know
' how far down to read. That value is saved in intMaxRow
strData = wks.UsedRange.Address
intMaxRow = CInt(Mid(strData, InStrRev(strData, "$")))
strData = ""

' Go get the list of fields for this worksheet from the Field Map table
strSQL = "SELECT [AccessField], [OrdinalPosition] FROM ImportColumnSpecs " & _
"WHERE [ImportName]='" & sTable & "' ORDER BY [OrdinalPosition] ASC;"
Set rstRead = dbs.OpenRecordset(strSQL, dbOpenDynaset)

' If there is a mistake and no specification exists, then exit with message
If rstRead.BOF And rstRead.EOF Then
strMsg = "The import spec was not found. Cannot continue."
MsgBox strMsg, vbExclamation, "Error"
Else
rstRead.MoveLast
rstRead.MoveFirst
intLastCol = rstRead.RecordCount

' The name of the import and destination table should be the same for this
' code to function correctly.
Set rstWrite = dbs.OpenRecordset(sTable, dbOpenDynaset)
Do Until intRow > intMaxRow
' Check row to be sure it is not blank. If so, skip the row
For intCol = 1 To intLastCol
strData = strData & Trim(Nz(wks.Cells(intRow, intCol), ""))
Next

If strData = "" Then
intRow = intRow + 1
Else
rstWrite.AddNew
Do Until rstRead.EOF
' Loop through the list of fields, processing them one at a time.
' Grab the field name to simplify code and improve performance.
strCurrFld = Nz(rstRead!AccessField, "")
intCol = rstRead!OrdinalPosition

' Make sure that text fields truncate data at prescribed limits.
' Users may not enter supply more text than the fields can contain.
If dbs.TableDefs(sTable).Fields(strCurrFld).Type = dbText Then
intLen = dbs.TableDefs(sTable).Fields(strCurrFld).Size
varValue = Left(Nz(wks.Cells(intRow, intCol), ""), intLen)
Else
varValue = wks.Cells(intRow, intCol)
End If

' The database schema requires that empty fields contain NULL, not
' the empty string.
If varValue = "" Then varValue = Null

' Handle date columns. Sometimes Excel doesn't format them as dates
If InStr(1, strCurrFld, "Date") > 0 Then
If Not IsDate(varValue) Then
If IsNumeric(varValue) Then
On Error Resume Next
varValue = CDate(varValue)
If Err.Number <> 0 Then
If Left(CStr(varValue), 2) = "20" Then
varValue = CDate(Left(varValue, 4) & "-" & _
Mid(varValue, 5, 2) & "-" & _
Right(varValue, 2))
End If
Err.Clear
End If
On Error GoTo ProcessFileImport_Error
Else
lngErrs = lngErrs + 1
varValue = Null
End If
End If
rstWrite.Fields(strCurrFld) = varValue
Else
' If not a date field, then just write the value to the rst
rstWrite.Fields(strCurrFld) = varValue
End If

rstRead.MoveNext
Loop
If Not rstRead.BOF Then rstRead.MoveFirst

rstWrite.Update

' Reset the variables for processing of the next record.
strData = ""
intRow = intRow + 1
'Debug.Print intRow
End If
Loop
Set wks = Nothing
End If
Next

Exit_Here:
' Report results
strMsg = "finished"
ProcessFileImport = strMsg
' Cleanup all objects (resume next on errors)

On Error Resume Next
Set wks = Nothing
wbk.Close True
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rstRead = Nothing
Set rstWrite = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

ProcessFileImport_Error:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Function

--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast/

"Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote in ...[color=blue]
> I've been doing a lot of importing from Excel lately, and have noticed that
> it is the rare spreadsheet that imports cleanly. I've gone to using automation
> to loop through rows and insert into a table myself. Are you interested in
> this solution? If so, I'll post the code, but it's not a trivial solution and will
> require tweaking for each spreadsheet you plan to import.
>
> If you post back, I'll try to clean up the code and post it here.
>
> --
>[/color]

I'm trying to automate the import of an Excel spreadsheet into Access. Something I've never done before. I searched the ng and came across the post below. The code seemed to be just what the doctor ordered.

Ultimately I want to ensure the spreadsheet headings match the import table, and that a dummy row of data can be inserted into the sheet to ensure that alphanumeric fields load into text; right now the first 9 rows have numeric data and the import kicks all alphanumeric data from the field (in spite of the import table being predefined and having a text format).

However, when I invoke the code, the function bombs on bad keywords. Excel.Application Excel.Workbook & Excel.Worksheet , for example. Obviously not intended for Access 97.

Is there an alternate mechanism to do this in A97? If not, what version is this meant for?

I'm hoping there's an easy way to do this. Any help would be appreciated.
--
Brian
[color=blue][color=green][color=darkred]
>>> Danny J. Lesandrini<dlesandrini@hotmail.com> September 1, 2005 9:16 am >>>[/color][/color][/color]
Aww heck, I decided to write it up anyhow. You can download the code here ...

http://amazecreations.com/datafast/D...tfromexcel.zip

and the bulk of the logic is posted below. You'll need a table to map the columns
from Excel to Access. The code below is looking for one called ImportColumnSpecs.
The download doesn't have a form to launch it, so you'll have to run from the debug
window with a command like this ...

ProcessFileImport "C:\download\sales.xls","sales_import"

Good Luck

Option Compare Database
Option Explicit

Public Function ProcessFileImport(ByVal sFile As String, ByVal sTable As String) As String
On Error GoTo ProcessFileImport_Error

' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

' Access object variables
Dim dbs As DAO.Database
Dim rstRead As DAO.Recordset
Dim rstWrite As DAO.Recordset
Dim fld As DAO.Field

' Declared variables
Dim bytWks As Byte
Dim bytMaxPages As Byte
Dim intStartRow As Integer
Dim strData As String
Dim intMaxRow As Integer
Dim strSQL As String
Dim strMsg As String
Dim intLastCol As Integer
Dim intRow As Integer
Dim strCurrFld As String
Dim intCol As Integer
Dim intLen As Integer
Dim varValue As Variant
Dim lngErrs As Long

Const cPassword As String = "xxx999"

DoCmd.Hourglass True

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sFile)
Set dbs = CurrentDb

' You could loop through sheets, but for this example, we'll just do one.
bytMaxPages = 1

' Sometimes there is header info, so the "Start Row" isn't the first one.
' Set this variable to the first row that contains actual data.
intStartRow = 2

For bytWks = 1 To bytMaxPages
' Initialize variables on each pass
Set wks = Nothing
Set rstRead = Nothing
intRow = intStartRow

' Load current worksheet. Find used range to determine row count.
Set wks = appExcel.Worksheets(bytWks)

' Optionally, you can protect / unprotect with a password
'wks.Unprotect (cPassword)

' You need to figure out how many rows this sheet contains, so to know
' how far down to read. That value is saved in intMaxRow
strData = wks.UsedRange.Address
intMaxRow = CInt(Mid(strData, InStrRev(strData, "$")))
strData = ""

' Go get the list of fields for this worksheet from the Field Map table
strSQL = "SELECT [AccessField], [OrdinalPosition] FROM ImportColumnSpecs " & _
"WHERE [ImportName]='" & sTable & "' ORDER BY [OrdinalPosition] ASC;"
Set rstRead = dbs.OpenRecordset(strSQL, dbOpenDynaset)

' If there is a mistake and no specification exists, then exit with message
If rstRead.BOF And rstRead.EOF Then
strMsg = "The import spec was not found. Cannot continue."
MsgBox strMsg, vbExclamation, "Error"
Else
rstRead.MoveLast
rstRead.MoveFirst
intLastCol = rstRead.RecordCount

' The name of the import and destination table should be the same for this
' code to function correctly.
Set rstWrite = dbs.OpenRecordset(sTable, dbOpenDynaset)
Do Until intRow > intMaxRow
' Check row to be sure it is not blank. If so, skip the row
For intCol = 1 To intLastCol
strData = strData & Trim(Nz(wks.Cells(intRow, intCol), ""))
Next

If strData = "" Then
intRow = intRow + 1
Else
rstWrite.AddNew
Do Until rstRead.EOF
' Loop through the list of fields, processing them one at a time.
' Grab the field name to simplify code and improve performance.
strCurrFld = Nz(rstRead!AccessField, "")
intCol = rstRead!OrdinalPosition

' Make sure that text fields truncate data at prescribed limits.
' Users may not enter supply more text than the fields can contain.
If dbs.TableDefs(sTable).Fields(strCurrFld).Type = dbText Then
intLen = dbs.TableDefs(sTable).Fields(strCurrFld).Size
varValue = Left(Nz(wks.Cells(intRow, intCol), ""), intLen)
Else
varValue = wks.Cells(intRow, intCol)
End If

' The database schema requires that empty fields contain NULL, not
' the empty string.
If varValue = "" Then varValue = Null

' Handle date columns. Sometimes Excel doesn't format them as dates
If InStr(1, strCurrFld, "Date") > 0 Then
If Not IsDate(varValue) Then
If IsNumeric(varValue) Then
On Error Resume Next
varValue = CDate(varValue)
If Err.Number <> 0 Then
If Left(CStr(varValue), 2) = "20" Then
varValue = CDate(Left(varValue, 4) & "-" & _
Mid(varValue, 5, 2) & "-" & _
Right(varValue, 2))
End If
Err.Clear
End If
On Error GoTo ProcessFileImport_Error
Else
lngErrs = lngErrs + 1
varValue = Null
End If
End If
rstWrite.Fields(strCurrFld) = varValue
Else
' If not a date field, then just write the value to the rst
rstWrite.Fields(strCurrFld) = varValue
End If

rstRead.MoveNext
Loop
If Not rstRead.BOF Then rstRead.MoveFirst

rstWrite.Update

' Reset the variables for processing of the next record.
strData = ""
intRow = intRow + 1
'Debug.Print intRow
End If
Loop
Set wks = Nothing
End If
Next

Exit_Here:
' Report results
strMsg = "finished"
ProcessFileImport = strMsg
' Cleanup all objects (resume next on errors)

On Error Resume Next
Set wks = Nothing
wbk.Close True
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rstRead = Nothing
Set rstWrite = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

ProcessFileImport_Error:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Function

--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast/

"Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote in ...[color=blue]
> I've been doing a lot of importing from Excel lately, and have noticed that
> it is the rare spreadsheet that imports cleanly. I've gone to using automation
> to loop through rows and insert into a table myself. Are you interested in
> this solution? If so, I'll post the code, but it's not a trivial solution and will
> require tweaking for each spreadsheet you plan to import.
>
> If you post back, I'll try to clean up the code and post it here.
>
> --
>[/color]

You need to set a reference to Excel. It doesn't matter which version you have, but the reference has to be set. Do
that and try compiling the code again.

--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast/

"Brian Graham" <GrahamB@psac-afpc.com> wrote in message news:pZERe.8022$2F1.513591@news20.bellglobal.com.. .[color=blue]
> I'm trying to automate the import of an Excel spreadsheet into Access. Something I've never done before. I searched
> the ng and came across the post below. The code seemed to be just what the doctor ordered.
>
> Ultimately I want to ensure the spreadsheet headings match the import table, and that a dummy row of data can be
> inserted into the sheet to ensure that alphanumeric fields load into text; right now the first 9 rows have numeric
> data and the import kicks all alphanumeric data from the field (in spite of the import table being predefined and
> having a text format).
>
> However, when I invoke the code, the function bombs on bad keywords. Excel.Application Excel.Workbook &
> Excel.Worksheet , for example. Obviously not intended for Access 97.
>
> Is there an alternate mechanism to do this in A97? If not, what version is this meant for?
>
> I'm hoping there's an easy way to do this. Any help would be appreciated.
> --
> Brian
>[color=green][color=darkred]
>>>> Danny J. Lesandrini<dlesandrini@hotmail.com> September 1, 2005 9:16 am >>>[/color][/color]
> Aww heck, I decided to write it up anyhow. You can download the code here ...
>
> http://amazecreations.com/datafast/D...tfromexcel.zip
>
> and the bulk of the logic is posted below. You'll need a table to map the columns
> from Excel to Access. The code below is looking for one called ImportColumnSpecs.
> The download doesn't have a form to launch it, so you'll have to run from the debug
> window with a command like this ...
>
> ProcessFileImport "C:\download\sales.xls","sales_import"
>
> Good Luck
>
>
>
> Option Compare Database
> Option Explicit
>
> Public Function ProcessFileImport(ByVal sFile As String, ByVal sTable As String) As String
> On Error GoTo ProcessFileImport_Error
>
> ' Excel object variables
> Dim appExcel As Excel.Application
> Dim wbk As Excel.Workbook
> Dim wks As Excel.Worksheet
>
> ' Access object variables
> Dim dbs As DAO.Database
> Dim rstRead As DAO.Recordset
> Dim rstWrite As DAO.Recordset
> Dim fld As DAO.Field
>
> ' Declared variables
> Dim bytWks As Byte
> Dim bytMaxPages As Byte
> Dim intStartRow As Integer
> Dim strData As String
> Dim intMaxRow As Integer
> Dim strSQL As String
> Dim strMsg As String
> Dim intLastCol As Integer
> Dim intRow As Integer
> Dim strCurrFld As String
> Dim intCol As Integer
> Dim intLen As Integer
> Dim varValue As Variant
> Dim lngErrs As Long
>
> Const cPassword As String = "xxx999"
>
> DoCmd.Hourglass True
>
> ' Create the Excel Applicaiton, Workbook and Worksheet and Database object
> Set appExcel = Excel.Application
> Set wbk = appExcel.Workbooks.Open(sFile)
> Set dbs = CurrentDb
>
> ' You could loop through sheets, but for this example, we'll just do one.
> bytMaxPages = 1
>
> ' Sometimes there is header info, so the "Start Row" isn't the first one.
> ' Set this variable to the first row that contains actual data.
> intStartRow = 2
>
> For bytWks = 1 To bytMaxPages
> ' Initialize variables on each pass
> Set wks = Nothing
> Set rstRead = Nothing
> intRow = intStartRow
>
> ' Load current worksheet. Find used range to determine row count.
> Set wks = appExcel.Worksheets(bytWks)
>
> ' Optionally, you can protect / unprotect with a password
> 'wks.Unprotect (cPassword)
>
> ' You need to figure out how many rows this sheet contains, so to know
> ' how far down to read. That value is saved in intMaxRow
> strData = wks.UsedRange.Address
> intMaxRow = CInt(Mid(strData, InStrRev(strData, "$")))
> strData = ""
>
> ' Go get the list of fields for this worksheet from the Field Map table
> strSQL = "SELECT [AccessField], [OrdinalPosition] FROM ImportColumnSpecs " & _
> "WHERE [ImportName]='" & sTable & "' ORDER BY [OrdinalPosition] ASC;"
> Set rstRead = dbs.OpenRecordset(strSQL, dbOpenDynaset)
>
> ' If there is a mistake and no specification exists, then exit with message
> If rstRead.BOF And rstRead.EOF Then
> strMsg = "The import spec was not found. Cannot continue."
> MsgBox strMsg, vbExclamation, "Error"
> Else
> rstRead.MoveLast
> rstRead.MoveFirst
> intLastCol = rstRead.RecordCount
>
> ' The name of the import and destination table should be the same for this
> ' code to function correctly.
> Set rstWrite = dbs.OpenRecordset(sTable, dbOpenDynaset)
> Do Until intRow > intMaxRow
> ' Check row to be sure it is not blank. If so, skip the row
> For intCol = 1 To intLastCol
> strData = strData & Trim(Nz(wks.Cells(intRow, intCol), ""))
> Next
>
> If strData = "" Then
> intRow = intRow + 1
> Else
> rstWrite.AddNew
> Do Until rstRead.EOF
> ' Loop through the list of fields, processing them one at a time.
> ' Grab the field name to simplify code and improve performance.
> strCurrFld = Nz(rstRead!AccessField, "")
> intCol = rstRead!OrdinalPosition
>
> ' Make sure that text fields truncate data at prescribed limits.
> ' Users may not enter supply more text than the fields can contain.
> If dbs.TableDefs(sTable).Fields(strCurrFld).Type = dbText Then
> intLen = dbs.TableDefs(sTable).Fields(strCurrFld).Size
> varValue = Left(Nz(wks.Cells(intRow, intCol), ""), intLen)
> Else
> varValue = wks.Cells(intRow, intCol)
> End If
>
> ' The database schema requires that empty fields contain NULL, not
> ' the empty string.
> If varValue = "" Then varValue = Null
>
> ' Handle date columns. Sometimes Excel doesn't format them as dates
> If InStr(1, strCurrFld, "Date") > 0 Then
> If Not IsDate(varValue) Then
> If IsNumeric(varValue) Then
> On Error Resume Next
> varValue = CDate(varValue)
> If Err.Number <> 0 Then
> If Left(CStr(varValue), 2) = "20" Then
> varValue = CDate(Left(varValue, 4) & "-" & _
> Mid(varValue, 5, 2) & "-" & _
> Right(varValue, 2))
> End If
> Err.Clear
> End If
> On Error GoTo ProcessFileImport_Error
> Else
> lngErrs = lngErrs + 1
> varValue = Null
> End If
> End If
> rstWrite.Fields(strCurrFld) = varValue
> Else
> ' If not a date field, then just write the value to the rst
> rstWrite.Fields(strCurrFld) = varValue
> End If
>
> rstRead.MoveNext
> Loop
> If Not rstRead.BOF Then rstRead.MoveFirst
>
> rstWrite.Update
>
> ' Reset the variables for processing of the next record.
> strData = ""
> intRow = intRow + 1
> 'Debug.Print intRow
> End If
> Loop
> Set wks = Nothing
> End If
> Next
>
> Exit_Here:
> ' Report results
> strMsg = "finished"
> ProcessFileImport = strMsg
> ' Cleanup all objects (resume next on errors)
>
> On Error Resume Next
> Set wks = Nothing
> wbk.Close True
> Set wbk = Nothing
> appExcel.Quit
> Set appExcel = Nothing
> Set rstRead = Nothing
> Set rstWrite = Nothing
> Set dbs = Nothing
> DoCmd.Hourglass False
> Exit Function
>
> ProcessFileImport_Error:
> MsgBox Err.Description, vbExclamation, "Error"
> Resume Exit_Here
>
> End Function
>
>
>
>
> --
>
> Danny J. Lesandrini
> dlesandrini@hotmail.com
> http://amazecreations.com/datafast/
>
>
>
> "Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote in ...[color=green]
>> I've been doing a lot of importing from Excel lately, and have noticed that
>> it is the rare spreadsheet that imports cleanly. I've gone to using automation
>> to loop through rows and insert into a table myself. Are you interested in
>> this solution? If so, I'll post the code, but it's not a trivial solution and will
>> require tweaking for each spreadsheet you plan to import.
>>
>> If you post back, I'll try to clean up the code and post it here.
>>
>> --
>>[/color]
>
>
>
>[/color]

I wish I knew how... ??
[color=blue][color=green][color=darkred]
>>> Danny J. Lesandrini<dlesandrini@hotmail.com> September 1, 2005 1:07 pm >>>[/color][/color][/color]
You need to set a reference to Excel. It doesn't matter which version you have, but the reference has to be set. Do
that and try compiling the code again.

"Brian Graham" <GrahamB@psac-afpc.com> wrote in message
[color=blue]
> I'm trying to automate the import of an Excel spreadsheet into Access. Something I've never done before.
> However, when I invoke the code, the function bombs on bad keywords. Excel.Application Excel.Workbook &
> Excel.Worksheet[/color]

Sorry about that. Didn't realize you haven't set references yet. You
will become very familiar with this in time.

Open any code module and select References from the Tools menu.
A dialog box will appear with some items checked (selected) at the
top of the list. Leave those alone and scroll down until you find one
named Microsoft Excel and select (check) the box. Save and close

Now, Choose Compile from the Debug menu and see if it throws any
errors.

To test my code, press Ctl+G to display the IMMEDIATE window and
type the following into the window ...

? ProcessFileImport ("C:\download\sales.xls","sales_import")

The Question Mark is shorthand for "Please print the result." It's
followed by the name of the procedure and it's arguments. The
first arg is the file path and name. The second is the name of the
table into which you are importing the data in the file.

My code is designed for my table and file. If you want to import
your own, you'll need to modify some things. It's not trivial, but
you can figure it out. Just follow the example for the sales table
and make the appropriate entries in table: ImportColumnSpecs.

Good luck
--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast/

"Brian Graham" <GrahamB@psac-afpc.com> wrote in message news:x5HRe.8969$2F1.523385@news20.bellglobal.com.. .[color=blue]
>I wish I knew how... ??
>[color=green][color=darkred]
>>>> Danny J. Lesandrini<dlesandrini@hotmail.com> September 1, 2005 1:07 pm >>>[/color][/color]
> You need to set a reference to Excel. It doesn't matter which version you have, but the reference has to be set. Do
> that and try compiling the code again.
>
> "Brian Graham" <GrahamB@psac-afpc.com> wrote in message
>[color=green]
>> I'm trying to automate the import of an Excel spreadsheet into Access. Something I've never done before.
>> However, when I invoke the code, the function bombs on bad keywords. Excel.Application Excel.Workbook &
>> Excel.Worksheet[/color][/color]

Thanks, Danny! Its always the obvious that's overlooked, isn't it? References are something I've managed to avoid, despite working on fairly significant apps for several years now. But there're always previously unused capabilities to learn about..

Selecting the reference as you indicated got me past the first error. Now I'm getting an undefined variable InStrRev in ProcessInputFile, but I've just DL'd the zip file. We'll see how we fare from here.

Thanks again!
--
Brian
[color=blue][color=green][color=darkred]
>>> Danny J. Lesandrini<dlesandrini@hotmail.com> September 1, 2005 2:35 pm >>>[/color][/color][/color]
Sorry about that. Didn't realize you haven't set references yet. You
will become very familiar with this in time.

Open any code module and select References from the Tools menu.
A dialog box will appear with some items checked (selected) at the
top of the list. Leave those alone and scroll down until you find one
named Microsoft Excel and select (check) the box. Save and close

Now, Choose Compile from the Debug menu and see if it throws any
errors.

To test my code, press Ctl+G to display the IMMEDIATE window and
type the following into the window ...

? ProcessFileImport ("C:\download\sales.xls","sales_import")

The Question Mark is shorthand for "Please print the result." It's
followed by the name of the procedure and it's arguments. The
first arg is the file path and name. The second is the name of the
table into which you are importing the data in the file.

My code is designed for my table and file. If you want to import
your own, you'll need to modify some things. It's not trivial, but
you can figure it out. Just follow the example for the sales table
and make the appropriate entries in table: ImportColumnSpecs.

Good luck
--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast/

"Brian Graham" <GrahamB@psac-afpc.com> wrote in message news:x5HRe.8969$2F1.523385@news20.bellglobal.com.. .[color=blue]
>I wish I knew how... ??
>[color=green][color=darkred]
>>>> Danny J. Lesandrini<dlesandrini@hotmail.com> September 1, 2005 1:07 pm >>>[/color][/color]
> You need to set a reference to Excel. It doesn't matter which version you have, but the reference has to be set. Do
> that and try compiling the code again.
>
> "Brian Graham" <GrahamB@psac-afpc.com> wrote in message
>[color=green]
>> I'm trying to automate the import of an Excel spreadsheet into Access. Something I've never done before.
>> However, when I invoke the code, the function bombs on bad keywords. Excel.Application Excel.Workbook &
>> Excel.Worksheet[/color][/color]

If you're using Access 97, you won't have that function. Try replacing it with this ...

Function LastInStr(strSearched As String, strSought As String) As Integer
On Error Resume Next

'//////////////////////////////////////////////////////////////////////////
'
' This function finds the last instance of a character within
' a string of characters and returns an integer representing
' the final position of the desired character.
'
' Typically, this function us used to find the final "\" in
' a file path string
'
'//////////////////////////////////////////////////////////////////////////

Dim intCurrVal As Integer
Dim intLastPosition As Integer

intCurrVal = InStr(strSearched, strSought)
Do Until intCurrVal = 0
intLastPosition = intCurrVal
intCurrVal = InStr(intLastPosition + 1, strSearched, strSought)
Loop
LastInStr = intLastPosition

End Function

--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast/

"Brian Graham" <GrahamB@psac-afpc.com> wrote in message news:iqIRe.7421$884.731826@news20.bellglobal.com.. .[color=blue]
> Thanks, Danny! Its always the obvious that's overlooked, isn't it? References are something I've managed to avoid,
> despite working on fairly significant apps for several years now. But there're always previously unused capabilities
> to learn about..
>
> Selecting the reference as you indicated got me past the first error. Now I'm getting an undefined variable InStrRev
> in ProcessInputFile, but I've just DL'd the zip file. We'll see how we fare from here.
>
> Thanks again!
> --
> Brian
>[color=green][color=darkred]
>>>> Danny J. Lesandrini<dlesandrini@hotmail.com> September 1, 2005 2:35 pm >>>[/color][/color]
> Sorry about that. Didn't realize you haven't set references yet. You
> will become very familiar with this in time.
>
> Open any code module and select References from the Tools menu.
> A dialog box will appear with some items checked (selected) at the
> top of the list. Leave those alone and scroll down until you find one
> named Microsoft Excel and select (check) the box. Save and close
>
> Now, Choose Compile from the Debug menu and see if it throws any
> errors.
>
> To test my code, press Ctl+G to display the IMMEDIATE window and
> type the following into the window ...
>
> ? ProcessFileImport ("C:\download\sales.xls","sales_import")
>
> The Question Mark is shorthand for "Please print the result." It's
> followed by the name of the procedure and it's arguments. The
> first arg is the file path and name. The second is the name of the
> table into which you are importing the data in the file.
>
> My code is designed for my table and file. If you want to import
> your own, you'll need to modify some things. It's not trivial, but
> you can figure it out. Just follow the example for the sales table
> and make the appropriate entries in table: ImportColumnSpecs.
>
> Good luck
> --
>
> Danny J. Lesandrini
> dlesandrini@hotmail.com
> http://amazecreations.com/datafast/
>
>
>
> "Brian Graham" <GrahamB@psac-afpc.com> wrote in message news:x5HRe.8969$2F1.523385@news20.bellglobal.com.. .[color=green]
>>I wish I knew how... ??
>>[color=darkred]
>>>>> Danny J. Lesandrini<dlesandrini@hotmail.com> September 1, 2005 1:07 pm >>>[/color]
>> You need to set a reference to Excel. It doesn't matter which version you have, but the reference has to be set. Do
>> that and try compiling the code again.
>>
>> "Brian Graham" <GrahamB@psac-afpc.com> wrote in message
>>[color=darkred]
>>> I'm trying to automate the import of an Excel spreadsheet into Access. Something I've never done before.
>>> However, when I invoke the code, the function bombs on bad keywords. Excel.Application Excel.Workbook &
>>> Excel.Worksheet[/color][/color]
>
>
>
>[/color]

Importing Excel

re: Importing Excel

re: Importing Excel

re: Importing Excel

re: Importing Excel

re: Importing Excel

re: Importing Excel

re: Importing Excel

re: Importing Excel

re: Importing Excel

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about