Re: Formatting Excel File

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Steven M. Britton (StevenMBritton_at_discussions.microsoft.com)
Date: 03/21/05 

Date: Mon, 21 Mar 2005 08:03:10 -0800

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]" wrote:

> 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.
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "Steven M. Britton" <StevenMBritton@discussions.microsoft.com> wrote in
> message news:A1A12C93-16A2-47BD-B18D-03A53ABC8225@microsoft.com...
> > 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]" wrote:
> >
> >> 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>
> >>
> >>
> >>
> >> "Steven M. Britton" <StevenMBritton@discussions.microsoft.com> wrote in
> >> message news:EE37F7AC-3725-4C76-8967-1456E70E6522@microsoft.com...
> >> > 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
> >> > ***.
> >> > 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]" wrote:
> >> >
> >> >> What is xlsApp? If it's EXCEL.Application, you need to set references
> >> >> to
> >> >> the
> >> >> workbook and the work***, and then use the Range object from the
> >> >> work***.
> >> >> --
> >> >>
> >> >> Ken Snell
> >> >> <MS ACCESS MVP>
> >> >>
> >> >>
> >> >>
> >> >> "Steven M. Britton" <StevenMBritton@discussions.microsoft.com> wrote
> >> >> in
> >> >> message news:1CEF6862-0D90-4626-9A12-BA83C50AADF3@microsoft.com...
> >> >> > On why the orginial section of code doesn't work to format the Excel
> >> >> > file?
> >> >> >
> >> >> >
> >> >> > "Ken Snell [MVP]" wrote:
> >> >> >
> >> >> >> Ideas about what?
> >> >> >>
> >> >> >> --
> >> >> >>
> >> >> >> Ken Snell
> >> >> >> <MS ACCESS MVP>
> >> >> >>
> >> >> >> "Steven M. Britton" <StevenMBritton@discussions.microsoft.com>
> >> >> >> wrote
> >> >> >> in
> >> >> >> message news:5F002A4B-2F5D-4F82-BFE6-A575DE7D4D9A@microsoft.com...
> >> >> >> >I have a docmd.TransferSpread*** 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
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Quantcast