Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Arrow Unanswered: Paste to Access from Excel (Without TransferSpreadsheet)

    I am pulling data:

    1) From a text file via FTP to an Excel Spreadsheet.
    2) The Data is then modified and prepared for an Access database.
    3) Currently, the data is saved to disk (Which is annoying.)
    4) The TransferSpreadsheet Method is then invoked pulling the Data into Access.

    If you manually select the columns in Excel and Copy, and then manually select the columns in Access and Paste, the data is correctly imported without step 3.

    This is exactly what I want to do except with code in VBA. (Unless, I can move the data without saving it to a disk by another method.)

    Any help is greatly appreciated.

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Paste to Access from Excel (Without TransferSpreadsheet)

    You can link that spreadsheet in your access db and then with a simple append or update query you can move the data from the spreadsheet to the target table.


    Originally posted by rubmyback
    I am pulling data:

    1) From a text file via FTP to an Excel Spreadsheet.
    2) The Data is then modified and prepared for an Access database.
    3) Currently, the data is saved to disk (Which is annoying.)
    4) The TransferSpreadsheet Method is then invoked pulling the Data into Access.

    If you manually select the columns in Excel and Copy, and then manually select the columns in Access and Paste, the data is correctly imported without step 3.

    This is exactly what I want to do except with code in VBA. (Unless, I can move the data without saving it to a disk by another method.)

    Any help is greatly appreciated.

  3. #3
    Join Date
    Jan 2004
    Posts
    6

    Arrow Re: Paste to Access from Excel (Without TransferSpreadsheet)

    Originally posted by khan
    You can link that spreadsheet in your access db and then with a simple append or update query you can move the data from the spreadsheet to the target table.
    Does anyone know a way to do the following without having to do it cell by cell (Which I can do with MyWS.Cells(y, x).Value?

    Thanks khan...that is a good idea. Unfortunately, it would be nice if I could do that without saving the file?

    I already have the sheet assigned to an object. I have the following objects to work with:

    MyXL (Excel Application)
    MyWB (Workbook)
    MyWS (Worksheet)

    Remember, I don't have a filename because the data has been downloaded, altered but not saved.

    So the two things I would need now are...
    1. How does one link to a spreadsheet object as opposed to a filename.
    2. ...and more specifically how to do it using VBA.

    Thank you for your response!
    Last edited by rubmyback; 01-22-04 at 10:14.

  4. #4
    Join Date
    Jan 2004
    Posts
    6

    Arrow Link to Excel Object (Not File) with VBA

    Does anyone know how to link using VBA Code to an Excel Object that has not been saved to a file?

Posting Permissions

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