VBA to Import Excel Worksheet

[bdaniel]

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.

[NTC]

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.

[NoellaG]

Hi,

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
.Filters.Clear
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))
Else
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))
Else
strLocation = ""
End If
End With
If Right(strLocation, 1) <> "\" Then strLocation = strLocation & "\"
End If
getLoc = strLocation

Exit_getLoc:
Exit Function

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

End Function

===============================================
greetings
NG