Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

VB - Importing Selected cells from Excel To Access

Options
  • 22-07-2003 11:06am
    #1
    ainran
    Closed Accounts Posts: 35 ✭


    I'm trying to import some cells from an excel file to an access database. The cells i want are always in the same place from file to file (there are ~500 files). I could write a macro in Excel but the problem is applying that to 500 files so what my plan was is to write a macro in access. Thats all very well but for i can't seem to figure out how to select different cells from different sheets. I'm using

    ' VB Code
    DoCmd.TransferSpreadsheet acImport, 9, _
    "Supplier","C:\supplier.xls", False, "A1:G12"

    This works fine, but only on the first sheet.
    Anyone know the method of changing sheets?

    Is there any other method that would work?

    (I've tried TransferText but because that requires a CSV or similar file type with a schema.ini file its turning out to be much more trouble than its worth)


Comments

  • Options
    #2
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    This works in Excel, you'll have to implement it in Access yourself. If loops through each sheet in the workbook.
    For i = 1 To Sheets.Count
        Sheets(i).Select
        ...
Next i

    I'd imagine you'd have to instansiate a Workbook object and then loop through the Sheets property. Dunno if this helps.


  • Options
    #3
    ainran
    Closed Accounts Posts: 35 ✭ainran


    its ok, found it

    If i use "Sheet Name$A1:Q1" it works

    (you wouldnt believe the time it took me to find that)

    thanks anyway


Advertisement