Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cells in Excel accessed from VB6
Is it possible select a range of cells in an Excel spreadsheet under access
from a VB6 program? The many permutations I have tried shown below fail. Surely I should be able to Select a range of cells, because I have to do this to sort the rows. Thanks for kind assistance. Lamont Private DataRange As Excel.Range Private DataSheet As New Excel.Worksheet Private MyWorkbook As New Excel.Workbook Private Sub Command1_Click() Dim strMyXLSBkSpec As String Form1.Show strMyXLSInSpec = "C:\DocData.xls" 'My spreadsheet containing data about paper documents Set MyWorkbook = GetObject(strMyXLSInSpec) 'This works to access my Excel File MyWorkbook.Activate 'this works Set DataSheet = MyWorkbook.Sheets("DataSheet") 'this works Set DataRange = DataSheet.UsedRange 'this works Debug.Print DataRange.Rows.Count 'this works DataSheet.Activate 'this works 'DataRange.Select 'Select method of range class failed. 'DataSheet.Range(1, 1).Select 'Method "Range" of object failed 'DataSheet.Cells(1, 1).Select 'Select method of range class failed. 'Set DataRange = Range(1, 1) 'Select method of range class failed. 'Cells.Select 'Select method of range class failed. 'DataSheet.Cells.Select 'Select method of range class failed. 'Set DataRange = Range(A1, A99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(A1, P99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(1, 99) 'Select method of range class failed. End Sub -- Lamont Phemister |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cells in Excel accessed from VB6
I think you will do better to ask your question in the Access forum.
-- Best wishes, Jim "Lamont" wrote: Is it possible select a range of cells in an Excel spreadsheet under access from a VB6 program? The many permutations I have tried shown below fail. Surely I should be able to Select a range of cells, because I have to do this to sort the rows. Thanks for kind assistance. Lamont Private DataRange As Excel.Range Private DataSheet As New Excel.Worksheet Private MyWorkbook As New Excel.Workbook Private Sub Command1_Click() Dim strMyXLSBkSpec As String Form1.Show strMyXLSInSpec = "C:\DocData.xls" 'My spreadsheet containing data about paper documents Set MyWorkbook = GetObject(strMyXLSInSpec) 'This works to access my Excel File MyWorkbook.Activate 'this works Set DataSheet = MyWorkbook.Sheets("DataSheet") 'this works Set DataRange = DataSheet.UsedRange 'this works Debug.Print DataRange.Rows.Count 'this works DataSheet.Activate 'this works 'DataRange.Select 'Select method of range class failed. 'DataSheet.Range(1, 1).Select 'Method "Range" of object failed 'DataSheet.Cells(1, 1).Select 'Select method of range class failed. 'Set DataRange = Range(1, 1) 'Select method of range class failed. 'Cells.Select 'Select method of range class failed. 'DataSheet.Cells.Select 'Select method of range class failed. 'Set DataRange = Range(A1, A99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(A1, P99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(1, 99) 'Select method of range class failed. End Sub -- Lamont Phemister |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cells in Excel accessed from VB6
Thanks for kindness to reply. However, I did not mean Microsoft Access.
Possibly the term to describe manipulation of an Excel spreadsheet from Visual Basic would be "automation". -- Lamont Phemister "Lamont" wrote: Is it possible select a range of cells in an Excel spreadsheet under access from a VB6 program? The many permutations I have tried shown below fail. Surely I should be able to Select a range of cells, because I have to do this to sort the rows. Thanks for kind assistance. Lamont Private DataRange As Excel.Range Private DataSheet As New Excel.Worksheet Private MyWorkbook As New Excel.Workbook Private Sub Command1_Click() Dim strMyXLSBkSpec As String Form1.Show strMyXLSInSpec = "C:\DocData.xls" 'My spreadsheet containing data about paper documents Set MyWorkbook = GetObject(strMyXLSInSpec) 'This works to access my Excel File MyWorkbook.Activate 'this works Set DataSheet = MyWorkbook.Sheets("DataSheet") 'this works Set DataRange = DataSheet.UsedRange 'this works Debug.Print DataRange.Rows.Count 'this works DataSheet.Activate 'this works 'DataRange.Select 'Select method of range class failed. 'DataSheet.Range(1, 1).Select 'Method "Range" of object failed 'DataSheet.Cells(1, 1).Select 'Select method of range class failed. 'Set DataRange = Range(1, 1) 'Select method of range class failed. 'Cells.Select 'Select method of range class failed. 'DataSheet.Cells.Select 'Select method of range class failed. 'Set DataRange = Range(A1, A99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(A1, P99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(1, 99) 'Select method of range class failed. End Sub -- Lamont Phemister |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cells in Excel manipulated from VB
It struck me that perhaps the "Select" command applies only to a mouse
operation by a user looking at the spreadsheet in Excel. So, since a range object has a sort method, I tried Myrange.sort, where presumeably the range implicitly "selects" the desired cells. This gets the error message "Method 'Range' of object '_Global' failed. So, my problem is not only inability to select, bu to sort when manipulating and Excel spreadhseet by code from VB6. -- Lamont Phemister "Lamont" wrote: Is it possible select a range of cells in an Excel spreadsheet under access from a VB6 program? The many permutations I have tried shown below fail. Surely I should be able to Select a range of cells, because I have to do this to sort the rows. Thanks for kind assistance. Lamont Private DataRange As Excel.Range Private DataSheet As New Excel.Worksheet Private MyWorkbook As New Excel.Workbook Private Sub Command1_Click() Dim strMyXLSBkSpec As String Form1.Show strMyXLSInSpec = "C:\DocData.xls" 'My spreadsheet containing data about paper documents Set MyWorkbook = GetObject(strMyXLSInSpec) 'This works to access my Excel File MyWorkbook.Activate 'this works Set DataSheet = MyWorkbook.Sheets("DataSheet") 'this works Set DataRange = DataSheet.UsedRange 'this works Debug.Print DataRange.Rows.Count 'this works DataSheet.Activate 'this works 'DataRange.Select 'Select method of range class failed. 'DataSheet.Range(1, 1).Select 'Method "Range" of object failed 'DataSheet.Cells(1, 1).Select 'Select method of range class failed. 'Set DataRange = Range(1, 1) 'Select method of range class failed. 'Cells.Select 'Select method of range class failed. 'DataSheet.Cells.Select 'Select method of range class failed. 'Set DataRange = Range(A1, A99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(A1, P99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(1, 99) 'Select method of range class failed. End Sub -- Lamont Phemister |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cells in Excel manipulated from VB
Lamont,
First, let me apologize for not understanding fully what you were asking. Sometimes we get so involved with Excel and Access that we forget that "access" has other meanings beside the database. Does your UserForm have fields for specifying the Data Range? I do not see anything in the code that specifies the desired Range. If the user needs to specify the Range a couple of input boxes could be used for that (the first asking for the beginning cell and the second asking for the ending cell. Let me know if any of this makes sense. -- Best wishes, Jim "Lamont" wrote: It struck me that perhaps the "Select" command applies only to a mouse operation by a user looking at the spreadsheet in Excel. So, since a range object has a sort method, I tried Myrange.sort, where presumeably the range implicitly "selects" the desired cells. This gets the error message "Method 'Range' of object '_Global' failed. So, my problem is not only inability to select, bu to sort when manipulating and Excel spreadhseet by code from VB6. -- Lamont Phemister "Lamont" wrote: Is it possible select a range of cells in an Excel spreadsheet under access from a VB6 program? The many permutations I have tried shown below fail. Surely I should be able to Select a range of cells, because I have to do this to sort the rows. Thanks for kind assistance. Lamont Private DataRange As Excel.Range Private DataSheet As New Excel.Worksheet Private MyWorkbook As New Excel.Workbook Private Sub Command1_Click() Dim strMyXLSBkSpec As String Form1.Show strMyXLSInSpec = "C:\DocData.xls" 'My spreadsheet containing data about paper documents Set MyWorkbook = GetObject(strMyXLSInSpec) 'This works to access my Excel File MyWorkbook.Activate 'this works Set DataSheet = MyWorkbook.Sheets("DataSheet") 'this works Set DataRange = DataSheet.UsedRange 'this works Debug.Print DataRange.Rows.Count 'this works DataSheet.Activate 'this works 'DataRange.Select 'Select method of range class failed. 'DataSheet.Range(1, 1).Select 'Method "Range" of object failed 'DataSheet.Cells(1, 1).Select 'Select method of range class failed. 'Set DataRange = Range(1, 1) 'Select method of range class failed. 'Cells.Select 'Select method of range class failed. 'DataSheet.Cells.Select 'Select method of range class failed. 'Set DataRange = Range(A1, A99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(A1, P99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(1, 99) 'Select method of range class failed. End Sub -- Lamont Phemister |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cells in Excel manipulated from VB
Thanks for your continued supprt.
I believe the range is specified by the coordinates in the parentheses. DataSheet.Range(1, 1).Select Set DataRange = Range(A1, A99) The specification of the range should be with program code to ensure consistent application in each run. -- Lamont Phemister "Jim Jackson" wrote: Lamont, First, let me apologize for not understanding fully what you were asking. Sometimes we get so involved with Excel and Access that we forget that "access" has other meanings beside the database. Does your UserForm have fields for specifying the Data Range? I do not see anything in the code that specifies the desired Range. If the user needs to specify the Range a couple of input boxes could be used for that (the first asking for the beginning cell and the second asking for the ending cell. Let me know if any of this makes sense. -- Best wishes, Jim "Lamont" wrote: It struck me that perhaps the "Select" command applies only to a mouse operation by a user looking at the spreadsheet in Excel. So, since a range object has a sort method, I tried Myrange.sort, where presumeably the range implicitly "selects" the desired cells. This gets the error message "Method 'Range' of object '_Global' failed. So, my problem is not only inability to select, bu to sort when manipulating and Excel spreadhseet by code from VB6. -- Lamont Phemister "Lamont" wrote: Is it possible select a range of cells in an Excel spreadsheet under access from a VB6 program? The many permutations I have tried shown below fail. Surely I should be able to Select a range of cells, because I have to do this to sort the rows. Thanks for kind assistance. Lamont Private DataRange As Excel.Range Private DataSheet As New Excel.Worksheet Private MyWorkbook As New Excel.Workbook Private Sub Command1_Click() Dim strMyXLSBkSpec As String Form1.Show strMyXLSInSpec = "C:\DocData.xls" 'My spreadsheet containing data about paper documents Set MyWorkbook = GetObject(strMyXLSInSpec) 'This works to access my Excel File MyWorkbook.Activate 'this works Set DataSheet = MyWorkbook.Sheets("DataSheet") 'this works Set DataRange = DataSheet.UsedRange 'this works Debug.Print DataRange.Rows.Count 'this works DataSheet.Activate 'this works 'DataRange.Select 'Select method of range class failed. 'DataSheet.Range(1, 1).Select 'Method "Range" of object failed 'DataSheet.Cells(1, 1).Select 'Select method of range class failed. 'Set DataRange = Range(1, 1) 'Select method of range class failed. 'Cells.Select 'Select method of range class failed. 'DataSheet.Cells.Select 'Select method of range class failed. 'Set DataRange = Range(A1, A99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(A1, P99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(1, 99) 'Select method of range class failed. End Sub -- Lamont Phemister |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cells in Excel accessed from VB6
Lamont,
Is you instance of Excel visible ? You cannot .Select something you cannot see. That said, it is seldom necessary to select objects before you use them. Also do not use unqualified objects, remember you are in VB6, not Excel/VBA, so: Set DataRange = Range(A1, A99) will not work, as to VB "Range" means nothings. What you need is: Set DataRange = DataSheet.Range(A1, A99) Also avoid using declarations like: Private DataSheet As New Excel.Worksheet as this code will never exit: If DataSheet Is Nothing Then Exit Sub Better: Private DataSheet As Excel.Worksheet Record a macro in Excel to see the correct syntax for specifying a range. NickHK "Lamont" wrote in message ... Is it possible select a range of cells in an Excel spreadsheet under access from a VB6 program? The many permutations I have tried shown below fail. Surely I should be able to Select a range of cells, because I have to do this to sort the rows. Thanks for kind assistance. Lamont Private DataRange As Excel.Range Private DataSheet As New Excel.Worksheet Private MyWorkbook As New Excel.Workbook Private Sub Command1_Click() Dim strMyXLSBkSpec As String Form1.Show strMyXLSInSpec = "C:\DocData.xls" 'My spreadsheet containing data about paper documents Set MyWorkbook = GetObject(strMyXLSInSpec) 'This works to access my Excel File MyWorkbook.Activate 'this works Set DataSheet = MyWorkbook.Sheets("DataSheet") 'this works Set DataRange = DataSheet.UsedRange 'this works Debug.Print DataRange.Rows.Count 'this works DataSheet.Activate 'this works 'DataRange.Select 'Select method of range class failed. 'DataSheet.Range(1, 1).Select 'Method "Range" of object failed 'DataSheet.Cells(1, 1).Select 'Select method of range class failed. 'Set DataRange = Range(1, 1) 'Select method of range class failed. 'Cells.Select 'Select method of range class failed. 'DataSheet.Cells.Select 'Select method of range class failed. 'Set DataRange = Range(A1, A99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(A1, P99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(1, 99) 'Select method of range class failed. End Sub -- Lamont Phemister |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cells in Excel accessed from VB6
Hello Nick, Thank you for kindness to reply.
about: Is your instance of Excel visible ? The instance of Excel is not visible. A couple of years ago when I operated on Excel spreadsheets with VB6, the Excel instance did appear in the toolbar and persisted after the program run. I just tried my old programs and it does not do it anymore. I don't know if if was some other program now discarded that caused the instance to appear, or not. Don't understand what should make it appear or not. about: it is seldom necessary to select objects before you use them. I am trying to sort the rows in the spreadsheet. The Excel macro for sort uses the Select statement to specify which rows to sort. However, I just found another way to sort in the MSDN library at MSDN Home MSDN Library\Devel Tools and Languages\Visual Studio\Vis Studio Tools for Office\ Reference\Mictosoft.Office.TOols.Excel\NamedRange Class The example code from this reference is inserted after the =================== line at end of message. Am loading the car to go out of town for a couple weeks just now, so will try that means of sort when I get back. Thank you for very your other very helpful instructions. Are you Microsoft staff? In Redmond? Best regards, Lamont ================================================== ======================= Syntax Visual Basic (Declaration)Public Function Sort ( _ <OptionalAttribute Key1 As Object, _ <OptionalAttribute Optional Order1 As XlSortOrder = XlSortOrder.xlAscending, _ <OptionalAttribute Key2 As Object, _ <OptionalAttribute Type As Object, _ <OptionalAttribute Optional Order2 As XlSortOrder = XlSortOrder.xlAscending, _ <OptionalAttribute Key3 As Object, _ <OptionalAttribute Optional Order3 As XlSortOrder = XlSortOrder.xlAscending, _ <OptionalAttribute Optional Header As XlYesNoGuess = XlYesNoGuess.xlNo, _ <OptionalAttribute OrderCustom As Object, _ <OptionalAttribute MatchCase As Object, _ <OptionalAttribute Optional Orientation As XlSortOrientation = XlSortOrientation.xlSortRows, _ <OptionalAttribute Optional SortMethod As XlSortMethod = XlSortMethod.xlPinYin, _ <OptionalAttribute Optional DataOption1 As XlSortDataOption = XlSortDataOption.xlSortNormal, _ <OptionalAttribute Optional DataOption2 As XlSortDataOption = XlSortDataOption.xlSortNormal, _ <OptionalAttribute Optional DataOption3 As XlSortDataOption = XlSortDataOption.xlSortNormal _ ) As Object Visual Basic (Usage)Dim instance As NamedRange Dim Key1 As Object Dim Order1 As XlSortOrder Dim Key2 As Object Dim Type As Object Dim Order2 As XlSortOrder Dim Key3 As Object Dim Order3 As XlSortOrder Dim Header As XlYesNoGuess Dim OrderCustom As Object Dim MatchCase As Object Dim Orientation As XlSortOrientation Dim SortMethod As XlSortMethod Dim DataOption1 As XlSortDataOption Dim DataOption2 As XlSortDataOption Dim DataOption3 As XlSortDataOption Dim returnValue As Object returnValue = instance.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3) ================= To sort data in a NamedRange control Call the Sort method of the NamedRange control. Visual Basic Copy CodeMe.Fruits.Sort( _ Key1:=Me.Fruits.Columns(1), Order1:=Excel.XlSortOrder.xlAscending, _ Key2:=Me.Fruits.Columns(2), Order2:=Excel.XlSortOrder.xlAscending, _ Orientation:=Excel.XlSortOrientation.xlSortColumns , _ Header:=Excel.XlYesNoGuess.xlNo, _ SortMethod:=Excel.XlSortMethod.xlPinYin, _ DataOption1:=Excel.XlSortDataOption.xlSortNormal, _ DataOption2:=Excel.XlSortDataOption.xlSortNormal, _ DataOption3:=Excel.XlSortDataOption.xlSortNormal) -- Lamont Phemister "NickHK" wrote: Lamont, Is you instance of Excel visible ? You cannot .Select something you cannot see. That said, it is seldom necessary to select objects before you use them. Also do not use unqualified objects, remember you are in VB6, not Excel/VBA, so: Set DataRange = Range(A1, A99) will not work, as to VB "Range" means nothings. What you need is: Set DataRange = DataSheet.Range(A1, A99) Also avoid using declarations like: Private DataSheet As New Excel.Worksheet as this code will never exit: If DataSheet Is Nothing Then Exit Sub Better: Private DataSheet As Excel.Worksheet Record a macro in Excel to see the correct syntax for specifying a range. NickHK "Lamont" wrote in message ... Is it possible select a range of cells in an Excel spreadsheet under access from a VB6 program? The many permutations I have tried shown below fail. Surely I should be able to Select a range of cells, because I have to do this to sort the rows. Thanks for kind assistance. Lamont Private DataRange As Excel.Range Private DataSheet As New Excel.Worksheet Private MyWorkbook As New Excel.Workbook Private Sub Command1_Click() Dim strMyXLSBkSpec As String Form1.Show strMyXLSInSpec = "C:\DocData.xls" 'My spreadsheet containing data about paper documents Set MyWorkbook = GetObject(strMyXLSInSpec) 'This works to access my Excel File MyWorkbook.Activate 'this works Set DataSheet = MyWorkbook.Sheets("DataSheet") 'this works Set DataRange = DataSheet.UsedRange 'this works Debug.Print DataRange.Rows.Count 'this works DataSheet.Activate 'this works 'DataRange.Select 'Select method of range class failed. 'DataSheet.Range(1, 1).Select 'Method "Range" of object failed 'DataSheet.Cells(1, 1).Select 'Select method of range class failed. 'Set DataRange = Range(1, 1) 'Select method of range class failed. 'Cells.Select 'Select method of range class failed. 'DataSheet.Cells.Select 'Select method of range class failed. 'Set DataRange = Range(A1, A99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(A1, P99) 'Select method of range class failed. 'Set DataRange = DataSheet.Range(1, 99) 'Select method of range class failed. End Sub -- Lamont Phemister |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Up down arrow keys do not select cells if select locked cells unch | Excel Discussion (Misc queries) | |||
Order Cells Accessed | Excel Discussion (Misc queries) | |||
How do I count how many times an Excel workbook has been accessed | Excel Discussion (Misc queries) | |||
Excel files are 'read only' when accessed over network. | Excel Discussion (Misc queries) | |||
Excel Macro Fails Accessed Through IE Browser | Excel Programming |