Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to import and excel spreadsheet using a macro and the function of transfer spreadsheet. The macro needs a file name for the excel sheet it is importing. Since I'm saving the file to a temp directory. I need to be able to identify this temp directory on any users machine. When I save it using and excel macro, I'm saving it with the temp file name of "TempFile = Environ$("temp") & "/" & TempFName".

    I'm having trouble figuring out how to tell the File name argument under the transfer spreadsheet option where to look for this file. Any help with this would be appreciated. If there are also some other suggestions using just VB instead of the macro option. I'm open to that as well.

    Basically just need to import the contents of this spreadsheet into a table so that I can append it to another. There are no column headers in the excel sheet and the number of rows in it will vary. All data is on sheet 1 of the excel file.

    Thanks in advance for any help with this.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd use VBA, not a macro.

    The VBA code would look like this:

    Dim FName As String
    FName = ...
    Docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblImport", Environ("Temp") & "\" & FName, False

    tblImport is the name of the table to import to.

  3. #3
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Hans. That is exactly what I needed.

Posting Permissions

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