In transfer spreadsheet macro whether user has an option to select the file path each time of import from spreadsheet
In transfer spreadsheet macro whether user has an option to select the file path each time of import from spreadsheet
usage:
vFile = UserPick1File("c:\folder")
if vFile <> "" then docmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel12, "tTable", vFile, true
Code:Public Function UserPick1File(pvPath) Dim strTable As String Dim strFilePath As String Dim sDialog As String, sDecr As String, sExt As String If IsMissing(pvPath) Then pvPath = "c:\" With Application.FileDialog(msoFileDialogFilePicker) 'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library .AllowMultiSelect = False .Title = "Locate a file to Import" .ButtonName = "Import" .Filters.Clear .Filters.Add sDecr, sExt .InitialFileName = pvPath .InitialView = msoFileDialogViewList 'msoFileDialogViewThumbnail If .show = 0 Then 'There is a problem Exit Function End If 'Save the first file selected UserPick1File = Trim(.SelectedItems(1)) End With End Function
Sir,
Thank you for the reply.
I have only little knowledge in VBA. Hence I am using the transfer spreadsheet macro action. How is it possible in macro?
Most of us don't use macros. Convert it to VBA by the following:
In the top left of the window it says "Convert Macro to Visual Basic".
Sometimes this seems to be grayed out. Here is the solution:
"The solution I found was, rather than loading the form in Design View,
I clicked the Macro button under Create on the Ribbon which brought up
the Macro Design window. Under the Action Catalog window, there is a
section called "In this Database." I clicked down to the form and
textbox which contained the macro I wanted to convert, then double-
clicked. The macro displayed in the left hand window and the "Convert
Macro's to Visual Basic", was not grayed out so I clicked it and the
conversion was simple after that."
You will see that the VBA code follows the macro and you can easily see what is going on, it will all feel very similar to you.
Once you have done this, change the line to refer to the variable as in post # 2.