Home |
Search |
Today's Posts |
#1
|
|||
|
|||
cells() and counting number or rows on spreadsheet
I was just curious I have done this in excel with a macro I created and it
works just fine so now I am in word and trying to create this macro which is supposed to open excel and then I am doing some excel programming in it I look for the last row using this lstrow = cells(rows.count, "A").End(XlUp).row and it tells me it doesn't understand the cells(). Is this because it's in a macro that I am creating in Word??Am I wrong in assuming that with the VB editor it shouldn't matter what application you are in?? Any help or suggestions is appreciated. Thanks, Heather |
#2
|
|||
|
|||
Hi Heather,
Yes it is, Cells is an Excel application property. What you need to do is pre-pend with the application object you have created, l;ike xlApp.cells(rows.count, "A").End(XlUp).row -- HTH RP (remove nothere from the email address if mailing direct) "HeatherO" wrote in message ... I was just curious I have done this in excel with a macro I created and it works just fine so now I am in word and trying to create this macro which is supposed to open excel and then I am doing some excel programming in it I look for the last row using this lstrow = cells(rows.count, "A").End(XlUp).row and it tells me it doesn't understand the cells(). Is this because it's in a macro that I am creating in Word??Am I wrong in assuming that with the VB editor it shouldn't matter what application you are in?? Any help or suggestions is appreciated. Thanks, Heather |
#3
|
|||
|
|||
Hi Bob,
Thanks, I think then that I have to initialize the xlApp then to be an excel application and use it to create the object. I found an article in the microsoft web site that talks about control excel from word but when I tried to create the dim argument of oXL as an Excel.Application it gives me a type mismatch so I don't know if this is maybe old code or if it's changed so there is no such thing as an excel.application. Take a look at the article because I think it is starting to confuse me. Control Excel from word Article contributed by Bill Coan FPRIVATE "TYPE=PICT;ALT=Bill's web site" and Dave Rado Here's some code which uses Early Binding. It checks to see if Excel is running. If it is, the code uses the existing instance of Excel. If not, the code creates an instance of Excel. You can get the syntax for most things you might want to do within Excel with the aid of Excel's macro recorder. First set a reference to Excel (in the VB Editor, select Tools + References). Sub WorkOnAWorkbook() Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As Excel.Range Dim ExcelWasNotRunning As Boolean Dim WorkbookToWorkOn As String 'specify the workbook to work on WorkbookToWorkOn = "C:\My Documents\myworkbook.xls" 'If Excel is running, get a handle on it; otherwise start a new instance of Excel On Error Resume Next Set oXL = GetObject(, "Excel.Application") If Err Then ExcelWasNotRunning = True Set oXL = New Excel.Application End If On Error GoTo Err_Handler 'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible 'Open the workbook Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn) 'Process each of the spreadsheets in the workbook For Each oSheet In oXL.ActiveWorkbook.Worksheets 'put guts of your code here 'get next sheet Next oSheet If ExcelWasNotRunning Then oXL.Quit End If 'Make sure you release object references. Set oRng = Nothing Set oSheet = Nothing Set oWB = Nothing Set oXL = Nothing 'quit Exit Sub Err_Handler: MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _ "Error: " & Err.Number If ExcelWasNotRunning Then oXL.Quit End If End Sub Thanks also for all your help, you are very knowledgable. Heather "Bob Phillips" wrote: Hi Heather, Yes it is, Cells is an Excel application property. What you need to do is pre-pend with the application object you have created, l;ike xlApp.cells(rows.count, "A").End(XlUp).row -- HTH RP (remove nothere from the email address if mailing direct) "HeatherO" wrote in message ... I was just curious I have done this in excel with a macro I created and it works just fine so now I am in word and trying to create this macro which is supposed to open excel and then I am doing some excel programming in it I look for the last row using this lstrow = cells(rows.count, "A").End(XlUp).row and it tells me it doesn't understand the cells(). Is this because it's in a macro that I am creating in Word??Am I wrong in assuming that with the VB editor it shouldn't matter what application you are in?? Any help or suggestions is appreciated. Thanks, Heather |
#4
|
|||
|
|||
If you are going to declare a variable of type Excel.Application, you have
to set a reference to that type library (ToolsReferences).. You can avoid this by using late binding, defining the type as Object, but you lose intellisense if you do this, and cannot use any Excel constants. -- HTH RP (remove nothere from the email address if mailing direct) "HeatherO" wrote in message ... Hi Bob, Thanks, I think then that I have to initialize the xlApp then to be an excel application and use it to create the object. I found an article in the microsoft web site that talks about control excel from word but when I tried to create the dim argument of oXL as an Excel.Application it gives me a type mismatch so I don't know if this is maybe old code or if it's changed so there is no such thing as an excel.application. Take a look at the article because I think it is starting to confuse me. Control Excel from word Article contributed by Bill Coan FPRIVATE "TYPE=PICT;ALT=Bill's web site" and Dave Rado Here's some code which uses Early Binding. It checks to see if Excel is running. If it is, the code uses the existing instance of Excel. If not, the code creates an instance of Excel. You can get the syntax for most things you might want to do within Excel with the aid of Excel's macro recorder. First set a reference to Excel (in the VB Editor, select Tools + References). Sub WorkOnAWorkbook() Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As Excel.Range Dim ExcelWasNotRunning As Boolean Dim WorkbookToWorkOn As String 'specify the workbook to work on WorkbookToWorkOn = "C:\My Documents\myworkbook.xls" 'If Excel is running, get a handle on it; otherwise start a new instance of Excel On Error Resume Next Set oXL = GetObject(, "Excel.Application") If Err Then ExcelWasNotRunning = True Set oXL = New Excel.Application End If On Error GoTo Err_Handler 'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible 'Open the workbook Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn) 'Process each of the spreadsheets in the workbook For Each oSheet In oXL.ActiveWorkbook.Worksheets 'put guts of your code here 'get next sheet Next oSheet If ExcelWasNotRunning Then oXL.Quit End If 'Make sure you release object references. Set oRng = Nothing Set oSheet = Nothing Set oWB = Nothing Set oXL = Nothing 'quit Exit Sub Err_Handler: MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _ "Error: " & Err.Number If ExcelWasNotRunning Then oXL.Quit End If End Sub Thanks also for all your help, you are very knowledgable. Heather "Bob Phillips" wrote: Hi Heather, Yes it is, Cells is an Excel application property. What you need to do is pre-pend with the application object you have created, l;ike xlApp.cells(rows.count, "A").End(XlUp).row -- HTH RP (remove nothere from the email address if mailing direct) "HeatherO" wrote in message ... I was just curious I have done this in excel with a macro I created and it works just fine so now I am in word and trying to create this macro which is supposed to open excel and then I am doing some excel programming in it I look for the last row using this lstrow = cells(rows.count, "A").End(XlUp).row and it tells me it doesn't understand the cells(). Is this because it's in a macro that I am creating in Word??Am I wrong in assuming that with the VB editor it shouldn't matter what application you are in?? Any help or suggestions is appreciated. Thanks, Heather |
#5
|
|||
|
|||
Thanks Bob that does work with the reference to, sorry but you can tell I am
new to this microsoft programming. I've programmed in RPG and progress before but this is amazing all the stuff it can do there is so much to learn. I took this project on to get familiar with it. Thanks for your help. By the way if I package this and give it to someone else would they have to set the references on their computer too or is that just linked through the document now? Sorry just trying to understand how that all works. Thanks again for all your help. I think I'll owe you a meal by the time I'm finished this. Heather "HeatherO" wrote: Hi Bob, Thanks, I think then that I have to initialize the xlApp then to be an excel application and use it to create the object. I found an article in the microsoft web site that talks about control excel from word but when I tried to create the dim argument of oXL as an Excel.Application it gives me a type mismatch so I don't know if this is maybe old code or if it's changed so there is no such thing as an excel.application. Take a look at the article because I think it is starting to confuse me. Control Excel from word Article contributed by Bill Coan FPRIVATE "TYPE=PICT;ALT=Bill's web site" and Dave Rado Here's some code which uses Early Binding. It checks to see if Excel is running. If it is, the code uses the existing instance of Excel. If not, the code creates an instance of Excel. You can get the syntax for most things you might want to do within Excel with the aid of Excel's macro recorder. First set a reference to Excel (in the VB Editor, select Tools + References). Sub WorkOnAWorkbook() Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As Excel.Range Dim ExcelWasNotRunning As Boolean Dim WorkbookToWorkOn As String 'specify the workbook to work on WorkbookToWorkOn = "C:\My Documents\myworkbook.xls" 'If Excel is running, get a handle on it; otherwise start a new instance of Excel On Error Resume Next Set oXL = GetObject(, "Excel.Application") If Err Then ExcelWasNotRunning = True Set oXL = New Excel.Application End If On Error GoTo Err_Handler 'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible 'Open the workbook Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn) 'Process each of the spreadsheets in the workbook For Each oSheet In oXL.ActiveWorkbook.Worksheets 'put guts of your code here 'get next sheet Next oSheet If ExcelWasNotRunning Then oXL.Quit End If 'Make sure you release object references. Set oRng = Nothing Set oSheet = Nothing Set oWB = Nothing Set oXL = Nothing 'quit Exit Sub Err_Handler: MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _ "Error: " & Err.Number If ExcelWasNotRunning Then oXL.Quit End If End Sub Thanks also for all your help, you are very knowledgable. Heather "Bob Phillips" wrote: Hi Heather, Yes it is, Cells is an Excel application property. What you need to do is pre-pend with the application object you have created, l;ike xlApp.cells(rows.count, "A").End(XlUp).row -- HTH RP (remove nothere from the email address if mailing direct) "HeatherO" wrote in message ... I was just curious I have done this in excel with a macro I created and it works just fine so now I am in word and trying to create this macro which is supposed to open excel and then I am doing some excel programming in it I look for the last row using this lstrow = cells(rows.count, "A").End(XlUp).row and it tells me it doesn't understand the cells(). Is this because it's in a macro that I am creating in Word??Am I wrong in assuming that with the VB editor it shouldn't matter what application you are in?? Any help or suggestions is appreciated. Thanks, Heather |
#6
|
|||
|
|||
Heather,
That depends. If they have the same version of Excel as you, then all should be okay, the references will stay with the doc. If they have an earlier version you could find that the references become invalid, and they will have to reset them. This is where late binding becomes useful (which I mentioned last time), but the best way to avoid problems is to develop on the earliest version of Excel that you will deploy to. -- HTH RP (remove nothere from the email address if mailing direct) "HeatherO" wrote in message ... Thanks Bob that does work with the reference to, sorry but you can tell I am new to this microsoft programming. I've programmed in RPG and progress before but this is amazing all the stuff it can do there is so much to learn. I took this project on to get familiar with it. Thanks for your help. By the way if I package this and give it to someone else would they have to set the references on their computer too or is that just linked through the document now? Sorry just trying to understand how that all works. Thanks again for all your help. I think I'll owe you a meal by the time I'm finished this. Heather "HeatherO" wrote: Hi Bob, Thanks, I think then that I have to initialize the xlApp then to be an excel application and use it to create the object. I found an article in the microsoft web site that talks about control excel from word but when I tried to create the dim argument of oXL as an Excel.Application it gives me a type mismatch so I don't know if this is maybe old code or if it's changed so there is no such thing as an excel.application. Take a look at the article because I think it is starting to confuse me. Control Excel from word Article contributed by Bill Coan FPRIVATE "TYPE=PICT;ALT=Bill's web site" and Dave Rado Here's some code which uses Early Binding. It checks to see if Excel is running. If it is, the code uses the existing instance of Excel. If not, the code creates an instance of Excel. You can get the syntax for most things you might want to do within Excel with the aid of Excel's macro recorder. First set a reference to Excel (in the VB Editor, select Tools + References). Sub WorkOnAWorkbook() Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As Excel.Range Dim ExcelWasNotRunning As Boolean Dim WorkbookToWorkOn As String 'specify the workbook to work on WorkbookToWorkOn = "C:\My Documents\myworkbook.xls" 'If Excel is running, get a handle on it; otherwise start a new instance of Excel On Error Resume Next Set oXL = GetObject(, "Excel.Application") If Err Then ExcelWasNotRunning = True Set oXL = New Excel.Application End If On Error GoTo Err_Handler 'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible 'Open the workbook Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn) 'Process each of the spreadsheets in the workbook For Each oSheet In oXL.ActiveWorkbook.Worksheets 'put guts of your code here 'get next sheet Next oSheet If ExcelWasNotRunning Then oXL.Quit End If 'Make sure you release object references. Set oRng = Nothing Set oSheet = Nothing Set oWB = Nothing Set oXL = Nothing 'quit Exit Sub Err_Handler: MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _ "Error: " & Err.Number If ExcelWasNotRunning Then oXL.Quit End If End Sub Thanks also for all your help, you are very knowledgable. Heather "Bob Phillips" wrote: Hi Heather, Yes it is, Cells is an Excel application property. What you need to do is pre-pend with the application object you have created, l;ike xlApp.cells(rows.count, "A").End(XlUp).row -- HTH RP (remove nothere from the email address if mailing direct) "HeatherO" wrote in message ... I was just curious I have done this in excel with a macro I created and it works just fine so now I am in word and trying to create this macro which is supposed to open excel and then I am doing some excel programming in it I look for the last row using this lstrow = cells(rows.count, "A").End(XlUp).row and it tells me it doesn't understand the cells(). Is this because it's in a macro that I am creating in Word??Am I wrong in assuming that with the VB editor it shouldn't matter what application you are in?? Any help or suggestions is appreciated. Thanks, Heather |
#7
|
|||
|
|||
Hi Bob,
Thanks for all your help, I'll have to look into the version they have and see it's something for me to definitely keep in mind. Hope you don't mind all my questions but I'm trying to get this project done and don't have a lot of help which was originally promised to me. Your a lifesaver, thanks again. Heather "Bob Phillips" wrote: Heather, That depends. If they have the same version of Excel as you, then all should be okay, the references will stay with the doc. If they have an earlier version you could find that the references become invalid, and they will have to reset them. This is where late binding becomes useful (which I mentioned last time), but the best way to avoid problems is to develop on the earliest version of Excel that you will deploy to. -- HTH RP (remove nothere from the email address if mailing direct) "HeatherO" wrote in message ... Thanks Bob that does work with the reference to, sorry but you can tell I am new to this microsoft programming. I've programmed in RPG and progress before but this is amazing all the stuff it can do there is so much to learn. I took this project on to get familiar with it. Thanks for your help. By the way if I package this and give it to someone else would they have to set the references on their computer too or is that just linked through the document now? Sorry just trying to understand how that all works. Thanks again for all your help. I think I'll owe you a meal by the time I'm finished this. Heather "HeatherO" wrote: Hi Bob, Thanks, I think then that I have to initialize the xlApp then to be an excel application and use it to create the object. I found an article in the microsoft web site that talks about control excel from word but when I tried to create the dim argument of oXL as an Excel.Application it gives me a type mismatch so I don't know if this is maybe old code or if it's changed so there is no such thing as an excel.application. Take a look at the article because I think it is starting to confuse me. Control Excel from word Article contributed by Bill Coan FPRIVATE "TYPE=PICT;ALT=Bill's web site" and Dave Rado Here's some code which uses Early Binding. It checks to see if Excel is running. If it is, the code uses the existing instance of Excel. If not, the code creates an instance of Excel. You can get the syntax for most things you might want to do within Excel with the aid of Excel's macro recorder. First set a reference to Excel (in the VB Editor, select Tools + References). Sub WorkOnAWorkbook() Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As Excel.Range Dim ExcelWasNotRunning As Boolean Dim WorkbookToWorkOn As String 'specify the workbook to work on WorkbookToWorkOn = "C:\My Documents\myworkbook.xls" 'If Excel is running, get a handle on it; otherwise start a new instance of Excel On Error Resume Next Set oXL = GetObject(, "Excel.Application") If Err Then ExcelWasNotRunning = True Set oXL = New Excel.Application End If On Error GoTo Err_Handler 'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible 'Open the workbook Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn) 'Process each of the spreadsheets in the workbook For Each oSheet In oXL.ActiveWorkbook.Worksheets 'put guts of your code here 'get next sheet Next oSheet If ExcelWasNotRunning Then oXL.Quit End If 'Make sure you release object references. Set oRng = Nothing Set oSheet = Nothing Set oWB = Nothing Set oXL = Nothing 'quit Exit Sub Err_Handler: MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _ "Error: " & Err.Number If ExcelWasNotRunning Then oXL.Quit End If End Sub Thanks also for all your help, you are very knowledgable. Heather "Bob Phillips" wrote: Hi Heather, Yes it is, Cells is an Excel application property. What you need to do is pre-pend with the application object you have created, l;ike xlApp.cells(rows.count, "A").End(XlUp).row -- HTH RP (remove nothere from the email address if mailing direct) "HeatherO" wrote in message ... I was just curious I have done this in excel with a macro I created and it works just fine so now I am in word and trying to create this macro which is supposed to open excel and then I am doing some excel programming in it I look for the last row using this lstrow = cells(rows.count, "A").End(XlUp).row and it tells me it doesn't understand the cells(). Is this because it's in a macro that I am creating in Word??Am I wrong in assuming that with the VB editor it shouldn't matter what application you are in?? Any help or suggestions is appreciated. Thanks, Heather |
#8
|
|||
|
|||
Hi Heather,
If we minded, we wouldn't bother ;-) So keep asking Regards Bob "HeatherO" wrote in message ... Hi Bob, Thanks for all your help, I'll have to look into the version they have and see it's something for me to definitely keep in mind. Hope you don't mind all my questions but I'm trying to get this project done and don't have a lot of help which was originally promised to me. Your a lifesaver, thanks again. Heather "Bob Phillips" wrote: Heather, That depends. If they have the same version of Excel as you, then all should be okay, the references will stay with the doc. If they have an earlier version you could find that the references become invalid, and they will have to reset them. This is where late binding becomes useful (which I mentioned last time), but the best way to avoid problems is to develop on the earliest version of Excel that you will deploy to. -- HTH RP (remove nothere from the email address if mailing direct) "HeatherO" wrote in message ... Thanks Bob that does work with the reference to, sorry but you can tell I am new to this microsoft programming. I've programmed in RPG and progress before but this is amazing all the stuff it can do there is so much to learn. I took this project on to get familiar with it. Thanks for your help. By the way if I package this and give it to someone else would they have to set the references on their computer too or is that just linked through the document now? Sorry just trying to understand how that all works. Thanks again for all your help. I think I'll owe you a meal by the time I'm finished this. Heather "HeatherO" wrote: Hi Bob, Thanks, I think then that I have to initialize the xlApp then to be an excel application and use it to create the object. I found an article in the microsoft web site that talks about control excel from word but when I tried to create the dim argument of oXL as an Excel.Application it gives me a type mismatch so I don't know if this is maybe old code or if it's changed so there is no such thing as an excel.application. Take a look at the article because I think it is starting to confuse me. Control Excel from word Article contributed by Bill Coan FPRIVATE "TYPE=PICT;ALT=Bill's web site" and Dave Rado Here's some code which uses Early Binding. It checks to see if Excel is running. If it is, the code uses the existing instance of Excel. If not, the code creates an instance of Excel. You can get the syntax for most things you might want to do within Excel with the aid of Excel's macro recorder. First set a reference to Excel (in the VB Editor, select Tools + References). Sub WorkOnAWorkbook() Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As Excel.Range Dim ExcelWasNotRunning As Boolean Dim WorkbookToWorkOn As String 'specify the workbook to work on WorkbookToWorkOn = "C:\My Documents\myworkbook.xls" 'If Excel is running, get a handle on it; otherwise start a new instance of Excel On Error Resume Next Set oXL = GetObject(, "Excel.Application") If Err Then ExcelWasNotRunning = True Set oXL = New Excel.Application End If On Error GoTo Err_Handler 'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible 'Open the workbook Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn) 'Process each of the spreadsheets in the workbook For Each oSheet In oXL.ActiveWorkbook.Worksheets 'put guts of your code here 'get next sheet Next oSheet If ExcelWasNotRunning Then oXL.Quit End If 'Make sure you release object references. Set oRng = Nothing Set oSheet = Nothing Set oWB = Nothing Set oXL = Nothing 'quit Exit Sub Err_Handler: MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _ "Error: " & Err.Number If ExcelWasNotRunning Then oXL.Quit End If End Sub Thanks also for all your help, you are very knowledgable. Heather "Bob Phillips" wrote: Hi Heather, Yes it is, Cells is an Excel application property. What you need to do is pre-pend with the application object you have created, l;ike xlApp.cells(rows.count, "A").End(XlUp).row -- HTH RP (remove nothere from the email address if mailing direct) "HeatherO" wrote in message ... I was just curious I have done this in excel with a macro I created and it works just fine so now I am in word and trying to create this macro which is supposed to open excel and then I am doing some excel programming in it I look for the last row using this lstrow = cells(rows.count, "A").End(XlUp).row and it tells me it doesn't understand the cells(). Is this because it's in a macro that I am creating in Word??Am I wrong in assuming that with the VB editor it shouldn't matter what application you are in?? Any help or suggestions is appreciated. Thanks, Heather |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting cells with conditional formatting applied | Excel Discussion (Misc queries) | |||
counting text cells | Excel Worksheet Functions | |||
Count number of shaded cells | Excel Discussion (Misc queries) | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions |