Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: TransferSpreadsheet Function

  1. #1
    New Member
    Join Date
    Aug 2005
    Location
    UK
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default TransferSpreadsheet Function

    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

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,304
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    3 Thread(s)

    Default

    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.
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Aug 2005
    Location
    UK
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,304
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    3 Thread(s)

    Default

    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.
    If posting code please use code tags.

  5. #5
    New Member
    Join Date
    Aug 2005
    Location
    UK
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,304
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    3 Thread(s)

    Default

    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.
    If posting code please use code tags.

  7. #7
    New Member
    Join Date
    Aug 2005
    Location
    UK
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!!!!

  8. #8
    New Member
    Join Date
    Aug 2005
    Location
    UK
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,304
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    3 Thread(s)

    Default

    Glad you got it to work.

    I'd be interested in seeing how you populated the listbox.
    If posting code please use code tags.

  10. #10
    New Member
    Join Date
    Aug 2005
    Location
    UK
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •