Importing files into Access

[Soppy]

Hi, I have been writing a Database system which requires the uploading of Excell Spreadsheets. At this time you need to put the Spreadsheets into a set directory and give them a set name for the process to work. I ma trying to make a front end that can be used by non-Access users and am wondering how I could either make VBA bring up a box like the "File/Open" box so they can select a file to upload or atleast program VBA to get the address from a table. Can a String in VBA be made to equal a value in a table?

Not sure if that is clear but any help would be a God send, thanks!

 

[Timtropolis]

try this

Good morning Soppy,

Try using this link. I've used it in the past and it has served me well.. very easy to implement.

Common Dialog box

 

[Guus2005]

First search this forum for "File dialog" then select to use an API or to add a reference to your database and use the opendialog method.

There are lot's of examples here.

HTH

 

[Soppy]

Thank you this looks very promissing, I have used the code :

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

This does not work as it does not recognise "ahtAddFilterItem"; I persume this is because I have not loaded the correct command libraries. I know Java and C++ well; I know how to find out whihc libraries to load and how to load them. I am inexperienced at VBA; how do you do this?

 

[Soppy]

Thanks Everyone for your help!

Thanks for pointed to where to look I have it working now!

The code I finally used and works is :

Private Sub Import_Weekly_Data_Click()
On Error GoTo Err_Import_Weekly_Data_Click

Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls", 1
.Filters.Add "All Files", "*.*", 2
If .Show = -1 Then
strFilename = .SelectedItems(1)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "sk inv cred", strFilename, False

Else
Exit Sub

End If

End With

Exit_Import_Weekly_Data_Click:
Exit Sub

Err_Import_Weekly_Data_Click:
MsgBox Err.Description
Resume Exit_Import_Weekly_Data_Click

End Sub