TransferSpreadsheet (2002)

Topic

New Reply

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,

Reply | Quote

Replies

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).

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

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

Reply | Quote

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 = “c:Documents 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

Reply | Quote

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).CopyFromRecordset rs

and you’re done!

greetings,

Reply | Quote

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.)

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…..

Reply | Quote

(No Text)

Reply | Quote