cells() and counting number or rows on spreadsheet

[HeatherO]

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

 

[Bob Phillips]

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]

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]

If you are going to declare a variable of type Excel.Application, you have
to set a reference to that type library (Tools>References).. 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]

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

 

[Bob Phillips]

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]

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]

Hi Heather,

If we minded, we wouldn't bother ;-)

So keep asking

Regards

Bob