Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook save behavior using VB6 very confusing
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook save behavior using VB6 very confusing
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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook save behavior using VB6 very confusing
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" wrote: 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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook save behavior using VB6 very confusing
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")..HorizontalAlignm ent = 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 "Jim Cone" wrote: 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- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook save behavior using VB6 very confusing
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 "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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook save behavior using VB6 very confusing
Hello kounoike.
That was it. It was Autofitting without the data ini it. Thanks for all of your help. Tony "kounoike" wrote: 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 "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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel workbook save behavior using VB6 very confusing
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 "Jim Cone" wrote: 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")..HorizontalAlignm ent = 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 "Jim Cone" wrote: 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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Default Behavior of Saving as Save As | Excel Discussion (Misc queries) | |||
Binary save - strange behavior | Excel Discussion (Misc queries) | |||
Confusing Invisible Excel Sheet | Excel Discussion (Misc queries) | |||
Auto run maco/change behavior of save/close | Excel Discussion (Misc queries) | |||
Weird File Open/Save As Behavior | Excel Discussion (Misc queries) |