Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Excel import, choosing sheets

    With the following code:

    Code:
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        fd.InitialFileName = "F:\User\"
        fd.show
        filen = fd.SelectedItems(1)
        DoCmd.TransferSpreadsheet acImport, , "import", filen, True
        Set fd = Nothing
    I have a small and simple excel importer. Yet, some of the sheets contain mutiple sheets, rather than just one, and the above doesn't allow for selections.

    How would I get about ehancing the import with a sheet picker and a basic "import every sheet" to importA, importB etc.?

    I've been browsing MSDN a bit and Access help, and can't seem to find something that covers this part of Excel Import.

    Ideas? Thanks :-)

    /Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    May 2004
    Posts
    65

    Loop through worksheets

    Dim objXL As Object
    Dim objActiveWkb As Object

    filepath = GetOpenFile_CLT("\your path\filename.xls", "Please Select the Document you wish to Import")
    Set objActiveWkb = GetObject(filepath)
    wsheetno = 1 ' this is the first sheet
    wsheetcnt = 0 ' this is used to count the number of sheets


    With objActiveWkb
    lastrep = ""
    '******** Calculate numbers of sheets
    On Error GoTo command88err
    tmpvalue = objActiveWkb.Worksheets(1).Cells(wsheetno, 1)
    ' this is a value (in this case cell 1,1) to validate this is one of your worksheets rather than just a blank one
    While tmpvalue <> ""
    wsheetcnt = wsheetcnt + 1
    'add one to the count as this is one of yours
    yourfield1 = objActiveWkb.Worksheets(wsheetno).Cells(1, 1)
    yourfield2 = objActiveWkb.Worksheets(wsheetno).Cells(1, 2)
    yourfield3 = objActiveWkb.Worksheets(wsheetno).Cells(1, 3)
    'do whatever you want with this data
    ' this is a sample to collect data

    tmpvalue = ""
    ' reset tmpvalue
    wsheetno = wsheetno + 1
    ' goto the next sheet
    tmpvalue = objActiveWkb.Worksheets(wsheetno).Cells(1, 1)
    'look for the first value to validate your worksheet and return to top of loop
    Wend


    strMsgPrompt = wsheetcnt & " Class Report sets have been imported successfully"
    strMsgTitle = "Import Successful"

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    Welp, I just discovered, that the range property of the transferspreadsheet can be used for worksheets as well, not just field ranges.

    See http://support.microsoft.com/default...b;en-us;210379
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

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