Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbscript and Excel
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. -------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbscript and Excel
Change...
.End(xlUp).Row) To... .End(.xlUp).Row) ' dot added -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "HSalim[MVP]" 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. -------------------------------------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbscript and Excel
I didn't run your code but try:
With XL.ActiveSheet -- Jim "HSalim[MVP]" 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. | -------------------------------------------- | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbscript and Excel
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) "HSalim[MVP]" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbscript and Excel
.xlUp
xlUp is just a constant, Jim. -- Jim "Jim Cone" wrote in message ... | Change... | .End(xlUp).Row) | To... | | .End(.xlUp).Row) ' dot added | -- | Jim Cone | San Francisco, USA | http://www.realezsites.com/bus/primitivesoftware | | | "HSalim[MVP]" | 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. | -------------------------------------------- | | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbscript and Excel
But the application the code is being run from doesn't know that. -- Jim Cone "Jim Rech" wrote in message .xlUp xlUp is just a constant, Jim. -- Jim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbscript and Excel
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 -------------------------------------------- Share your knowledge. Add your favorite questions and answers Help add questions to this site! We want Your input. -------------------------------------------- "Dave Peterson" wrote in message ... 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) "HSalim[MVP]" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbscript and Excel
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) "HSalim[MVP]" 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. -------------------------------------------- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
vbscript and Excel
My apologies, my suggestion doesn't even come close to working. Jim Cone "Jim Rech" wrote in message ... .xlUp xlUp is just a constant, Jim. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run VBScript from Excel | Excel Discussion (Misc queries) | |||
Vbscript Excel question | Excel Programming | |||
Excel & VBScript | Excel Programming | |||
Using excel through vbscript | Excel Programming | |||
VBScript code behind Excel | Excel Programming |