Results 1 to 14 of 14
  1. #1
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246

    Import from Excel to Access

    I need to import data from an Excel 2007 spreadsheet into a temp table in Access 2007. I cannot always count on the Excel file being in the same location every time.


    The following DoCmd.TransferSpreadsheet works perfect every time. But notice that the path to the file is hard coded.


    DoCmd.TransferSpreadsheet acImport, 10,"Tbl_VendorPrices_TempImport", "C:\menuprobids\import35", True, "A1:I1000"

    I have tried many different ways to navigate to the file. This one below works very well. However, when you pick the file it instantiates an instance of Excel. So when I try to import the file it fails saying that the file is already open by another user.

    What I need is a way to navigate to the file location, and only return to VB the PATH, and store it as a string variable. Then I can have the TransferSpreadsheet use the variable to locate the file.


    Dim Dlg As FileDialog
    Dim txtFilePath As String
    Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
    With Dlg
    .title = "Select the file you want to import"
    .AllowMultiSelect = False
    If .Show = -1 Then
    txtFilePath = .InitialFileName
    Else
    Exit Sub
    End If
    End With


    Any Thoughts?
    Phred

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936

  3. #3
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246
    I wasn't able to get this work. It gave me a Sub or Function not Defined compile error. It was not able to define:

    ahtCommonFileOpenSave

    It wanted me to open the references dialog box and call up the program that has it. Here is the code as I was useing it. I commented out everything I thought I was not using.

    Private Sub CmdTestImport_Click()
    Dim strFilter As String
    Dim strInputFileName As String
    'strFilter = ahtAddFilterItem(strFilter, "XLSX Files (*.XLS, *.XLSX)", "*.XLSX;*.XLS")
    'strFilter = ahtAddFilterItem(strFilter, "bmp Files (*.bmp)", "*.bmp")
    'strFilter = ahtAddFilterItem(strFilter, "all Files (*.*)", "*.*")
    strInputFileName = ahtCommonFileOpenSave( _
    Filter:=strFilter, _
    OpenFile:=True, _
    DialogTitle:="Choose the Vendor to import...")
    'Flags:=ahtOFN_HIDEREADONLY)
    If Len(strInputFileName) > 0 Then
    ' Do something with the selected file
    Else
    'No file chosen, or user canceled
    End If
    End Sub

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I made a Command button on a form. In the OnClick event, here is the code:

    Private Sub Command0_Click()

    Dim strFilter As String, strDir As String, strTitle As String, FileName As String, lngFlags As Long
    strFilter = "All Files (*.*)"
    strDir = "C:\"
    strTitle = "Open a file"
    FileName = ahtCommonFileOpenSave(InitialDir:=strDir, Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, DialogTitle:=strTitle)
    Debug.Print FileName

    End Sub


    Go to this link: http://www.ammara.com/access_image_f...en_dialog.html
    click on Download Code
    create a new module and insert that code

  5. #5
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246
    I downloaded this code and I don't know who to give credit to.

    I have been working with this. My problem seem that the variable varFile won't hold the value. When I step through, varFile has the correct path name I have selected. When I F8 the varFile loses it data. I do have the 12.0 Object Library set up. I think I also have a problem passing the varFile variable into my TransferSpreadsheet statement. Two problems:

    The varFile loses it's value and can't pass it to the transfer statement.
    The varFile is probably referenced incorrectly in the Transfer statement.

    See red text in code below.

    Private Sub CmdTestImport_Click()
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim strMsg As String

    On Error GoTo Err_cmdFileDialog_Click
    ' This requires a reference to the Microsoft Office 12.0 Object Library.
    'MsgBox "Please ensure that the Columns of the Excel document about to be loaded are labeled EXACTLY as followed: 'DTCNUMBER' 'PARTICIPANTNAME' 'SHARES'", , "***IMPORTANT INFORMATION***"
    ' Clear the list box contents.

    'Forms![Frm_Main]![Frm_ReportsParent].Form![FileList] = ""

    'Me.FileList.RowSource = ""
    ' Set up the File dialog box.

    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog

    ' Allow the user to make multiple selections in the dialog box.

    .AllowMultiSelect = True
    ' Set the title of the dialog box.

    .title = "Select One or More Files"
    ' Clear out the current filters, and then add your own.

    .Filters.Clear

    .Filters.Add "DTC List (Excel Format)", "*.xls,*.xlsx"
    ' Show the dialog box. If the .Show method returns True, the
    ' user picked at least one file. If the .Show method returns
    ' False, the user clicked Cancel.
    If .Show = True Then
    ' Loop through each file that is selected and then add it to the list box.
    For Each varFile In .SelectedItems

    varFile = Me.FileList

    'WHEN I SINGLE STEP THROUGH, THE varFile HAS THE SELECTED FILE PATH CORRECTLY

    'WHEN I PRESS F8 TO TAKE THE NEXT STEP THE varFile IN THE WATCH WINDOW LOSES IT VALUE

    The varfile cannot be passed to the transferspreadsheet.


    '.AddItem varFile
    'DoCmd.TransferSpreadsheet acImport, 10, "Tbl_VendorPrices_TempImport", "C:\menuprobids\fox35b", True, "A1:I1000"
    DoCmd.TransferSpreadsheet acImport, 10, "Tbl_VendorPrices_TempImport", varFile, True

    Next
    Else
    MsgBox "You clicked Cancel in the file dialog box."
    End If
    End With
    Exit_cmdFileDialog_Click:
    Exit Sub
    Err_cmdFileDialog_Click:
    MsgBox "The following error occurred: " & "***" & Err.Description & "***" & " Please ensure the excel columns are labeled correctly.", , "Import Failure"
    Resume Exit_cmdFileDialog_Click



    End Sub

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This must be because of the multiple-selection feature you are providing. I didn't check that out, I only selected one file name. I presume you got it to work with just one file name, same as I did?

  7. #7
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246
    Yes I got it to work with one file name.Do you think that might be causing the problem?

  8. #8
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246

    Smile

    THAT DID IT, YIPPIE!!!

    I remmed out allow multiselect and Varfile.

    .AllowMultiSelect = False
    'varFile = Me.FileList

    It does not leave an instance of Excel running. You don't know how long I have been looking for a soultion or how many chunks of code I have tried to make work. You did it, you alone. You need to elevate your status on the boards.

    Thanks Phred

  9. #9
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Well then I am happy too. But sometimes I just get lucky!!

  10. #10
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246
    Sometimes it is better to be lucky. May the bird of paradise fly up your nose. I would like to post the code for this thread. It seems a lot of people are looking for it. Would that be ok with you?

  11. #11
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Post them the link for the download. All I did was take their "TestIt" function and attach it to a button, made it a bit simpler (I think). I don't need any credit!

  12. #12
    kilo21 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    7
    Hello guys, I've been following this post. I'm a beginner and found this really helpful.

    I put the codes given by aytee111 inside module1 and command button respectively. The browse window appeared when I clicked it. But after clicking Open after choosing an Excel file, the browse window closed. That was it. Where did the selected Excel file go?

  13. #13
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The file didn't "go" anywhere. All you have after selecting the file is the file name. Now you have to do something with the file, such as import/link.

  14. #14
    kilo21 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    7
    Ok, got it thanks.
    Last edited by kilo21; 05-16-2012 at 09:16 PM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Excel Import Macro Help in Access
    By vennies83 in forum Import/Export Data
    Replies: 5
    Last Post: 11-02-2011, 09:24 AM
  2. Import excel to access
    By beha in forum Import/Export Data
    Replies: 8
    Last Post: 10-25-2011, 11:39 AM
  3. import excel file to access 2003 yes, access 2007 no
    By stapik in forum Import/Export Data
    Replies: 9
    Last Post: 07-27-2011, 07:09 AM
  4. Access Import from Excel Overwrite
    By redpanda in forum Programming
    Replies: 8
    Last Post: 07-10-2011, 09:18 PM
  5. Import excel sheets to access.
    By calexandru in forum Import/Export Data
    Replies: 0
    Last Post: 08-19-2009, 09:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums