Access to Excel export with proper formatting

[mousie_12345]

The below code (http://www.tek-tips.com/viewthread.cfm?qid=1201057&page=1#) exports information for me from Access (http://www.tek-tips.com/viewthread.cfm?qid=1201057&page=1#) to Excel (http://www.tek-tips.com/viewthread.cfm?qid=1201057&page=1#), and this works perfectly, however, I need for the export to properly format my excel sheets before the export (bolding, column sizes,etc.). Based on the coding (http://www.tek-tips.com/viewthread.cfm?qid=1201057&page=1#) below, what would I need to add to this in order for this to work?

Private Sub Command4_Click()
Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT field1 FROM table ORDER By field1;")
strDt = Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Year(Date), "00")
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
strCrt = rs.Fields(0)
Set str1Sql = db.CreateQueryDef("" & strCrt, "SELECT table.* FROM table WHERE table.field1 = '" & strCrt & "';")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "" & strCrt, "C:\file " & strCrt & ".xls", True
DoCmd.DeleteObject acQuery, "" & strCrt
rs.MoveNext
Loop
End Sub

[matthewspatrick]

That depends. What formatting do you need, specifically?

[mousie_12345]

1 - I need the first row (A1:J1) to be BOLD and FONT = YELLOW.
2 - The rows underneath the header just need to be expanded to fit the data (so formatted column width).
3 - If possible, a subtotal at the end of COLUMN (J2:END) where it subtotals the entire column where the first NULL row appears in COLUMN J.

[XLGibbs]

You would need to do something more like this:



Private Sub Command4_Click()
Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String

Dim oApp as Excel.Application
Dim oWs as Excel.Worksheet
Dim oWb as Excel.Workbook


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT field1 FROM table ORDER By field1;")
strDt = Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Year(Date), "00")
rs.MoveLast
rs.MoveFirst

Set oApp = CreateObject("Excel.Application")



Do While Not rs.EOF
Set oApp = CreateObject("Excel.Application")
Set oWb as ActiveWorkbook
Set oWs as oWb.Sheets(1)
Dim rs2 as Recordset
Dim y as long, x as long

strCrt = rs.Fields(0)

Set str1Sql = db.CreateQueryDef("" & strCrt, "SELECT table.* FROM table WHERE table.field1 = '" & strCrt & "';")


Set rs2 = str1Sql,CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly, adCmdText
y = 1
With oWs
Do while not rs2.EOF

For x = 1 to rs2.Fields.Count
.cells(y,x) = rs2.Fields(x)
Next x

y = y+1

rs2.MoveNext
Loop
.Cells(1,1).EntireRow.Font = Bold
.cells(1,1).EntireRow.Interior.colorindex = 6
.cells.autofit
.cells(y+1,10) = WorksheetFunction.Sum(Range(Cells(2,10),cells(y,10))
End With
oWb.SaveAs {filename}
owb.Close
Set oWb = Nothing
Kill owb

rs.MoveNext
Loop
End Sub


Untested, but the idea is that you create the excel object via VBA and cotnrol that object there...applying the recordset to the worksheet as it goes through...this way you can set the format via VBA as needed. Otherwise, you can't control the format of the transferspreadsheet exactly.

[mousie_12345]

XL, The following code was highlighted in RED (syntax errors):

Set oWb As ActiveWorkbook
Set oWs As oWb.Sheets(1)

Set rs2 = str1Sql,CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly, adCmdText

oWb.SaveAs {filename}

[XLGibbs]

Set oWb as oApp.Workbooks.Add

Set oWS as oWB.Sheets(1)

set Rs2 = New Recordset
Rs2.Open str1Sql,currentproject.connection,acForwardOnly,acLockReadOnly, adCmdText


Sorry, it was written quickly and as stated was untested. I imagine you will have to make modifications since it was written on the fly. it was not intended to be cut/paste operational for your exact needs...more for example of the idea.

[mousie_12345]

Thanks, actually even the update following code does not work, not sure why (I understand I might have to manipulate, but not sure why the below does not work):

Set oWb As oApp.Workbooks.Add

Set oWS As oWB.Sheets(1)

[XLGibbs]

Sorry more typos..

Set = instead of Set As

[mousie_12345]

XL,

I am getting a run time error 3001 "Arugments are of the wrong type, are out of acceptable range, or are in conflict with one another" and the below code is highlighted....any thoughts as to why? I'm kinda a newbie to VBA so I'm slowly working thru my problems.

rs2.Open str1Sql, CurrentProject.Connection, acForwardOnly, acLockReadOnly, adCmdText

[Justinlabenne]

There is an example in the kb (http://vbaexpress.com/kb/getarticle.php?kb_id=662) similiar to what your wanting to do. You can also pre-format the excel file that you output to, and save it as a template, like this example (http://www.rogersaccesslibrary.com/download3.asp?SampleName=ExportToExcel.mdb)

[mousie_12345]

Justin, I appreciate your feedback, but I'm looking to format about 60 spreadsheets prior to it spitting all of them out (that is what my original code states above). I've tried using your methods and have not had luck. I think XL's format is more of what I'm looking for...just caught up on a runtime error now....

[XLGibbs]

XL,

I am getting a run time error 3001 "Arugments are of the wrong type, are out of acceptable range, or are in conflict with one another" and the below code is highlighted....any thoughts as to why? I'm kinda a newbie to VBA so I'm slowly working thru my problems.

rs2.Open str1Sql, CurrentProject.Connection, acForwardOnly, acLockReadOnly, adCmdText


ugh, more typos....I have been buried at work...so I should have been more careful..


s2.Open str1Sql, CurrentProject.Connection, adForwardOnly, adLockReadOnly, adCmdText


Had I spotted Justin's KB entry on the topic i would have just referred you to that...seems to perform a similar function as what my hastily written typo-laden error filled sample would be loosely intended to do.

[Justinlabenne]

I would like to see an example if you can provide one, maybe zip up the db and an example excel file of the formatting your after if can. Sensitive information excluded of course.

I do this quite often for clients, and I skip Access's report formatter altogether preferring to create pre-formatted Excel files and save them as templates. I open the template (simliar to the example in the second link I provided), save it somewhere if need be, then export query(s), in which the exported info is linked to cells in the formatted sheets, and then print the sheets.

It just depends on the structure of the project, hard to tell for me what your after without seeing it first hand.

[mousie_12345]

Well I don't have it quite working yet. I'm still getting errors from the above code that XL has been helping me with, still getting an error on



s2.Open str1Sql, CurrentProject.Connection, adForwardOnly, adLockReadOnly, adCmdText


Not sure if it should have stayed at Rs2 (but that didn't work either)...while I havent had much time over the weekend to try to work on this, I'm sure it can be done...