output to excel - format issue 1

[mrsbean]

I am using some code I got from another source to output from Access to Excel. I want to create an Excel worksheet with specific formatting at the top of the page and then place the data following that. The following code works - sort of. It does place the text exactly where I want it, but it doesn't format it. I want to put in some merged cells and some borders. Can someone tell me why it is not formatting the text? If I run a macro in Excel with the same general code, it works - formats with the borders and merges the cells.

Private Sub cmdMamasItems_Click()
Dim response As VbMsgBoxResult

response = _
MsgBox("This action may take several minutes." & _
vbCrLf & "Do you wish to continue...?", _
vbQuestion + vbOKCancel)

If response = vbOK Then
DoCmd.Hourglass True

Dim rs As DAO.Recordset
Dim xlApp As Excel.Application
Dim intCol As Integer
Dim strFileName As String
Dim strSQL As String

Set xlApp = New Excel.Application
xlApp.Workbooks.Add

Set rs = CurrentDb.OpenRecordset("qry_MAMAS_ITEMS")

'add title to this section
xlApp.Cells(2, 3).Value = "Deluxe Pizza"
xlApp.Range("c2:i2").Select

Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True

End With

xlApp.Cells(2, 10).Value = "$5 Pizza"
xlApp.Range("j2:k2").Select

Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

xlApp.Cells(2, 14).Value = "Slices"
xlApp.Range("n2:o2").Select

Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

xlApp.Cells(2, 16).Value = "Drinks"
xlApp.Range("p2:s2").Select

Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

xlApp.Cells(2, 20).Value = "Extras"
xlApp.Range("t2:y2").Select

Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With


xlApp.ActiveWorkbook.SaveAs FileName:="mamasItems.xls"

Shell "C:\Program Files\Microsoft Office\OFFICE11\excel.exe"
& " " & "mamasItems.xls", vbMaximizedFocus

xlApp.Quit


Set xlApp = Nothing
Set rs = Nothing
End If


ExitHere:
DoCmd.Hourglass False
Exit Sub

'HandleErrors:
'MsgBox Err.Description
'Resume ExitHere
End Sub

MrsBean

 

[AlexCuse]

MrsBean-

I think there are a few ways around this, but what I have always done is stored a template page, copied any formatted cells from that, and then looped through populating the data area with whatever I need there. Here's an example:

Dim xlApp As Object
Dim strFile As String
Dim CNN As ADODB.Connection
Dim rsRegn As New ADODB.Recordset
Dim intRow As Integer

Set CNN = CurrentProject.Connection

strFile = "G:\Some\Dir\FileName.xls"

Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open strFile

With xlApp
    'copy template into new sheet, set sheet name to current date
    .ActiveWorkbook.Sheets.Add
    .ActiveWorkbook.Worksheets(1).Name =  Format(Date, "mmddyy")
    .Sheets("Template").Select
    .ActiveSheet.Cells.Select
    .Selection.Copy
    .Sheets(DateName).Select
    .ActiveSheet.Cells.Select
    .Selection.PasteSpecial
    'set file receipt date
    .Sheets(DateName).Select
    .ActiveSheet.Range("B4").Value = Format(Date, "mm/dd/yyyy")
    
    
    'place query results for Region Breakouts into Excel Report
    rsRegn.Open "qryMyQuery", CNN
    
'set intRow to row to start on (in excel)
    intRow = 2
    rsRegn.MoveFirst
    While Not rsRegn.EOF
'loop through recordset, assigning values to cells in column A, B & C (rownum = intRow)
        .ActiveSheet.Range("A" & intRow).Value = rsRegn.Fields(0).Value
        .ActiveSheet.Range("B" & intRow).Value = rsRegn.Fields(1).Value
        .ActiveSheet.Range("C" & intRow).Value = rsRegn.Fields(2).Value
        intRow = intRow + 1
        rsRegn.MoveNext
    Wend
        
    'Save and Quit
    .ActiveWorkbook.Save
    .Quit
End With

'dispose of objects
Set xlApp = Nothing
Set rsRegn = Nothing
Set CNN = Nothing

If you need more precise formatting, I recommend using Excel's macro recorder. You can put some data in your spreadsheet, start recording a macro, perform all your formatting, then stop. When you view the macro in Excel's vba editor, you will see code that can easily be modified to use from access. I have some code that changes width of cells and what not, but since this is a highly specialized task I'd recommend using the macro recorder. If you run into any problems converting the code, you know where to go

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mrsbean]

Thanks Alex. The template seems like an excellent approach. I used the macro recorder to produce the code which you saw here. I wouldn't have had a clue how to come up with all that on my own.

With a template, I will have the formatting at the top of the page how I want it, and then I can dump the data where it needs to go after the header.

Linda

 

[combo]

You miss some objects in your code: Workbook, Worksheet and Range. They would allow precise object pointing:
set xlWbk=xlApp.Workbooks.Add
set xlWks=xlWbk.Worksheets(1)
after which you could rather use:
xlWks.Cells(2, 3).Value = "Deluxe Pizza"

Probably access does not understand 'Selection'. You could rather (and no selecting required here):
Set xlRng=xlWks.Range("n22")
With xlRng
...

combo

 

[PHV]

Replace all occurrences of Selection with xlApp.Selection

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