TransferSpreadsheet Function

Carl1

New Member
Joined
Aug 16, 2005
Messages
7
Help. I am not a particularly sophisticated Access person but have a basic understanding of Macros and have a slight problem with a Macro within Access.

I am trying to Import data (Will always be in same field layout - but in Excel) into an existing table (With Same Field Format - But in Access) and append this data to the table.

I have had some success using the TransferSpreadsheet function doing this but as I use Macros I have to set-up at the start the filename for the Excel File.
Is there a way that I can change this filename, so it looks at a different file each time (Will always be the same format)?

Ideally I would like to be able to just run the macro - and browse/search and dble click the actual file I want appending at that time.

Hope someone can help

Carl :(
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Carl

Is there a pattern to how the filename will change? eg according to the date

You could use an inputbox to enter the name of the file.

But I don't know if you can use that via a macro, you might have to convert the macro to actual VBA code.
 
Upvote 0
Yes, there can be. it will always be two alpha numerics followed by 4 numbers, an example would be CO1626.xls

Also, I can ensure these files are always saved within the same folder if that will help.

Converting a MAcro to VBA code, now youve lost me. I assume that means using the Modules within Access, have seen previous responses to similar questions as mine, and tried pasting the code suggested, that works fine but then cant figure out how to run it from a button...

Carl
 
Upvote 0
Carl

If you wanted code to run from a button you would either put the code or a call to run the code into the button's Click event.

You can access the click event via the button's property sheet on form design view.
 
Upvote 0
Norie
Cheers, have now managed to get code and can hit a button and choose a filename to appear in a listbox.
The problem I have now is how i then get the TransferSpreadsheet function to fire off using that filename chosen.

Where do I place the code within the form? do I need another button that, and also what should the code look like within the module?
Examples Ive found seem to say it is easy, using:

DoCmd.TransferSpreadsheet acImport, 3,

But I dont know how to tell it to look for the filename/path at the listbox called FileList__

Carl
 
Upvote 0
Carl

To get the value in the listbox just use FileList.Value.

Check out TransferSpreadsheet in the VBA Help to find out what arguments you need to use.
 
Upvote 0
Almost There

Cheers Norie - Think im almost there
Used the helpfile and have decided to place another comand button with the appropriote code in - Button called TrSp

Then under the code - on click put the following statement, from the VBA help

Private Sub TrSp_Click()
DoCmd.TransferSpreadsheet acImport, , _
"tbl_Order Lines Detail", FileList__.Value, True, ""
End Sub

Leaving the """" at the end apparently means it will take all the range, not just a set range (Which is exactly as required), and have used the FileList.Value and FileList__.Value in several times. Still get the following error report:

RunTime Error 2522

The Action or Method requires a File Name Argument.

Help!!!!
 
Upvote 0
NORIE

You are a star!!! Cheers - Just realised I hadnt selected the File, Sorry, hence when i select the file it works perfectly.

Thanks

Carl
 
Upvote 0
Glad you got it to work.

I'd be interested in seeing how you populated the listbox.
 
Upvote 0
ListBox - Populated, not sure what you mean there, so apologies if all the below is not what your after.
Basically within the VB Code for the ListBox I put the following:

cmdFileDialog_Click()
' This requires a reference to the Microsoft Office 11.0 Object Library.

Dim fDialog As Office.FileDialog
Dim varFile As Variant

' Clear the list box contents.
Me.FileList.RowSource = ""

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to make multiple selections in the dialog box.
.AllowMultiSelect = True

' Set the title of the dialog box.
.Title = "Select One or More Files"

' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Excel Spreadsheets", "*.xls"
.Filters.Add "All Files", "*.*"

' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
' Loop through each file that is selected and then add it to the list box.
For Each varFile In .SelectedItems
Me.FileList.AddItem varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub



Basically got the above from a website and then slowly changed it to what i needed, the exact website version allowed you to choose multiple selections etc, which I dont want at this stage.

As an afterthought, would it now be possible, once I have appended my data from the Excel file, to then somehow change the name of the Excel File?
Either renaming, or changing the file extension would be preferable, as this would prevent appending the same data twice.??? Have tried using the VBA help on Renaming and Naming functions but they only appear to work with Access tbl's etc.

Carl :)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top