Results 1 to 3 of 3
  1. #1
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009

    VBA to Import Excel Worksheet

    Have been using the saved imports in access to import excel data into access tables. Like the way it works except for the difficulty when you have to move the program from one directory to another, means have to redo all of the saved imports to reference the new location. Very time consuming.

    Is there VBA code that will perform the same action as the access saved imports? That way, just have to change the location in the code instead of redoing the saved import.

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    when in vba - use the Help to look up the TransferSpreadsheet method. I think you will find this to be a solution.

    hope this helps.

  3. #3
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010

    if you need a flexible location picker to get the path , filename of the file you want to import:
    ================================================== =====
    Public Function getLoc(strDefaultLoc As String, fFile As Boolean, fFolder As Boolean, intFormat As ImportFormat) As String
    On Error GoTo Err_getLoc

    Dim fdo As FileDialog
    Dim strLocation As String

    If fFile Then
    Set fdo = Application.FileDialog(msoFileDialogOpen)
    With fdo
    .Title = "File location"
    .ButtonName = "Select file location"
    .InitialView = msoFileDialogViewDetails
    If Len(strDefaultLoc) > 0 Then .InitialFileName = strDefaultLoc
    Select Case intFormat
    Case ImportFormat.FlatFile
    .Filters.Add "Text File", "*.txt"
    Case ImportFormat.Excel
    .Filters.Add "Excel 2007", "*.xlsx"
    .Filters.Add "Excel 97-2003", "*.xls"
    Case ImportFormat.XML
    .Filters.Add "XML File", "*.xml"
    Case ImportFormat.Access
    .Filters.Add "Access 2007", "*.accdb"
    .Filters.Add "Access 97-2003", "*.mdb"
    End Select
    If .Show = -1 Then
    strLocation = CStr(fdo.SelectedItems.Item(1))
    strLocation = ""
    End If
    End With
    End If

    If fFolder Then
    Set fdo = Application.FileDialog(msoFileDialogFolderPicker)
    With fdo
    .Title = "Folder location"
    .ButtonName = "Select folder"
    .InitialView = msoFileDialogViewDetails
    If Len(strDefaultLoc) > 0 Then .InitialFileName = strDefaultLoc
    If .Show = -1 Then
    strLocation = CStr(fdo.SelectedItems.Item(1))
    strLocation = ""
    End If
    End With
    If Right(strLocation, 1) <> "\" Then strLocation = strLocation & "\"
    End If
    getLoc = strLocation

    Exit Function

    getLoc = "#ERROR#"
    Debug.Print Err.Number & ": " & Err.Description
    Resume Exit_getLoc

    End Function


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

Similar Threads

  1. Import from Excel
    By Jonathan58 in forum Import/Export Data
    Replies: 18
    Last Post: 07-19-2013, 09:52 AM
  2. Replies: 1
    Last Post: 11-21-2010, 10:26 PM
  3. Import Excel with SQL - again
    By tpcervelo in forum Queries
    Replies: 1
    Last Post: 08-06-2010, 08:13 PM
  4. Importing the second excel worksheet
    By geoffwbailey in forum Programming
    Replies: 1
    Last Post: 06-25-2010, 12:16 AM
  5. VBA to open excel, import on close of excel
    By bdaniel in forum Programming
    Replies: 0
    Last Post: 03-20-2010, 02:45 PM

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 - Senior Forums