Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    TransferSpreadsheet (2002)

    I am going to be doing a TransferSpreadsheet/TransferDatabase from Access. Not sure which one at this point as it will depend on the response I get from this forum.

    What I need to happen is as follows:

    I want the data from Access to export directly into an "existing" spreadsheet. In doing so I would like the workbook to open automatically, once open go to directly to a "new" (next available/blank) record in the spreadsheet and export the data in that cell. Could someone point me in the right direction for this information.

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: TransferSpreadsheet (2002)

    I fear that neither TransferDatabase nor TransferSpreadsheet will do what you want.

    TransferDatabase is for importing from/exporting to other databases, so it's not suitable for exporting to a spreadsheet. If you use TransferSpreadsheet to export to an existing workbook, it will create or completely overwrite a worksheet with the name of the table, it won't append to existing data.

    You will need to use Automation to accomplish your goal. That means creating an Excel application object in VBA code in Access, and use this to pen the workbook, find the first available row, and write the data there. If you do a search in the Access forum on Excel.Application, you will find several posts that explain how to start Excel from Access in code.

    If you need more help, feel free to ask - either in this thread, or in a new thread in the Access forum (with a reference to this thread).

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (2002)

    Roberta,

    can you ecplain the longer term use of the spreadsheet at all? i am wondering whether you could use the external data links from excel to access rather that from access to excel. if you can shed a little more light on your project i may be able to suggest a way that would avoid too much vba

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (2002)

    Hans,

    Thanks for the post. I will research the option you spoke of. Over the weekend I mulled it over in my mind (I was unable to tinker with the project) and realized that the xferbase was out of the question, and the xferSpreadsheet wouldn't work either for the exact reason you mentioned. I didn't realize however, there may be the option of Excel.Application. I will check into it. I don't know the first thing about it.

    I had considered the linking of the tables as suggested in the next post, but because this db will be on an intranet, I don't think this would be adviseable. The purpose of the spreadsheet is because the endusers aren't familiar and don't want to learn Access. Therefore, they want the responses from the database shipped to an Excel spreadsheet.

    My logic with that mind was to create a temp table, update the table in the AfterUpdate Event of the last field of the form (all is well so far), export that data to Excel (Stumped Here), and then Delete the data from the temp table to prepare it for the next use (no problem).

    On Friday, I managed to Export the data to Excel w/o any problems, but as it was pointed out, it will erase all that was there previously. I need ALL existing data to stay intact. So if anyone has any suggestions, I would be most greatful!

    Thanks again
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (2002)

    It would not be difficult to write VBA code to open a workbook exported from Access and append the data in that workbook to another workbook that contained any previous data. I could help write that code that would run in Excel, but that would require that you export the workbook from Access and then open Excel manually and run the macro. It sounds like you want this to all happen automatically from within Access. If that is the case, then you probably should ask this question over in the Access forum where the people who know the Access Object Model hang out.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (2002)

    hi Roberta,

    my suggestion is to work directly from excel, and use some DAO (yes, DAO, the predecessor of ADO) to import/append data immediately from within the workbook the users will be working with.

    greetings,

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (2002)

    Hello Again,

    I've been busy, I have the following code and all is well....well except for 1 tiny exception. If you notice---The .Range (A2)....I would like for that to be the next available cell, can someone please help me with this one last piece of the puzzle. Thanks.



    Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht As Excel.Worksheet
    Dim db As Database
    Dim rs As Recordset
    Const conMAX_ROWS = 20000
    Const conSHT_NAME = "Testing"
    Const conWKB_NAME = "cocuments and Settingsrnewt01My DocumentsComTrac.xls"
    'Const conRANGE = "RangeForRS"

    Set db = CurrentDb
    Set objXL = New Excel.Application
    Set rs = db.OpenRecordset("tblComExport", dbOpenSnapshot)
    With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
    Set objSht = objWkb.Worksheets.Add
    objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    .Range("A2").CopyFromRecordset rs
    End With
    Set objSht = Nothing
    Set objWkb = Nothing
    Set objXL = Nothing
    Set rs = Nothing
    Set db = Nothing
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (2002)

    hi Roberta,

    this was the code i am referring to in my post - well done!
    replace
    .Range("A2").CopyFromRecordset rs
    with
    .Range("A65536").end(xlup).offset(1,0).CopyFromRec ordset rs

    and you're done!

    greetings,

  9. #9
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet (2002)

    I know I am a little late to the party on this one, but I don't hang around the Excel lounge very often....I'm more of a VB/Access/Web guy, then an excel fellow.

    However, I have done data dumps to and from Excel. I have found a nifty little trick. You can use ADO to work with Excel, in both reading and writing to an Excel file. It is VERY fast, much faster then automation, because with Automation you are working through Excel, not seeing it as a database, but instead a spreadsheet.

    I am attaching an example of how to do what you want. It's a zip file with two files. An Excel and Access file. (Both 2k....I used A2K because it uses ADO natively, but I can give you a VB example, or A97 example, though the A97 example would need to have a reference to ADO in order to work.) Put both files in the same folder (doesn't matter what folder). Then open the Access .mdb, and open the only form within it (frmExcelDump). This form as a text box, a command button, and a label at the bottom. The textbox tells the code how many times you want to add the tblPhoneNumbers to the Excel file. (I put this there to show how fast ADO is, even with a ton of records involved). The label at the bottom will show the number of 'records' in the Excel file. Put how many times you want it to loop, and hit the command button. 1 is almost instantaneous, 50 takes a few seconds, and 100 takes about 4 or 5 seconds. (that is 1, 50 and 100 times close to 300 records.....also an added delay is involved by 'requerying' the Excel file to show how many records are now in it.)

    <img src=/S/stop.gif border=0 alt=stop width=26 height=24>

    A few items to note. First, the ADO connection object needs an Extended property to have it open an Excel file as a 'database'. (My code has this in it.....). Next, the Excel file has to 'sorta' fit a table format. (ie, data going down the columns, with a 'header'). I don't know if the header is absolutely required, but it helps, because then you can refer to the fields in the recordset object by name or number. Finally, you can use SQL Statements to 'query' your excel data, I did this with showing the number of records in the excel file. There is a trick though. In adding the records, the 'table' name is the name of the sheet with a dollar sign $ after it. In an SQL statement, the table has to be 'wrapped' in `s. The ` character should be the same key as the ~ tilde (without shifting).

    Anyhow, I don't know if this is a dead issue or not, but ADO is something you may want to look into. By using ADO to send data to Excel, it is going to be faster, and you also don't have to worry about finding the next available line, since .AddNew is doing that for you!

    Have fun..... <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25>
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    great stuff Drew, thanks!

    <P ID="nt"><font size=-1>(No Text)</font>

Posting Permissions

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