Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoImport (97 SR2)

    I'm not quite sure where to start on this one, so perhaps someone can point me in the righr direction.

    I am looking to develop an access database that will automatically import a series of excel spreadsheets into one large table.

    The spreadsheets all have the same format, and structure, we have about 2 dozen people entering data in these sheets, and we want to consolidate it...

    Any ideas? --I'm Lost.

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoImport (97 SR2)

    Do you know VBA? If so, you could try the TransferSpreadsheet method. Something like:-

    <pre> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "mytable", _
    "mysheet.xls", True, "myrange"
    </pre>


    You could loop thru the sheets using, for instance, an array of filenames.

    Hope this helps,

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoImport (97 SR2)

    This is working well, thanks for the reply!

    But, how can I automatically remove blank records after the import?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoImport (97 SR2)

    I can think of two ways to approach this...

    <font color=red>1-You might want to use an autofilter in Excel on a particular field, to isolate the non-null records,
    Then copy and paste the results into another sheet and THEN import the sheet.</font color=red>

    <font color=blue>2-Import into a static table, remove the null records, THEN append them to your desired table.</font color=blue>

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoImport (97 SR2)

    I guess i'm looking for an example...

    My code evaluates a predetermined directory for a particular string. This string is assigned by an Excel workbook, which serves as a data entry point.

    The code parses through each file, and performs the afforementioned command to import the excel workbook.

    What command, if any, can I place before or after this one to effectively parse my blank records?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoImport (97 SR2)

    Are you referring to that pesky blank record that seems to be inevitable at the end of an Excel import? If so, determine which fields should *always* have a value and run a delete query on the imported table that removes all records with Null in those fields.
    Charlotte

Posting Permissions

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