Importing Different Files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have set up a macro which uses the Transfer Text action. This will be
used to import a file from a folder.

Is there any way to set the File Name arguement so that it brings up an
Import box which would allow the user to browse the network for the file?

Thanks,
Beth
 
Yes, here is a site where you can get the code to allow you to do that.

http://www.mvps.org/access/api/index.html

If you are using a Macro, then you will have to write the code to call the
Common Open/Save Dialog box in a function that your macro can call. It
would, however, be better to write a sub and call it instead of the macro.

Here is an example of how to use the API code you can download from the site
above.

Dim varGetFileName As Variant

'Get the file name to save to
Do While True
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, , _
"Excel Spreadsheets (*.xls) *.xls", , _
"xls", , "Save YTD Plan Vs Actual", , False)
If varGetFileName = "" Then
If MsgBox("Cancel Save?", vbQuestion + vbYesNo, "YTD Plan Vs
Actual") _
= vbYes Then
Exit Sub
End If
Else
Exit Do
End If
Loop

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"showfinalytd", varGetFileName, True
 
Hello,

Please can you clarify the following for me?

Do I write the sub in a Module?

How do I call the sub exactly. Would you link it to a button on a form?

Thanks,
Beth
 
The API code you download should go in a Standard Module by itself. When you
go to the site, copy the code, then create a new module and paste in the
copied code and save it. I call mine modCommonDialog.

The sample code I posted could go in the Click event of a command button on
a form. Please note you will probably have to change some of the names and
some of the code to suite your own situation.
 
Back
Top