Hello.

I'm using VB6.0, SP6 and Excel 2003 to open an Excel workbook, put data into
it, format it and save it. I have placed my module at the end of this post.

When i run the program and the Excel spreadsheet already exists it does all
of the data and formatting correctly. If i run the program and the
spreadsheet needs to be created, it puts the data in, but the last part of
the formatting is not done. Namely:
newExcelWorkSheet.Columns("A:L").Select
Selection.Columns.AutoFit

Can anybody explain how to fix this? I want all data and formatting to be
applied whether the spreadsheet already exists or not.

Any help would be gratefully appreciated.

Thanks,
Tony

================================================== ===
Private Sub Form_Load()

Me.Show

informationButton.Visible = False

createTestData
fillArrayFromInputFile

Dim newExcelApplication As Excel.Application
Dim newExcelWorkbook As Excel.Workbook
Dim newExcelWorkSheet As Excel.Worksheet
Dim newExcelWorkbookNameString As String
Dim rangeCellOne As String
Dim rangeCellTwo As String

'Start a new workbook in Excel.
newExcelWorkbookNameString = "R:\Minisoft\TestExc\CASExcel.xls"
Set newExcelApplication = New Excel.Application
newExcelApplication.Visible = False

On Error Resume Next
Set newExcelWorkbook =
newExcelApplication.Workbooks.Open(newExcelWorkboo kNameString)
If newExcelWorkbook Is Nothing Then
Set newExcelWorkbook = newExcelApplication.Workbooks.Add
newExcelWorkbook.SaveAs newExcelWorkbookNameString
End If

'Add headers to the worksheet on row 1.
Set newExcelWorkSheet = newExcelWorkbook.Worksheets(1)
newExcelWorkSheet.Name = "CASExcel"

rangeCellOne = "A1"
rangeCellTwo = "L" & rowNumber + 1

newExcelWorkSheet.Range(rangeCellOne, rangeCellTwo).Name = "CASList"
newExcelWorkSheet.Range("CASList").Select
Selection.Font.Name = "Times New Roman"
Selection.Font.Size = 12

rangeCellOne = "D2"
rangeCellTwo = "D" & rowNumber + 1

Dim newRange As Range
Set newRange = newExcelWorkSheet.Range(rangeCellOne, rangeCellTwo)
newRange.NumberFormat = "0.00%"

newExcelWorkSheet.Range("A1").Value = "CAS#"
newExcelWorkSheet.Range("B1").Value = "CAS Desc"
newExcelWorkSheet.Range("C1").Value = "All Item Numbers"
newExcelWorkSheet.Range("D1").Value = "% CAS each item"
newExcelWorkSheet.Range("E1").Value = "Max Daily Amt"
newExcelWorkSheet.Range("F1").Value = "Avg Daily Amt"
newExcelWorkSheet.Range("G1").Value = "Total # Days on Site"
newExcelWorkSheet.Range("H1").Value = "C.H.H"
newExcelWorkSheet.Range("I1").Value = "A.H.H"
newExcelWorkSheet.Range("J1").Value = "Reac"
newExcelWorkSheet.Range("K1").Value = "Fire"
newExcelWorkSheet.Range("L1").Value = "Pres"

newExcelWorkSheet.Range("A1:L1").Select
Selection.Font.Bold = True
Selection.Font.Size = 11
Selection.HorizontalAlignment = xlCenter

newExcelWorkSheet.Columns("A:L").Select
Selection.Columns.AutoFit

newExcelWorkSheet.Range("A1").Select

'Transfer the array to the worksheet starting at cell A2.
newExcelWorkSheet.Range("A2").Resize(rowNumber, 12).Value = DataArray

'Save the Workbook and quit Excel.
newExcelApplication.DisplayAlerts = False
newExcelWorkbook.Close SaveChanges:=True

Set newExcelWorkSheet = Nothing
Set newExcelWorkbook = Nothing
newExcelApplication.Quit
Set newExcelApplication = Nothing

informationLabel.Caption = "Program Finished. Click OK to Continue."

informationButton.Visible = True

End Sub