Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Importing from Excel (Ex 2002)

    I can manually import data (one column) from Excel into a new table in Access; however, what I need is a macro to automate this process. Any suggestions on how to do this?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Importing from Excel (Ex 2002)

    Does this <post#=228920>post 228920</post#> help?

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing from Excel (Ex 2002)

    As the post referred to by Steve indicates, you can use DoCmd.TransferSpreadsheet to import data from Excel in code. If you need more assistance, you will have to provide more details.

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Importing from Excel (Ex 2002)

    Here is simple example:

    Public Sub ImportExcelRange()

    Dim strFile As String
    strFile = "Cocuments and SettingsMarkDMy DocumentsWorksheet1.xls"
    DoCmd.TransferSpreadsheet transfertype:=acImport, _
    spreadsheettype:=acSpreadsheetTypeExcel8, _
    TableName:="Table3", _
    FileName:=strFile, _
    HasFieldNames:=True, _
    Range:="COL_A"

    End Sub

    This imports a named range "COL_A" (the populated portion of Column A of the spreadsheet) from the file Worksheet1.xls into a new table named Table3. The HasFieldNames argument indicates whether or not the range has column headings that represent field names. The Range argument allows you to specify a named range to import. For more details, look up "TransferSpreadsheet Method" in Access VBA Help. For more info on named ranges in Excel, refer to Excel Help for "Names".

    HTH

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Importing from Excel (Ex 2002)

    In further reply, if this still involves the data that originates on a web site in an HTML table, here is another option that does not involve a middleman (Excel or Word) to import data into Access. Whether this approach will work depends on the way the web site is designed. If a simple page that primarily displays data in table format, should work; if complex page with CSS, embedded JavaScript, etc, may not have much luck. Access has an option to import or link HTML data, but in A2K is somewhat limited option: as explained in ACC2000: "Import/Link Data on an FTP or HTTP Server" Help Topic Incorrect, "Microsoft Access 2000 does not support importing or linking data from a remote FTP or HTTP server." Solution: "Save the file or HTML page to your hard disk or Intranet site, and then import or link the file from there." Sample code to do this, using the Windows URLDownloadToFile API function to save file to disk:

    Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
    "URLDownloadToFileA" _
    (ByVal pCaller As Long, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As Long) As Long

    Public Sub DownloadAndImportHTMLTable(ByRef SourceURL As String, ByRef DestFile As String)
    On Error GoTo Err_Handler

    Dim strMsg As String
    Dim lngRtn As Long
    Dim strTable As String ' Destination table in Access
    Dim strHtmlTable As String ' HTML table

    ' DestFile = full path & filename to download to - will overwrite existing file if any - specify .html extension or Err 3027 results
    strTable = "ASCII" ' new or existing table
    strHtmlTable = "" ' No table title or caption defined in HTML source text
    lngRtn = URLDownloadToFile(0, SourceURL, DestFile, 0, 0)

    DoCmd.TransferText TransferType:=acImportHTML, _
    TableName:=strTable, _
    FileName:=DestFile, _
    HasFieldNames:=True, _
    HTMLtablename:=strHtmlTable

    strMsg = "HTML table downloaded and imported to " & strTable & " table."
    MsgBox strMsg, vbInformation, "DOWNLOAD & IMPORT COMPLETE"
    ' Optional:
    DoCmd.OpenTable strTable

    Exit_Sub:
    Exit Sub
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "DOWNLOAD HTML TABLE ERROR MSG"
    Debug.Print strMsg
    Resume Exit_Sub
    End Sub

    You can test this using this web site, that provides a simple, formatted ASCII table:
    DownloadAndImportHTMLTable "http://www.unix-manuals.com/refs/misc/ascii-table.html", "C:AccessASCII.html"

    This successfully downloaded the file to hard drive, then imported table into an Access table. To determine HTML table name, view source code in a text editor like Notepad (see VBA Help for TransferText Method for details). In this example, I could find no table title or caption, so simply used an empty string for this argument. Depending on the format of the HTML table you are importing, may have to do some cleaning up once in Access; for example, if you only need first field (column), the others can be deleted after import; same applies for any superfluous rows imported.

    HTH

  6. #6
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing from Excel (Ex 2002)

    Thanks Mark. Your code works wonders.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •