Import Excel .XLS file (Access 2003)?

[PW]

Does the worksheet have to be exported to a CSV file first or can
Access import directly from an Excel workbook (.xls)?

Thanks,

-paulw

[Roger]

On May 15, 3:45*pm, PW <paulremove_wil liamson...@remo vehotmail.com>
wrote:

Does the worksheet have to be exported to a CSV file first or can
Access import directly from an Excel workbook (.xls)?

Thanks,

-paulw

access with handle either based on how you create the link table

[PW]

On Thu, 15 May 2008 18:19:30 -0700 (PDT), Roger
<le*********@na tpro.comwrote:

>On May 15, 3:45*pm, PW <paulremove_wil liamson...@remo vehotmail.com>
wrote:

>Does the worksheet have to be exported to a CSV file first or can
Access import directly from an Excel workbook (.xls)?

Thanks,

-paulw

access with handle either based on how you create the link table

Would you explain "create the link table" further"? Your response to
my previous post:

>create a linked table to your excel worksheet

then use an insert query to populate your table
<

I did not know a link could be pointed to an Excel file. I do not
know the name of the file until the user selects it in a dialog box
like so:

strFilter = ahtAddFilterIte m(strFilter, "Comma Seperated Value Files
(*.csv)", "*.CSV")

strReturnVal = ahtCommonFileOp enSave(Filter:= strFilter, _
OpenFile:=True, _
DialogTitle:="P lease select the document...", _
Flags:=ahtOFN_H IDEREADONLY)

How would I then link to that file in code?

Thanks!

-pw

[Roger]

On May 15, 8:16*pm, PW <paulremove_wil liamson...@remo vehotmail.com>
wrote:

On Thu, 15 May 2008 18:19:30 -0700 (PDT), Roger

<lesperan...@na tpro.comwrote:

On May 15, 3:45*pm, PW <paulremove_wil liamson...@remo vehotmail.com>
wrote:

Does the worksheet have to be exported to a CSV file first or can
Access import directly from an Excel workbook (.xls)?

Thanks,

-paulw

access with handle either based on how you create the link table

Would you explain "create the link table" further"? *Your response to
my previous post:

create a linked table to your excel worksheet

then use an insert query to populate your table
<

I did not know a link could be pointed to an Excel file. *I do not
know the name of the file until the user selects it in a dialog box
like so:

strFilter = ahtAddFilterIte m(strFilter, "Comma Seperated Value Files
(*.csv)", "*.CSV")

strReturnVal = ahtCommonFileOp enSave(Filter:= strFilter, _
OpenFile:=True, _
DialogTitle:="P lease select the document...", _
Flags:=ahtOFN_H IDEREADONLY)

How would I then link to that file in code?

Thanks!

-pw

create a linked table to an existing csv file, called tblCsv
then use the code below

I used an xls file, so your connect string may be different, use this
in a debug window to get the correct string
?currentdb.Tabl eDefs("tblCSV") .Connect

Dim db As DAO.Database
Dim tbl As DAO.TableDef

Set db = CurrentDb
Set tbl = db.TableDefs("t blCSV")
With tbl
.Connect = "Excel 5.0;HDR=YES;IME X=2;DATABASE=" & strReturnVal
.RefreshLink
End With
End Sub