Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fully Qualifying - ActiveCell.SpecialCells(xlLastCell)).Select
I am trying to change the formatting of an Excel worksheet from within
Access. I have found a way to approach this using setting the format properties for a cell range. It sets the background white and prints (or doesnt print) borders for the active area. It produces what I want except that if I run it twice in a row I get a Method €˜Range of Object Global Failed error. Ive also found some Microsoft advice on this issue. It involves fully qualifying the range object. However, I dont know how to fully qualify the ActiveCell.SpecialCells(xlLastCell) statement. The code to set the cells white and set border properties is below. After that is the Microsoft example of how to resolve this issue. oSheet.Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select With Selection.Interior ..ColorIndex = 2 ..Pattern = xlSolid End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) '.LineStyle = xlContinuous ..LineStyle = xlNone '.Weight = xlThin '.ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic ..LineStyle = xlNone End With With Selection.Borders(xlEdgeBottom) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic ..LineStyle = xlNone End With With Selection.Borders(xlEdgeRight) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic ..LineStyle = xlNone End With With Selection.Borders(xlInsideVertical) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic ..LineStyle = xlNone End With With Selection.Borders(xlInsideHorizontal) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic ..LineStyle = xlNone End With Microsoft Discussion 1. Start a new Standard EXE project in Visual Basic. Form1 is created by default. 2. Click References from the Project menu and check the Object Library for the version of Excel you intend to automate. 3. Place a CommandButton on Form1. 4. Copy the following code to the Code Window of Form1: Option Explicit Private Sub Command1_Click() Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets("Sheet1") xlSheet.Range(Cells(1, 1), Cells(10, 2)).Value = "Hello" xlBook.Saved = True Set xlSheet = Nothing Set xlBook = Nothing xlApp.Quit Set xlApp = Nothing End Sub 5. On the Run menu, click Start or press the F5 key to start the program. 6. Click the CommandButton. No error occurs. However, a reference to Excel has been created and has not been released. 7. Click the CommandButton again and note that you receive one of the errors previously described. NOTE: The error occurs because the code refers to the Cell's method without preceding the call with the xlSheet object variable. 8. Stop the project and change the following line: xlSheet.Range(Cells(1,1),Cells(10,2)).Value = "Hello" to: xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(10, 2)).Value = "Hello" 9. Run the program again. Note that you can run the code multiple times without error. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fully Qualifying - ActiveCell.SpecialCells(xlLastCell)).Select
Activecell exists only on the active sheet. So if oSheet is not the active
sheet then your code will bomb. Also you can not select a rang on any sheet that is not active. You would need to select the sheet first and then select the range. Additionally SpecialCells(xlLastCell) is kinda dangerous code to use. The last cell is not always what you might think it is. If I understand what you are trying to do you want to apply formatting to the entire data range of a sheet? Do you know how many columns wide the spreadsheet is or do you need to determine that at run time? Is there one column that will alway be populated all the way down to the last data row? If you know both of these things then you can use code taht looks upwards from the bottom of the sheet to the first row with data to define the last row. Knowing that and how may columns wide the sheet is you can accurately define the data range. -- HTH... Jim Thomlinson "tmort" wrote: I am trying to change the formatting of an Excel worksheet from within Access. I have found a way to approach this using setting the format properties for a cell range. It sets the background white and prints (or doesnt print) borders for the active area. It produces what I want except that if I run it twice in a row I get a Method €˜Range of Object Global Failed error. Ive also found some Microsoft advice on this issue. It involves fully qualifying the range object. However, I dont know how to fully qualify the ActiveCell.SpecialCells(xlLastCell) statement. The code to set the cells white and set border properties is below. After that is the Microsoft example of how to resolve this issue. oSheet.Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select With Selection.Interior .ColorIndex = 2 .Pattern = xlSolid End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) '.LineStyle = xlContinuous .LineStyle = xlNone '.Weight = xlThin '.ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlEdgeBottom) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlEdgeRight) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlInsideVertical) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlInsideHorizontal) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With Microsoft Discussion 1. Start a new Standard EXE project in Visual Basic. Form1 is created by default. 2. Click References from the Project menu and check the Object Library for the version of Excel you intend to automate. 3. Place a CommandButton on Form1. 4. Copy the following code to the Code Window of Form1: Option Explicit Private Sub Command1_Click() Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets("Sheet1") xlSheet.Range(Cells(1, 1), Cells(10, 2)).Value = "Hello" xlBook.Saved = True Set xlSheet = Nothing Set xlBook = Nothing xlApp.Quit Set xlApp = Nothing End Sub 5. On the Run menu, click Start or press the F5 key to start the program. 6. Click the CommandButton. No error occurs. However, a reference to Excel has been created and has not been released. 7. Click the CommandButton again and note that you receive one of the errors previously described. NOTE: The error occurs because the code refers to the Cell's method without preceding the call with the xlSheet object variable. 8. Stop the project and change the following line: xlSheet.Range(Cells(1,1),Cells(10,2)).Value = "Hello" to: xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(10, 2)).Value = "Hello" 9. Run the program again. Note that you can run the code multiple times without error. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fully Qualifying - ActiveCell.SpecialCells(xlLastCell)).Select
My sheet will always be 19 colums wide (A:S) and column 3 (C) would always
have an entry. I was looking for code to do as you describe when I happened upon the SpecialCells(xlLastCell) method. Can you point me to an example of how to find the range by finding the last cell of column 3? Thanks "Jim Thomlinson" wrote: Activecell exists only on the active sheet. So if oSheet is not the active sheet then your code will bomb. Also you can not select a rang on any sheet that is not active. You would need to select the sheet first and then select the range. Additionally SpecialCells(xlLastCell) is kinda dangerous code to use. The last cell is not always what you might think it is. If I understand what you are trying to do you want to apply formatting to the entire data range of a sheet? Do you know how many columns wide the spreadsheet is or do you need to determine that at run time? Is there one column that will alway be populated all the way down to the last data row? If you know both of these things then you can use code taht looks upwards from the bottom of the sheet to the first row with data to define the last row. Knowing that and how may columns wide the sheet is you can accurately define the data range. -- HTH... Jim Thomlinson "tmort" wrote: I am trying to change the formatting of an Excel worksheet from within Access. I have found a way to approach this using setting the format properties for a cell range. It sets the background white and prints (or doesnt print) borders for the active area. It produces what I want except that if I run it twice in a row I get a Method €˜Range of Object Global Failed error. Ive also found some Microsoft advice on this issue. It involves fully qualifying the range object. However, I dont know how to fully qualify the ActiveCell.SpecialCells(xlLastCell) statement. The code to set the cells white and set border properties is below. After that is the Microsoft example of how to resolve this issue. oSheet.Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select With Selection.Interior .ColorIndex = 2 .Pattern = xlSolid End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) '.LineStyle = xlContinuous .LineStyle = xlNone '.Weight = xlThin '.ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlEdgeBottom) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlEdgeRight) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlInsideVertical) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlInsideHorizontal) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With Microsoft Discussion 1. Start a new Standard EXE project in Visual Basic. Form1 is created by default. 2. Click References from the Project menu and check the Object Library for the version of Excel you intend to automate. 3. Place a CommandButton on Form1. 4. Copy the following code to the Code Window of Form1: Option Explicit Private Sub Command1_Click() Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets("Sheet1") xlSheet.Range(Cells(1, 1), Cells(10, 2)).Value = "Hello" xlBook.Saved = True Set xlSheet = Nothing Set xlBook = Nothing xlApp.Quit Set xlApp = Nothing End Sub 5. On the Run menu, click Start or press the F5 key to start the program. 6. Click the CommandButton. No error occurs. However, a reference to Excel has been created and has not been released. 7. Click the CommandButton again and note that you receive one of the errors previously described. NOTE: The error occurs because the code refers to the Cell's method without preceding the call with the xlSheet object variable. 8. Stop the project and change the following line: xlSheet.Range(Cells(1,1),Cells(10,2)).Value = "Hello" to: xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(10, 2)).Value = "Hello" 9. Run the program again. Note that you can run the code multiple times without error. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fully Qualifying - ActiveCell.SpecialCells(xlLastCell)).Select
Sorry for taking so long... Meetings... The range you are going to want to
work with can be defined using this code... dim rngToFormat as Range with oSheet set rngToformat = .range(.range("S1"), _ .cells(rows.count, "C").end(xlUp).offset(0, -3)) end with with rngToFormat .border = xlThin .interior.colorindex = 5 end with -- HTH... Jim Thomlinson "tmort" wrote: My sheet will always be 19 colums wide (A:S) and column 3 (C) would always have an entry. I was looking for code to do as you describe when I happened upon the SpecialCells(xlLastCell) method. Can you point me to an example of how to find the range by finding the last cell of column 3? Thanks "Jim Thomlinson" wrote: Activecell exists only on the active sheet. So if oSheet is not the active sheet then your code will bomb. Also you can not select a rang on any sheet that is not active. You would need to select the sheet first and then select the range. Additionally SpecialCells(xlLastCell) is kinda dangerous code to use. The last cell is not always what you might think it is. If I understand what you are trying to do you want to apply formatting to the entire data range of a sheet? Do you know how many columns wide the spreadsheet is or do you need to determine that at run time? Is there one column that will alway be populated all the way down to the last data row? If you know both of these things then you can use code taht looks upwards from the bottom of the sheet to the first row with data to define the last row. Knowing that and how may columns wide the sheet is you can accurately define the data range. -- HTH... Jim Thomlinson "tmort" wrote: I am trying to change the formatting of an Excel worksheet from within Access. I have found a way to approach this using setting the format properties for a cell range. It sets the background white and prints (or doesnt print) borders for the active area. It produces what I want except that if I run it twice in a row I get a Method €˜Range of Object Global Failed error. Ive also found some Microsoft advice on this issue. It involves fully qualifying the range object. However, I dont know how to fully qualify the ActiveCell.SpecialCells(xlLastCell) statement. The code to set the cells white and set border properties is below. After that is the Microsoft example of how to resolve this issue. oSheet.Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select With Selection.Interior .ColorIndex = 2 .Pattern = xlSolid End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) '.LineStyle = xlContinuous .LineStyle = xlNone '.Weight = xlThin '.ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlEdgeBottom) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlEdgeRight) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlInsideVertical) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With With Selection.Borders(xlInsideHorizontal) '.LineStyle = xlContinuous '.Weight = xlThin '.ColorIndex = xlAutomatic .LineStyle = xlNone End With Microsoft Discussion 1. Start a new Standard EXE project in Visual Basic. Form1 is created by default. 2. Click References from the Project menu and check the Object Library for the version of Excel you intend to automate. 3. Place a CommandButton on Form1. 4. Copy the following code to the Code Window of Form1: Option Explicit Private Sub Command1_Click() Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets("Sheet1") xlSheet.Range(Cells(1, 1), Cells(10, 2)).Value = "Hello" xlBook.Saved = True Set xlSheet = Nothing Set xlBook = Nothing xlApp.Quit Set xlApp = Nothing End Sub 5. On the Run menu, click Start or press the F5 key to start the program. 6. Click the CommandButton. No error occurs. However, a reference to Excel has been created and has not been released. 7. Click the CommandButton again and note that you receive one of the errors previously described. NOTE: The error occurs because the code refers to the Cell's method without preceding the call with the xlSheet object variable. 8. Stop the project and change the following line: xlSheet.Range(Cells(1,1),Cells(10,2)).Value = "Hello" to: xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(10, 2)).Value = "Hello" 9. Run the program again. Note that you can run the code multiple times without error. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
.Cells.SpecialCells(xlLastCell) | Excel Programming | |||
SpecialCells(xlLastCell) | Excel Programming | |||
MS Query automatically fully qualifying SQL Select Statement | Excel Programming | |||
.SpecialCells(xlLastCell).Select | Excel Programming | |||
XP VBA: Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select | Excel Programming |