Formatting Excel File

[Guest]

I have a docmd.TransferSpreadsheet and after I transfer it I am formatting
it. Everything I have up to these lines works to do the formatting. I just
recorded a Macro in Excel and then pasted the code into Access... Any ideas.

xlsApp.Range("A:C").Select
With xlsApp.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'xlsApp.Columns("F:F").Select
'xlsApp.Selection.HorizontalAlignment = xlCenter
'xlsApp.Columns("H:H").Select
'xlsApp.Selection.HorizontalAlignment = xlCenter
'xlsApp.Columns("J:K").Select
'xlsApp.Selection.HorizontalAlignment = xlCenter
'xlsApp.Columns("Q:Q").Select
'xlsApp.Selection.HorizontalAlignment = xlCenter

xlsApp.Range("A1:AD1").Select
With xlsApp.Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
xlsApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
xlsApp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With xlsApp.Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With xlsApp.Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With xlsApp.Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With xlsApp.Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With xlsApp.Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

 

[Ken Snell [MVP]]

Ideas about what?

 

[Guest]

Ideas about what?

 

[Ken Snell [MVP]]

What is xlsApp? If it's EXCEL.Application, you need to set references to the
workbook and the worksheet, and then use the Range object from the
worksheet.

 

[Guest]

Ken thanks again for your response. The xlsApp is the EXCEL.Application, but
I "think" I am properly setting the references to the workbook then sheet. I
can get the format to work on everything up to the point of centering the
columns. Below is the entire function, if you could please take a look and
see if you know were I am missing something.

Function SendtoExcel()

Dim xlsApp As Object
Dim wkb As Object
Dim strPath As String
Dim wkbTemp As Object
Dim wks As Object

On Error Resume Next
Set xlsApp = GetObject("Excel.application")
If Err Then
Set xlsApp = CreateObject("Excel.Application")
End If
xlsApp.Activate
xlsApp.Visible = False
strPath = "C:\file\temp.xls"

Set rs = db.OpenRecordset("tblModels")
DoCmd.SetWarnings False
With rs
.MoveFirst
Do While Not .EOF
vModel = !Model
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryLifeCycle", strPath, , vModel
.MoveNext
Loop
.MoveFirst

Set wkbTemp = xlsApp.Workbooks.Open(strPath)

Do While Not .EOF
vModel = !Model

xlsApp.Sheets(vModel).Select
xlsApp.Cells.Select
With xlsApp.Selection.Font
.Name = "Arial"
.Size = 7
End With

xlsApp.Columns("B:B").Select
xlsApp.Selection.NumberFormat = "mmm-yy"

'Format Date/Time column
xlsApp.Columns("C:C").Select
xlsApp.Selection.NumberFormat = "mm/dd/yyyy hh:mm:ss"

'Formant Numbers
xlsApp.Columns("T:W").Select
xlsApp.Selection.NumberFormat = "0.000_);(0.000)"
xlsApp.Columns("Y:AB").Select
xlsApp.Selection.NumberFormat = "$#,##0.000_);($#,##0.000)"
xlsApp.Columns("AD:AD").Select
xlsApp.Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"

'Format column widths
xlsApp.Columns("A:A").Select
xlsApp.Selection.ColumnWidth = 11
xlsApp.Columns("B:AD").Select
xlsApp.Selection.ColumnWidth = 20
xlsApp.Columns("B:AD").EntireColumn.AutoFit
xlsApp.Columns("AC:AC").ColumnWidth = 0.64
xlsApp.Columns("X:X").ColumnWidth = 0.64
xlsApp.Columns("S:S").ColumnWidth = 0.73
'Center Columns THIS DOESN'T WORK TO CENTER THEM, BUT DOES SELECT THEM?
xlsApp.Range("A:C").Select
With xlsApp.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End With

.MoveNext
Loop
.Close
End With

DoCmd.SetWarnings True
xlsApp.Visible = True
End Function

 

[Ken Snell [MVP]]

Try changing these lines:

xlsApp.Range("A:C").Select
With xlsApp.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


to these lines:

xlsApp.Columns("A:C").Select
With xlsApp.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


or to these lines:

Dim lngCol As Long
For lngCol = 1 To 3
With xlsApp.Columns(lngCol)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Next lngCol


--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Ken,

Thanks again. Made the change, no new result...??? I get the columns
selected, but it doesn't center them? Any further ideas?

-Steve

 

[Ken Snell [MVP]]

Sorry... I overlooked that you're trying to use the instrinsic EXCEL VBA
constants.

Change
xlCenter
to
-4108

and change
xlBottom
to
-4107

and change
xlContext
to
-5002

ACCESS has no knowledge of the values of the EXCEL constants unless you have
a reference set to the EXCEL library, which (as is correct) you don't have
as a referernce in your database.

 

[Guest]

Ken,

This did the trip. Now I have a follow up question, should I have an Excel
reference set in the database to format these files or is there a location
that I can look up these values to replace the constants? I am also trying
to make some lines and Access doens't understand them for the same reason.

Thank.

 

[Ken Snell [MVP]]

If you set a reference to EXCEL in your database's References (early
binding), then you can use that code only with that version of EXCEL. Using
late binding is preferred if your users might have different versions of
EXCEL installed; no reference errors will result.

You can get the value of the EXCEL intrinsic constants by opening EXCEL,
opening VBE in EXCEL, and then going to Immediate Window and typing
?xlCenter
and pressing Enter. The actual value will be returned just under it.


--

Ken Snell
<MS ACCESS MVP>