Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2004
    Posts
    21

    Unanswered: DoCmd.TransferSpreadsheet-- HELP!

    Hi there... I'm making progress on the db I'm building, but I need your help!!! Each week I have to import data from Excel from 3 different spreadsheets. The names and locations of the spreadsheets won't really change, but I want to design the db so that if there are more files that need to be imported, it'd relatively easy to update.

    So I created a table with 3 fields: (tblImportInfo)
    | FileID | FileName | TblName |

    That way, when there's a new file to import, it can be added to the table, and the coding won't necessarily change.

    Then, I want to import the files, based on the table, by pressing just one button on the ImportData form.

    What I'm not sure of is the syntax of getting the values from the table in a 'DoCmd.transferspreadsheet' command.

    I guess what I'm looking for is something like:
    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,[TblName],[FileName],True
    But I'm not sure how to get it to use each record in the table to import them all at once. A Loop, maybe?

    Help please!!! (And sorry so long!)

  2. #2
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Like this:


    Dim db as dao.database
    dim rst as dao.recordset
    dim strFileName as string

    set db = currentdb
    set rst = db.openrecordset("SELECT * FROM YOURTABLENAME",dbopensnapshot)


    with rst
    .movefirst
    do while not .eof
    strFileName = .fields("FileName") ''use the field name which contains file path
    docmd.transfertext ''use strFileName as the path
    .movenext
    loop
    end with

  3. #3
    Join Date
    Jul 2004
    Posts
    21
    Thanks so much for your help. I was trying to figure it out, but I'm lost... I'm getting an error that dao.database is not defined, and it doesn't seem to work. I don't even know if I've got the rest of it correct...What am I doing wrong??? THANKS!!

    ----------------------------------------

    Private Sub cmdImport_Click()

    Dim db As dao.database
    Dim rst As Recordset
    Dim strFileName As String
    Dim strTblName As String

    Set db = CurrentDb
    Set rst = db.openrecordset("SELECT * FROM DataImport", dbopensnapshot)


    With rst
    .MoveFirst
    Do While Not .EOF
    strFileName = .Fields("FileName")
    strTblName = .Fields("TblName")
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, strTblName, strFileName, True
    .MoveNext
    Loop
    End With

    End Sub

  4. #4
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    in the VB editor goto tools/references and make sure that Microsoft DAO 3.6 Object Library is checked then try to run it again.

  5. #5
    Join Date
    Jul 2004
    Posts
    21
    Worked perfectly!!! Thank you thank you thank you!!!

  6. #6
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Worked perfectly!!! Thank you thank you thank you!!!








    Dim i

    For i = 1 to 3
    debug.print "Your Welcome!"
    next i


  7. #7
    Join Date
    Jul 2004
    Posts
    21

    Lightbulb

    Ok, so it works GREAT.... BUT... I forgot that rather than writing over the data in the table, it just appends it to the existing data. Is there a way to replace the existing data with the new data? All I can think of is to somehow delete the data first, and then import the new data. I'm concerned that if I deleted the data first, it would somehow mess up the queries attached to the table.

    Any ideas?

    THANKS IN ADVANCE!!

  8. #8
    Join Date
    Jul 2004
    Posts
    21
    Nevermind-- I figured it out!

  9. #9
    Join Date
    Jul 2004
    Posts
    21
    Ok... so I THOUGHT I had figured out the problem. I can't figure out how to write over the existing data. I can't just delete the table and then import the new data because I'm sure it would mess up the relationship based on that table.

    Any ideas?? THANKS IN ADVANCE!!!

  10. #10
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Off the top of my head you could imort to a temp table (which you can delete the data from each time) then run an update query to update your main table.

  11. #11
    Join Date
    Jul 2004
    Posts
    21
    That solution sounds like it'll work just fine. A little cumbersome because of the huge amounts of data, but it should be fine. I've only done limited amounts of work with update queries, where it's only been multiplying a field * 2 or something like that.

    Does anyone know a relatively easy way to update an entire table with 30 fields in it??

    Thanks for your help!

  12. #12
    Join Date
    Jul 2004
    Posts
    21
    Well, I created the query to update all the fields in the table with the records in the temp table, but because of the size of both tables (I'm assuming), the query freezes Access.

    Anyone have any ideas about another way to go about this?? HELP PLEASE!!!

  13. #13
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Check to see if your tables are indexed correctly. Also check to see if you can eliminate rows that need to be changed in your query.


    If that doesnt work and you have that much data you really need to get a stored procedure going on the server side of things.

  14. #14
    Join Date
    Jul 2004
    Posts
    21
    Thanks for your help... I think I fixed the problem on the other side. I just found a way to only import the current week's data, rather than all of it. So now I can just append it to the table, rather than have to over write the whole thing, eliminating this whole problem.

    Thanks for all your help, TerpInMD!!!

Posting Permissions

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