Excel workbook save behavior using VB6 very confusing

[Tony Girgenti]

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(newExcelWorkbookNameString)
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

 

[Jim Cone]

I see a two things that could be affecting your program...

The variable rowNumber has no value.
Selection applies to the ActiveWindow and your Application is Not visible.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"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
-snip-

 

[Tony Girgenti]

Hello Jim.

I debugged the program and rowNumber had a value wherever it was used.

When you say "Selection applies to the ActiveWindow ", which selection are
you referring to? If the Application is not visible, how is all the other
stuff happening?

Can you suggest another way to do all this?

Thanks,
Tony

 

[Jim Cone]

Tony,

1. From your code...
"newExcelApplication.Visible = False"
The application does not have to be visible in order to work with it.
In fact that is preferred if no user interaction is required.

2. From help...
"Application.Selection - Returns the selected object in the active window.
Using this property with no object qualifier is equivalent to using Application.Selection"

3. There is no declaration of rowNumber in your code and no value
is assigned to it. I assume rowNumber is a public variable?

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

5. All other uses of "Selection" should be changed to the appropriate range.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Tony Girgenti"
wrote in message
Hello Jim.
I debugged the program and rowNumber had a value wherever it was used.
When you say "Selection applies to the ActiveWindow ", which selection are
you referring to? If the Application is not visible, how is all the other
stuff happening?
Can you suggest another way to do all this?
Thanks,
Tony

 

[kounoike]

I don't know what DataArray is. but it's seems that you do autofit to
columns("A:L") before you put data in range("A2:L2"). so i doubt about that
it works all well when you don't need create a new workbook and don't in
other case. i think you shoud do autofit after you put the data into the
range.

keiji

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

- snip -

 

[Peter T]

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]

Hello kounoike.

That was it. It was Autofitting without the data ini it.

Thanks for all of your help.
Tony

 

[Tony Girgenti]

Hello Jim.

rowNumber is declared in the General/Declarations and assigned a value in
fillArrayFromInputFile, which is not part of the code i posted.

The problem is resolved due to another post in this thread by kounoike.

However, i really appreciate your advice in your five points. I changed all
of my selection references to ranges and the program seems to work fine.

Thanks for all of your help.
Tony