By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 2,052 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Delete lines in Excel through Access??

100+
P: 122
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?
Jun 27 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
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).
Expand|Select|Wrap|Line Numbers
  1. Dim objExcel As Excel.Application
  2. Dim HeaderRS As DAO.Recordset
  3. Dim BodyRS As DAO.Recordset
  4. Set objExcel = New Excel.Application
  5. Set HeaderRS = CurrentDb.OpenRecordset("headerqueryname")
  6. Set BodyRS = CurrentDb.OpenRecordset("bodyqueryname")
  7. objExcel.Workbooks.Add
  8. With objExcel.ActiveSheet
  9.     .Cells(1, 1).CopyFromRecordset HeaderRS
  10.     .Cells(2, 1).CopyFromRecordset BodyRS
  11. End With
  12. objExcel.DisplayAlerts = False
  13. objExcel.ActiveWorkbook.SaveAs Filename:="yourfilename.csv", FileFormat:=xlCSV
  14. objExcel.Quit
  15. objExcel.DisplayAlerts = True
  16. HeaderRS.Close
  17. BodyRS.Close
  18. Set objExcel = Nothing
  19.  
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
Jun 27 '08 #2

100+
P: 122
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.
Jun 28 '08 #3

100+
P: 122
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?
Jun 28 '08 #4

100+
P: 122
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. =)
Jun 28 '08 #5

Expert Mod 2.5K+
P: 2,545
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
Jun 28 '08 #6

100+
P: 122
Great! Thanks Stewart.
Jun 28 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.