Import Excel .XLS file (Access 2003)?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • PW
    #1

    Import Excel .XLS file (Access 2003)?

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

    Thanks,

    -paulw
    Tags: None
    • Roger
      #2
      Re: Import Excel .XLS file (Access 2003)?

      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

        Comment

        • PW
          #3
          Re: Import Excel .XLS file (Access 2003)?

          On Thu, 15 May 2008 18:19:30 -0700 (PDT), Roger
          <lesperancer@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

            Comment

            • Roger
              #4
              Re: Import Excel .XLS file (Access 2003)?

              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

                Comment

                Previous template Next
                Working...