vbscript and Excel

[HSalim[MVP]]

Hi
I need to work with an Excel file using vbscript.

Here is a code snippet:
Set XL = CreateObject("Excel.Application")
XL.Application.Workbooks.Open "c:\temp\Myfile.xls"
XL.Visible = True

'rename sheet etc
XL.ActiveSheet.Name = "PayrollInfo"
XL.Cells.UnMerge

'Add new column headings.. Current data in cols a-e
XL.Range("F1:I1") = Array("TrxDate", "Co", "LineNum", "Acct")

'get the last row of data


With XL
Set rng =.Range("A1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)

This line fails with error 1004 - Application or object defined error


What am I doing wrong? Thanks in advance for your advice.

REgards
Habib

 

[Jim Cone]

Change...
.End(xlUp).Row)
To...

.End(.xlUp).Row) ' dot added
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"HSalim[MVP]" <[email protected]>
wrote in message
Hi
I need to work with an Excel file using vbscript.

Here is a code snippet:
Set XL = CreateObject("Excel.Application")
XL.Application.Workbooks.Open "c:\temp\Myfile.xls"
XL.Visible = True
'rename sheet etc
XL.ActiveSheet.Name = "PayrollInfo"
XL.Cells.UnMerge
'Add new column headings.. Current data in cols a-e
XL.Range("F1:I1") = Array("TrxDate", "Co", "LineNum", "Acct")
'get the last row of data

With XL
Set rng =.Range("A1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)

This line fails with error 1004 - Application or object defined error


What am I doing wrong? Thanks in advance for your advice.

REgards
Habib

 

[Jim Rech]

I didn't run your code but try:

With XL.ActiveSheet

--
Jim
| Hi
| I need to work with an Excel file using vbscript.
|
| Here is a code snippet:
| Set XL = CreateObject("Excel.Application")
| XL.Application.Workbooks.Open "c:\temp\Myfile.xls"
| XL.Visible = True
|
| 'rename sheet etc
| XL.ActiveSheet.Name = "PayrollInfo"
| XL.Cells.UnMerge
|
| 'Add new column headings.. Current data in cols a-e
| XL.Range("F1:I1") = Array("TrxDate", "Co", "LineNum", "Acct")
|
| 'get the last row of data
|
|
| With XL
| Set rng =.Range("A1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)
|
| This line fails with error 1004 - Application or object defined error
|
|
| What am I doing wrong? Thanks in advance for your advice.
|
| REgards
| Habib
|
| --
| www.DynExtra.com
| A resource for the Microsoft Dynamics Community
| Featuring FAQs, File Exchange and more
| Current member count: 40
| --------------------------------------------
| Share your knowledge. Add your favorite questions and answers
| Help add questions to this site! We want Your input.
| --------------------------------------------
|
|

 

[Dave Peterson]

If this isn't part of excel's VBA, then your script doesn't know what xlUp is.

I opened excel, went to the VBE, hit ctrl-g to see the immediate window and
typed this:

?xlup

and got this:
-4162

So you could try:

Set rng =.Range("A1:F" & .Cells(.Rows.Count, "A").End(-4162).Row)

 

[Jim Rech]

.xlUp

xlUp is just a constant, Jim.

--
Jim
| Change...
| .End(xlUp).Row)
| To...
|
| .End(.xlUp).Row) ' dot added
| --
| Jim Cone
| San Francisco, USA
| http://www.realezsites.com/bus/primitivesoftware
|
|
| "HSalim[MVP]" <[email protected]>
| wrote in message
| Hi
| I need to work with an Excel file using vbscript.
|
| Here is a code snippet:
| Set XL = CreateObject("Excel.Application")
| XL.Application.Workbooks.Open "c:\temp\Myfile.xls"
| XL.Visible = True
| 'rename sheet etc
| XL.ActiveSheet.Name = "PayrollInfo"
| XL.Cells.UnMerge
| 'Add new column headings.. Current data in cols a-e
| XL.Range("F1:I1") = Array("TrxDate", "Co", "LineNum", "Acct")
| 'get the last row of data
|
| With XL
| Set rng =.Range("A1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)
|
| This line fails with error 1004 - Application or object defined error
|
|
| What am I doing wrong? Thanks in advance for your advice.
|
| REgards
| Habib
|
| --
| www.DynExtra.com
| A resource for the Microsoft Dynamics Community
| Featuring FAQs, File Exchange and more
| Current member count: 40
| --------------------------------------------
| Share your knowledge. Add your favorite questions and answers
| Help add questions to this site! We want Your input.
| --------------------------------------------
|
|

 

[Jim Cone]

But the application the code is being run from doesn't know that.

 

[HSalim[MVP]]

Perfect! that did it. Thank you.
Now, why didn't I think of that?
HS


--
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 40

 

[Bob Phillips]

With XL
Set rng = .Range("A1:F" & .Cells(.Rows.Count, "A").End(-4162).Row)
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Cone]

My apologies, my suggestion doesn't even come close to working.
Jim Cone



xlUp is just a constant, Jim.

--