Delete lines in Excel through Access??

[Annalyzer]

My database must produces a .csv file with a header line that is different from the detail lines. This .csv file contains a monthly report to an outside agency and is uploaded online so it must follow specific guidelines.

As usual, I think I have made the processing way more convoluted than necessary. Here is what I have currently:

1) Macro #1 runs query #1 to create (or overwrite) a table with the current month's header information. This comes from a table.

2) Macro #2 runs query #2 to create (or overwrite) a table that contains the detail lines. This comes from a table as well.

3) Each of these newly created tables is then used to create a subreport which contains text boxes with the data in the order required for the .csv file. There are no labels included in these subreports.

4) The 2 subreports are then brought together into a single report with one header line in the report header section, and all the detail lines in the report detail section.

5) This final report is then programmatically output to an Excel file.

The probem is, the Excel file has extra rows that I don't want. Row 1 has the names for each field in the header line, row 3 is a blank line, and row 4 has names for each field in the detail lines.

The user then has to manually delete these lines and save the file in .csv format. If they delete the wrong line(s) the file will not upload correctly.

Is there a way to programmatically delete these lines before the database gives control back to the user? Or, have I overthought this whole process and if so, can you help me find an easier way to get to the desired result?

[Stewart Ross]

Way overcomplicated as you thought, Annalyser.

Much simpler to use VBA to

1. Open an instance of Excel as an automation object
2. Use the Excel CopyFromRecordset method to copy the headings query recordset contents to the worksheet
3. Move the current activecell to the next blank row then use the same CopyFromRecordset method to copy the body data to the Excel workbook
4. Save the workbook as a CSV file
5. Close the Excel automation object.

CopyFromRecordset does not include field headers - which for your application is ideal.

A skeleton for how this is done in VBA is included below, with placeholders for your header and body query names and the filename. You would place the code in the on-click sub of a command button on a form somewhere, replacing your current use of macros.

I assume that the header is the first row (hence the use of Cells(1,1) to refer to row 1 column1 - Cell A1) and that the data is on the second and subsequent rows (hence cells(2, 1) for the second row column 1 - Cell A2).

Dim objExcel As Excel.Application
Dim HeaderRS As DAO.Recordset
Dim BodyRS As DAO.Recordset
Set objExcel = New Excel.Application
Set HeaderRS = CurrentDb.OpenRecordset("headerqueryname")
Set BodyRS = CurrentDb.OpenRecordset("bodyqueryname")
objExcel.Workbooks.Add
With objExcel.ActiveSheet
    .Cells(1, 1).CopyFromRecordset HeaderRS
    .Cells(2, 1).CopyFromRecordset BodyRS
End With
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs Filename:="yourfilename.csv", FileFormat:=xlCSV
objExcel.Quit
objExcel.DisplayAlerts = True
HeaderRS.Close
BodyRS.Close
Set objExcel = Nothing
 

You will need references to the DAO object library and the Excel object library to be set before this code will compile. From the VB editor select Tools, References and ensure that Microsoft DAO 3.6 (or later) is ticked, and Microsoft Excel 11 (or later).

-Stewart

[Annalyzer]

Thank you muchly, Stewart. I haven't worked with recordsets before, but I suppose it's time I learned. I'll give this a try - it does sound easier.

[Annalyzer]

Quick question, do the DAO object library and Excel object library go with the database when I split it and move it to a server/client setup? Or do I have to set them on each machine that gets a copy of the front end?

[Annalyzer]

Yowza! It worked! I just created one function that does the job of 2 macros, 2 queries, 2 tables, 2 subreports, 1 report and a partridge in a pear tree! Okay, maybe not the partridge, but I am tickled!! I did some reading - this recordset thing is powerful stuff! I see so many possibilities. Thank you!

Still wondering if I need to set the reference for object libraries on each machine the database is installed on or if these references are stored in the database? I'll go rtm, but in case I don't find it, an answer is always appreciated. =)

[Stewart Ross]

Glad to be of assistance, Annalyser. As for the references, these normally go with the DB itself - as long as the DLLs referenced are installed on the target machine. Since in this case DAO is part of Access and Excel is part of Office you should have no problems with the references once you distribute the database to your users.
-Stewart

[Annalyzer]

Great! Thanks Stewart.