Excel workbook save behavior using VB6 very confusing
Prefix each instance of 'selection' with your reference to Excel
newExcelApplication .Selection.etc
Better still, avoid use of Select and Selection altogether, eg
newExcelWorkSheet.Range("A1:L1").font.bold = true
Regards,
Peter T
"Tony Girgenti" wrote in message
...
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
|