Importing data - browsing for file

[knuddelsternchen]

I am at the stage where I'm fine-tuning my module, and would like to enable the user(s) to browse for a file rather than tieing their hands by stipulating the specific path. Currently, I have the following to import the specific file, which works just fine the way it is, but could anyone tell me how to change this up to pop open a window to let the user select the file to import? It would be greatly appreciated.

Function ImportData()
DoCmd.SetWarnings False
On Error Resume Next
DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel9, _
"MyFileName", _
Path & "C:\Documents and Settings\My Documents\MyFileName.xls", _
True, _
"MyTabName!"
DoCmd.SetWarnings True
End Function

 

[RuralGuy]

I believe this link will be what you are looking for.

 

[knuddelsternchen]

I'd tried that before even posting here...seems like a really good way to go, but like many others, I seem to have a problem with it error'ing out in "sub or function not defined," though. The debugger highlights "ahtAddFilterItem." I have seen many people ask about this (just from searching the web), but haven't been able to find a satisfactory solution.

Thanks for all your help, RuralGuy!

 

[RuralGuy]

Put all of the code in a standard module of its own and name the module something like basFunctions for now. Then look at the included TestIt function as to how to call it from your form.

 

[knuddelsternchen]

This is driving me mad. Could someone please let me know how to incorporate this into my current module so that I can call it using a macro? I'm just trying to automate an import and am failing miserably.

 

[RuralGuy]

Did you copy and paste all of the code into a new module named basFunctions? Have you tried testing the TestIt code from the immediate window? Press ^G to bring up the immediate window.

 

[aetiger]

I am trying to do something similar. I want the user to click a button in a form, and then select the excel file to import. I created the basFunctions module, but I am very new to VBA, so I am unsure how to reference that module in the TransferSpreadsheet command. Thoughts?

 

[RuralGuy]

Post the code you have and someone will suggest changes you can make.

 

[aetiger]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel2003, "FY07", "C:\FY07.xls", True

How do I replace "C:\FY07.xls" with a user-selected excel file so that it doesn't always have to be located in the same path using the same file name?

 

[RuralGuy]

Try:
Dim OurFile As String
OurFile = GetOpenFile(,"Select the spreadsheet to import")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel2003, "FY07", OurFile , True

 

[aetiger]

Unfortunately I am getting a compile error of "Sub or Function not defined" when I attempt that method. Is GetOpenFile a function in Access2003?

 

[RuralGuy]

Did you copy the code in the link I supplied into the new BasFunction module yet? It will not work without it.

 

[CraigWarmy]

Re: Importing data - browsing for file(New Easier Way)

Add a reference to Microsoft 11.0 object code in the Tools,References.

Here is the easy way to do it:
Dim dlgOpen As FileDialog
Dim sLogoPath As String
'Open a dialog box and get a filename for the data database.
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
With dlgOpen
.Title = "Add your file name here"
.Filters.Add "All Files", "*.*", 1
.AllowMultiSelect = False
.Show
End With
'Returns the table connection file path string.
sLogoPath = dlgOpen.SelectedItems.Item(1) 'Can only be item 1.

That's it! Put an err.number=5 in your error trapping in case someone selects the cancel button.

 

[knuddelsternchen]

I feel pretty dumb, but I still can't get it to work. Thanks for bearing with me. Here's what I have:

A module as follows:
Function BasFunctions()
Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long
Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000
End Function

This was copied straight from the link provided.

Then I created a second module with the following code:

Function TestNEW()
Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", _
"*.MDA;*.MDB")
strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:\", _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Hello! Open Me!")
' Since you passed in a variable for lngFlags,
' the function places the output flags value in the variable.
Debug.Print Hex(lngFlags)
End Function

This was also copied straight from the link.

I have a macro that calls the TestNEW module (I haven't even gotten to the point of changing this up yet and incorporating it into my own situation and it's already not working. ), and the Error message I'm getting is: "There was an error compiling this function. The Visual Basi module contains a syntax error. ..."

 

[RuralGuy]

You name the *module* basFunctions, not a function within the module. You name the module when you save it the first time.

 

[RuralGuy]

You need to copy and paste *everything* from Code Start through Code End into one standard module.

 

[knuddelsternchen]

Fair 'nough. Now we're back to getting a "sub or function not defined" error that highlights the first instance of "ahtAddFilterItem" just as before in the TestNEW function module...

 

[RuralGuy]

Can you zip up your db and attach it to a post so we can see it and fix it?

 

[CraigWarmy]

What are you doing? That code that I gave you is the only thing that you need. Nothing else. Throw it away.

 

[YevS]

Re: Importing data - browsing for file(New Easier Way)

Add a reference to Microsoft 11.0 object code in the Tools,References.

Here is the easy way to do it:
Dim dlgOpen As FileDialog
Dim sLogoPath As String
'Open a dialog box and get a filename for the data database.
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
With dlgOpen
.Title = "Add your file name here"
.Filters.Add "All Files", "*.*", 1
.AllowMultiSelect = False
.Show
End With
'Returns the table connection file path string.
sLogoPath = dlgOpen.SelectedItems.Item(1) 'Can only be item 1.

That's it! Put an err.number=5 in your error trapping in case someone selects the cancel button.

Thanks CraigWarmy, was looking all over for this!

This Works with "Microsoft Office 12.0 Object Library" (I dont have office 11 for some reason)

Here is my code (copied & slightly modified):

 Function GetFileLink() As String
'Here is the easy way to do it:
Dim dlgOpen As FileDialog
'Open a dialog box and get a filename for the data database.
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
With dlgOpen
    '.Title = "Add your file name here"
    .Filters.Add "All Files", "*.*", 1
    .AllowMultiSelect = False
    .Show
End With
'Returns the table connection file path string.
GetFileLink = dlgOpen.SelectedItems.Item(1) 'Can only be item 1.
End Function
 
 
Private Sub Command0_Click() ' call it with whatever

Me.Text1 = GetFileLink

End Sub