Can I assume...docmd.transferspreadsheet DOES NOT Append?...

[snayjay]

I've searched and searched for an answer so now I'm posting. I'm trying to append a worksheet in a particular workbook and not create a new worksheet in the workbook. Reason I MUST use the old worksheet is because the first 4 lines are structed headers...with comments and autofilters and such. I don't know how to format the spreadsheet thru code so if I could just drop the data into the formatted spreadsheet everything would be fine.

Backstory (read if you are bored) I'm in the military and just came to a new office. There is a process that one of my troops has to do everymorning...taking him/her about 1 hour. All they are doing is pulling down a report in Excel format. Then because the monkeys higher in the trees are too busy scratching their butts to filter information for themselves, my troops have to make seperate worksheets within the workbook and separate (filter) the information to each tab so that the lazy people who make more than we do don't have to hit an autofilter dropdown to find the information. So I decided to make a database do it all. I've made it pull the excel data into the main table. But when I try to export it to the formatted worksheet (in a workbook) it says the worksheet is already created and won't do it.

Dim qdf As New DAO.QueryDef
Dim db As DAO.Database
qdf.SQL = Me.RecordSource
qdf.Name = "D23"
Set db = CurrentDb()
db.QueryDefs.Append qdf
DoCmd.TransferSpreadsheet acExport, 8, qdf.Name, Forms!MAINfrm!D23OUTPUT.Value, true
db.QueryDefs.Delete qdf.Name

In the excel spreadsheet I'm transferring data to, there is a worksheet called D23. That's where I want the data going.

Anyhelp would be greatly appreciated!

 

[TheAceMan1]

If you select Import in the Transfer Type argument, Microsoft [blue]Access appends[/blue] the spreadsheet data to this table [blue]if the table already exists[/blue]. [purple]Otherwise, Microsoft Access creates a new table[/purple] containing the spreadsheet data.

In VBE help look for [blue]TransferSpreadsheet Action[/blue].

See Ya! . . . . . .

 

[JerryKlmns]

With this method
DoCmd.TransferSpreadsheet acExport [, spreadsheettype], tablename, filename[, hasfieldnames][, range]

when you export to a spreadsheet, you must leave the range argument blank. If you enter a range, the export will fail.

So, if you want to keep your formating, you should either populate the recordsets of the queries to excel using automation, or with excel Get External Data from your database!

 

[snayjay]

Thanks for responding but...

TheAceMan1... you read my post wrong.

If you select Import in the Transfer Type argument, Microsoft Access appends the spreadsheet data to this table if the table already exists. Otherwise, Microsoft Access creates a new table containing the spreadsheet data.

If you reread my code you'll see I'm choosing Export. Import is used to bring data from the spreadsheet to my database table. I'm not doing that.

JerryKlmns... if you read my code you'll see I didn't put in a range. I too read that blurp about the range in the help files but that isn't my problem. I know very very little about coding in Excel, so I'm going to have to read up on some automation techniques especially the Get External Data...that sounds like what I need, but I'm clueless there. I'd really like to do it all from the database.

The way I have it, my office emails a spreadsheet out to our customers everymorning and it takes usually 30 mins+ to get the data formatted and separated the way we need to have it. So I wanted to make a database that does this formatting for us. The data is compiled in a report form (non-delimited)... so I have to use a program called Monarch to extract the data and put it to a spreadsheet format. So I made a form on my database that has a textbox, that uses a file open dialog. You basically only have to do two things with my database. 1) go to the textbox which opens up the file open... dialog and choose the file with the data. Then click a button. All the work is done on the button_click() event. First it exports the data to a tab in a workbook, then selectively thru queries pulls certain data to other tabs in the workbook, then formats the data the way it's supposed to be formatted. I have the file open dialog working and the export data to the workbook works, but won't append, it wants to create a new tab each time. I'm referencing the specific tab I want the data to go to.

DoCmd.TransferSpreadsheet acExport, 8, qdf.Name, Forms!MAINfrm!D23OUTPUT.Value, True

which is the "qdf.Name" in this case is "D23"... but it errors out and says there is already a D23 tab...duh, that's the tab I want the data to go to.

Somebody please say they understand what I'm talking about and have a fix.

Any help would be greatly appreciated...and thanks again you too for trying.

 

[crobg]

We do that at my work. The data is in Access and we use Excel to present the data. I didn't do it personally so I don't have the code but here are the steps:

1. Setup your query.
2. Create a macro in Excel.
3. In your Excel VBA window go to References and make sure that you have DAO selected. (Use the version your database is using.)
4. Connect to your database. (See Excel VBA help files on DAO).
5. Run the query in the Excel Macro like you would in Access.
6. Setup a loop to copy the data from the query into your Excel spreadsheet:

Do while rs.EOF = false
lngRow = 5 'Your start row
Me.Cells(1, lngRow) = rs![field1]
Me.Cells(2, lngRow) = rs![field2] ' repeat for each coloumn
...
lngRow = lngRow + 1
Loop

Be sure to consult the Execel VBA help file for correct syntax but that should get you going in the right direction.

 

[PHV]

You may also consider the CopyFromRecordset method of the Range object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886