Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: DoCmd.TransferSpreadsheet question!

    I am importing large sets of data via Access VBA from Excel Workbooks using DoCmd.TransferSpreadsheet and it works fine when I specify the name of a worksheet I want to import.

    The problem is that some of those Excel spreadsheets have hit 65K row limit and thus the data are contained withing multiple worksheets named Odds1, Odds2 etc.

    Is there a way to question from Access VBA which worksheets are contained within a particular Excel Workbook and then importing the ones you need.

    Something like (in pseudo):

    DoCmd.Return_Me_An_Array_Of_Strings_Representing_S heetNames (in) SomeWorkbook.xls

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes and No. You can't use DoCmd (DoCmd just lets you carry out some simple Access Macro tasks). You'll need to use Excel automation to do this. How comfy are you with that?

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd be doing nothing but considering to re-write the situation so as Excel is NOT being used to store large amounts of data. Having data going over several sheets is just absurd. Use a database (Access) to store the data.

    Sorry, but I would just refuse to help someone dig their own grave.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Totally agree assuming he is storing data in Excel and worth clarifying. I assumed he was just tansporting data via Excel but since he is exceeding 65k rows it is clearly not a good choice for that either.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup, that's the bit that made me cringe! Having data spread over multiple sheets is just asking for further problems imo.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Apr 2007
    Posts
    108
    I stand by my original question!

    It just needs some more clarification as to why I'm exceeding 65K rows in Excel!

    I'm perfectly aware that Excel is not a good solution for storing, processing and querying large amounts of data and that's exactly why I need importing that data to Access!

    So, how did I come up with having 65K rows in Excel?

    Excel VBA is used extensively as an intermediate to spider the Web site.

    I'm using Excel because of its WebQuery option - that is I'm using Excel to retrieve large amounts of data from a web site (betting odds site) via WebQuery. After retrieving that data I need to get rid of "useless" data and store useful data. After that from each page visited (which can be chosen via nice user menus) hyperlinks are extracted. After that hyperlinks are parsed to follow the ones that I need. After that macro continues to extract useful information from followed hyperlinks (some hyprelinks have no data, some have few, some have large sets of data) etc.

    So if you know how I can do that with Access that's fine. But I don't think there's a way of doing it via Access, it doesn't have WebQuery options, it doesn't put data into parsable cells, it can't extract array of hyperlinks and follow the ones that I need etc.

    If someone is interested I can upload that magnificent example of Web spider to see exactly what it does.

  7. #7
    Join Date
    Apr 2007
    Posts
    108
    Yes and No. You can't use DoCmd (DoCmd just lets you carry out some simple Access Macro tasks). You'll need to use Excel automation to do this. How comfy are you with that?
    I would prefer Yes solution (that's the reason why I'm asking the question)!

    If the answer is No I can make some additional Excel macros to save each additional sheet as a stand-alone file and later use Access to cycle through list of files and import it all in the one predefined table.
    I'm very comfy with Excel VBA as if my Web collection macro would show (should I upload it).

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah! The complicated bit is the Excel Object model. I'll give you process - tell me if you need owt else.

    1) Create an excel workbook object & instantiate it to your workbook
    2) Loop through the worksheets collection and read the name at each pass
    3) At each pass, feed the worksheet name to transferspreadsheet

    Examples of automating Excel are available in the first page of the code bank sticky ^^

  9. #9
    Join Date
    Apr 2007
    Posts
    108
    Thanks pootle flump!

    Just the answer I was hoping for!

    All 3 criterias of good answer satisified:
    1. I understand how I'm supposed to do it
    2. It points me in the right direction
    3. I wouldn't have come up wtih it on my own!

    Will give it a try later and let you know if it comes up right!

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I <3 Riorin

    But I'm not going to tackle writing an Access web spider

Posting Permissions

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