Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.sqlserver.dts,microsoft.public.sqlserver.programming,microsoft.public.excel.programming,microsoft.public.scripting.vbscript
|
|||
|
|||
vb script in dts throws error when operating on Excel file - helpplease!
On Oct 5, 6:50 pm, Tom Lavedas wrote:
On Oct 5, 1:17 pm, Edward wrote: SQL Server 2000 DTS VBScript Excel I have to manipulate an Excel file within a DTS package. Put simply, I need to move the contents of certain cells in one Worksheet into diffferent cells on another worksheet. Here's my code: Function Main Dim e_app Dim e_wbook Dim e_wksheet1 Dim e_wksheet2 Dim e_wksheet3 Dim sFilename sFilename = "\\Server1\Shared\Import\ManagerQuestionnaire. xls" ' Create the Excel Object Set e_app = CreateObject("Excel.Application") ' Open up the Excel Spreadsheet Set e_wbook = e_app.Workbooks.Open(sFilename) ' Which sheet do we get our data from Set e_wksheet1 = e_wbook.Worksheets("Sheet1") Set e_wksheet2 = e_wbook.Worksheets("Sheet2") e_wksheet1.Range("A7").Select e_wksheet1.Copy e_wksheet2.Select <-- BARFS HERE WITH "Select method of worksheet class failed" e_wksheet2.Range("A1").Select <-- If I remove the above line it barfs with "Select method of Range class failed" I've tried setting e_wksheet2 to the e_wbook.Worksheets("Sheet1") (same as e_wksheet1) but it still fails. The code parses correctly. It obviously gets beyond the Select method call on e_wksheet1 so why is it failing on e_wksheet2? Thanks Edward Try directly addressing the ranges that you want to use instead of the very problematic cell Select approach that the macro recorder tends to provide, something like this ... ' Which sheet do we get our data from Set e_wksheet1 = e_wbook.Worksheets("Sheet1") Set e_wksheet2 = e_wbook.Worksheets("Sheet2") e_wksheet2.Range("A1") = e_wksheet1.Range("A7") Hi Tom This approach worked really well - thanks. I also need to copy a range of cells, and this is the approach that I'm trying: e_wksheet3.Range("A1:I3") = e_wksheet1.Range("A19:I22") Although the script runs without error, the target worksheet is blank. However, if I substitute this line with e_wksheet3.Range("A1") = e_wksheet1.Range("A19") the cell at A1 is populated. Any thoughts? Thanks Edward |
#2
Posted to microsoft.public.sqlserver.dts,microsoft.public.sqlserver.programming,microsoft.public.excel.programming,microsoft.public.scripting.vbscript
|
|||
|
|||
vb script in dts throws error when operating on Excel file - helpplease!
On Oct 6, 6:07*am, Edward wrote:
On Oct 5, 6:50 pm, Tom Lavedas wrote: On Oct 5, 1:17 pm, Edward wrote: SQL Server 2000 DTS VBScript Excel I have to manipulate an Excel file within a DTS package. *Put simply, I need to move the contents of certain cells in one Worksheet into diffferent cells on another worksheet. Here's my code: Function Main * * * * Dim e_app * * * * Dim e_wbook * * * * Dim e_wksheet1 * * * * Dim e_wksheet2 * * * * Dim e_wksheet3 * * * * Dim sFilename * * * * sFilename = "\\Server1\Shared\Import\ManagerQuestionnaire. xls" * * * * ' Create the Excel Object * * * * Set e_app = CreateObject("Excel.Application") * * * * ' Open up the Excel Spreadsheet * * * * Set e_wbook = e_app.Workbooks.Open(sFilename) * * * * ' Which sheet do we get our data from * * * * Set e_wksheet1 = e_wbook.Worksheets("Sheet1") * * * * Set e_wksheet2 = e_wbook.Worksheets("Sheet2") * * * * e_wksheet1.Range("A7").Select * * * * e_wksheet1.Copy * * * * e_wksheet2.Select <-- BARFS HERE WITH "Select method of worksheet class failed" * * * * e_wksheet2.Range("A1").Select *<-- If I remove the above line it barfs with "Select method of Range class failed" I've tried setting e_wksheet2 to the e_wbook.Worksheets("Sheet1") (same as e_wksheet1) but it still fails. *The code parses correctly.. It obviously gets beyond the Select method call on e_wksheet1 so why is it failing on e_wksheet2? Thanks Edward Try directly addressing the ranges that you want to use instead of the very problematic cell Select approach that the macro recorder tends to provide, something like this ... * * * * ' Which sheet do we get our data from * * * * Set e_wksheet1 = e_wbook.Worksheets("Sheet1") * * * * Set e_wksheet2 = e_wbook.Worksheets("Sheet2") * * * * *e_wksheet2.Range("A1") = e_wksheet1.Range("A7") Hi Tom This approach worked really well - thanks. I also need to copy a range of cells, and this is the approach that I'm trying: e_wksheet3.Range("A1:I3") = e_wksheet1.Range("A19:I22") Although the script runs without error, the target worksheet is blank. *However, if I substitute this line with e_wksheet3.Range("A1") = e_wksheet1.Range("A19") the cell at A1 is populated. Any thoughts? Thanks Edward There are two ways, at least. One is to loop through the range addressing each cell with the Cells(row,col) property which is a bit complicated. The other is to use the clipboard in a somewhat different manner than your first attempt. That is ... e_wksheet3.Range("A1:I3").Copy ActiveSheet.Paste e_wksheet1.Range("A19") This approach avoids the problem with getting the 'selection' setup correctly and will work for single cells as well. Note, however, that since this performs a copy, any formulas will be copied over (if there are any in the range), not just the values. To paste just the values (if formulas might be present), the following can be used instead ... Const xlPasteValues = -4163 e_wksheet3.Range("A1:I3").Copy e_wksheet1.Range("A19").PasteSpecial xlPasteValues _____________________ Tom Lavedas |
#3
Posted to microsoft.public.sqlserver.dts,microsoft.public.sqlserver.programming,microsoft.public.excel.programming,microsoft.public.scripting.vbscript
|
|||
|
|||
vb script in dts throws error when operating on Excel file - helpplease!
On Oct 6, 1:52*pm, Tom Lavedas wrote:
On Oct 6, 6:07*am, Edward wrote: On Oct 5, 6:50 pm, Tom Lavedas wrote: On Oct 5, 1:17 pm, Edward wrote: SQL Server 2000 DTS VBScript Excel I have to manipulate an Excel file within a DTS package. *Put simply, I need to move the contents of certain cells in one Worksheet into diffferent cells on another worksheet. Here's my code: Function Main * * * * Dim e_app * * * * Dim e_wbook * * * * Dim e_wksheet1 * * * * Dim e_wksheet2 * * * * Dim e_wksheet3 * * * * Dim sFilename * * * * sFilename = "\\Server1\Shared\Import\ManagerQuestionnaire. xls" * * * * ' Create the Excel Object * * * * Set e_app = CreateObject("Excel.Application") * * * * ' Open up the Excel Spreadsheet * * * * Set e_wbook = e_app.Workbooks.Open(sFilename) * * * * ' Which sheet do we get our data from * * * * Set e_wksheet1 = e_wbook.Worksheets("Sheet1") * * * * Set e_wksheet2 = e_wbook.Worksheets("Sheet2") * * * * e_wksheet1.Range("A7").Select * * * * e_wksheet1.Copy * * * * e_wksheet2.Select <-- BARFS HERE WITH "Select method of worksheet class failed" * * * * e_wksheet2.Range("A1").Select *<-- If I remove the above line it barfs with "Select method of Range class failed" I've tried setting e_wksheet2 to the e_wbook.Worksheets("Sheet1") (same as e_wksheet1) but it still fails. *The code parses correctly. It obviously gets beyond the Select method call on e_wksheet1 so why is it failing on e_wksheet2? Thanks Edward Try directly addressing the ranges that you want to use instead of the very problematic cell Select approach that the macro recorder tends to provide, something like this ... * * * * ' Which sheet do we get our data from * * * * Set e_wksheet1 = e_wbook.Worksheets("Sheet1") * * * * Set e_wksheet2 = e_wbook.Worksheets("Sheet2") * * * * *e_wksheet2.Range("A1") = e_wksheet1.Range("A7") Hi Tom This approach worked really well - thanks. I also need to copy a range of cells, and this is the approach that I'm trying: e_wksheet3.Range("A1:I3") = e_wksheet1.Range("A19:I22") Although the script runs without error, the target worksheet is blank. *However, if I substitute this line with e_wksheet3.Range("A1") = e_wksheet1.Range("A19") the cell at A1 is populated. Any thoughts? Thanks Edward There are two ways, at least. *One is to loop through the range addressing each cell with the Cells(row,col) property which is a bit complicated. *The other is to use the clipboard in a somewhat different manner than your first attempt. *That is ... * * e_wksheet3.Range("A1:I3").Copy * * ActiveSheet.Paste e_wksheet1.Range("A19") This approach avoids the problem with getting the 'selection' setup correctly and will work for single cells as well. *Note, however, that since this performs a copy, any formulas will be copied over (if there are any in the range), not just the values. To paste just the values (if formulas might be present), the following can be used instead ... * * Const xlPasteValues = -4163 * * e_wksheet3.Range("A1:I3").Copy * * e_wksheet1.Range("A19").PasteSpecial xlPasteValues _____________________ Wow Tom, that's awesome service! I'm hugely in your debt. This will fox you, though. Let's say that the range of values to be copied (above shown as e_wksheet3.Range("A1:I3")) was unknown - or, to be completely specific, it was known that the columns were A through I, but the number of rows was unknown. I could cludge it by writing Const xlPasteValues = -4163 e_wksheet3.Range("A1:I5000").Copy e_wksheet1.Range("A19").PasteSpecial xlPasteValues but there would be sure to be circumstances where this was not a good idea. Any idea how to find the index of the first empty row and use it in the script above? I'm aware of a constant called xlDown, but not sure how to use it, or even if it's the right one.... Again, many thanks Edward |
#4
Posted to microsoft.public.sqlserver.dts,microsoft.public.sqlserver.programming,microsoft.public.excel.programming,microsoft.public.scripting.vbscript
|
|||
|
|||
vb script in dts throws error when operating on Excel file - helpplease!
On Oct 6, 10:24*am, Edward wrote:
On Oct 6, 1:52*pm, Tom Lavedas wrote: On Oct 6, 6:07*am, Edward wrote: On Oct 5, 6:50 pm, Tom Lavedas wrote: On Oct 5, 1:17 pm, Edward wrote: SQL Server 2000 DTS VBScript Excel I have to manipulate an Excel file within a DTS package. *Put simply, I need to move the contents of certain cells in one Worksheet into diffferent cells on another worksheet. Here's my code: Function Main * * * * Dim e_app * * * * Dim e_wbook * * * * Dim e_wksheet1 * * * * Dim e_wksheet2 * * * * Dim e_wksheet3 * * * * Dim sFilename * * * * sFilename = "\\Server1\Shared\Import\ManagerQuestionnaire. xls" * * * * ' Create the Excel Object * * * * Set e_app = CreateObject("Excel.Application") * * * * ' Open up the Excel Spreadsheet * * * * Set e_wbook = e_app.Workbooks.Open(sFilename) * * * * ' Which sheet do we get our data from * * * * Set e_wksheet1 = e_wbook.Worksheets("Sheet1") * * * * Set e_wksheet2 = e_wbook.Worksheets("Sheet2") * * * * e_wksheet1.Range("A7").Select * * * * e_wksheet1.Copy * * * * e_wksheet2.Select <-- BARFS HERE WITH "Select method of worksheet class failed" * * * * e_wksheet2.Range("A1").Select *<-- If I remove the above line it barfs with "Select method of Range class failed" I've tried setting e_wksheet2 to the e_wbook.Worksheets("Sheet1") (same as e_wksheet1) but it still fails. *The code parses correctly. It obviously gets beyond the Select method call on e_wksheet1 so why is it failing on e_wksheet2? Thanks Edward Try directly addressing the ranges that you want to use instead of the very problematic cell Select approach that the macro recorder tends to provide, something like this ... * * * * ' Which sheet do we get our data from * * * * Set e_wksheet1 = e_wbook.Worksheets("Sheet1") * * * * Set e_wksheet2 = e_wbook.Worksheets("Sheet2") * * * * *e_wksheet2.Range("A1") = e_wksheet1.Range("A7") Hi Tom This approach worked really well - thanks. I also need to copy a range of cells, and this is the approach that I'm trying: e_wksheet3.Range("A1:I3") = e_wksheet1.Range("A19:I22") Although the script runs without error, the target worksheet is blank. *However, if I substitute this line with e_wksheet3.Range("A1") = e_wksheet1.Range("A19") the cell at A1 is populated. Any thoughts? Thanks Edward There are two ways, at least. *One is to loop through the range addressing each cell with the Cells(row,col) property which is a bit complicated. *The other is to use the clipboard in a somewhat different manner than your first attempt. *That is ... * * e_wksheet3.Range("A1:I3").Copy * * ActiveSheet.Paste e_wksheet1.Range("A19") This approach avoids the problem with getting the 'selection' setup correctly and will work for single cells as well. *Note, however, that since this performs a copy, any formulas will be copied over (if there are any in the range), not just the values. To paste just the values (if formulas might be present), the following can be used instead ... * * Const xlPasteValues = -4163 * * e_wksheet3.Range("A1:I3").Copy * * e_wksheet1.Range("A19").PasteSpecial xlPasteValues _____________________ Wow Tom, that's awesome service! *I'm hugely in your debt. This will fox you, though. *Let's say that the range of values to be copied (above shown as e_wksheet3.Range("A1:I3")) was unknown - or, to be completely specific, it was known that the columns were A through I, but the number of rows was unknown. *I could cludge it by writing * * *Const xlPasteValues = -4163 * * *e_wksheet3.Range("A1:I5000").Copy * * *e_wksheet1.Range("A19").PasteSpecial xlPasteValues but there would be sure to be circumstances where this was not a good idea. *Any idea how to find the index of the first empty row and use it in the script above? *I'm aware of a constant called xlDown, but not sure how to use it, or even if it's the right one.... Again, many thanks Edward We're playing 'stump the chump', are we? ;-)) Try something like this ... Const xlPasteValues = -4163, xlDown = -4121, xlToRight = -4161 sLRCorner = Range("A1").End(xlDown).End(xlToRight).Address(Fal se, False) e_wksheet3.Range("A1:" & sLRCorner).Copy e_wksheet1.Range("A19").PasteSpecial xlPasteValues This assumes there are no blank (empty) cells along the path traced (down and then right). A reversal of the two constants can also be used (right and then down), if that will work better. If the desired range is the only block of data on the sheet then the location of the last cell can be found with this ... Const xlLastCell = 11 sLRCorner = Range("A1").SpecialCells(xlLastCell).Address(False , False) BTW, I figured all of this out by running the Macro Recorder in Excel and then reviewing the VBA code that was created - and a few references to the help documentation in the VB editor. _____________________ Tom Lavedas |
#5
Posted to microsoft.public.sqlserver.dts,microsoft.public.sqlserver.programming,microsoft.public.excel.programming,microsoft.public.scripting.vbscript
|
|||
|
|||
vb script in dts throws error when operating on Excel file - helpplease!
On Oct 6, 6:29*pm, Tom Lavedas wrote:
On Oct 6, 10:24*am, Edward wrote: On Oct 6, 1:52*pm, Tom Lavedas wrote: On Oct 6, 6:07*am, Edward wrote: On Oct 5, 6:50 pm, Tom Lavedas wrote: On Oct 5, 1:17 pm, Edward wrote: SQL Server 2000 DTS VBScript Excel I have to manipulate an Excel file within a DTS package. *Put simply, I need to move the contents of certain cells in one Worksheet into diffferent cells on another worksheet. Here's my code: Function Main * * * * Dim e_app * * * * Dim e_wbook * * * * Dim e_wksheet1 * * * * Dim e_wksheet2 * * * * Dim e_wksheet3 * * * * Dim sFilename * * * * sFilename = "\\Server1\Shared\Import\ManagerQuestionnaire. xls" * * * * ' Create the Excel Object * * * * Set e_app = CreateObject("Excel.Application") * * * * ' Open up the Excel Spreadsheet * * * * Set e_wbook = e_app.Workbooks.Open(sFilename) * * * * ' Which sheet do we get our data from * * * * Set e_wksheet1 = e_wbook.Worksheets("Sheet1") * * * * Set e_wksheet2 = e_wbook.Worksheets("Sheet2") * * * * e_wksheet1.Range("A7").Select * * * * e_wksheet1.Copy * * * * e_wksheet2.Select <-- BARFS HERE WITH "Select method of worksheet class failed" * * * * e_wksheet2.Range("A1").Select *<-- If I remove the above line it barfs with "Select method of Range class failed" I've tried setting e_wksheet2 to the e_wbook.Worksheets("Sheet1") (same as e_wksheet1) but it still fails. *The code parses correctly. It obviously gets beyond the Select method call on e_wksheet1 so why is it failing on e_wksheet2? Thanks Edward Try directly addressing the ranges that you want to use instead of the very problematic cell Select approach that the macro recorder tends to provide, something like this ... * * * * ' Which sheet do we get our data from * * * * Set e_wksheet1 = e_wbook.Worksheets("Sheet1") * * * * Set e_wksheet2 = e_wbook.Worksheets("Sheet2") * * * * *e_wksheet2.Range("A1") = e_wksheet1.Range("A7") Hi Tom This approach worked really well - thanks. I also need to copy a range of cells, and this is the approach that I'm trying: e_wksheet3.Range("A1:I3") = e_wksheet1.Range("A19:I22") Although the script runs without error, the target worksheet is blank. *However, if I substitute this line with e_wksheet3.Range("A1") = e_wksheet1.Range("A19") the cell at A1 is populated. Any thoughts? Thanks Edward There are two ways, at least. *One is to loop through the range addressing each cell with the Cells(row,col) property which is a bit complicated. *The other is to use the clipboard in a somewhat different manner than your first attempt. *That is ... * * e_wksheet3.Range("A1:I3").Copy * * ActiveSheet.Paste e_wksheet1.Range("A19") This approach avoids the problem with getting the 'selection' setup correctly and will work for single cells as well. *Note, however, that since this performs a copy, any formulas will be copied over (if there are any in the range), not just the values. To paste just the values (if formulas might be present), the following can be used instead ... * * Const xlPasteValues = -4163 * * e_wksheet3.Range("A1:I3").Copy * * e_wksheet1.Range("A19").PasteSpecial xlPasteValues _____________________ Wow Tom, that's awesome service! *I'm hugely in your debt. This will fox you, though. *Let's say that the range of values to be copied (above shown as e_wksheet3.Range("A1:I3")) was unknown - or, to be completely specific, it was known that the columns were A through I, but the number of rows was unknown. *I could cludge it by writing * * *Const xlPasteValues = -4163 * * *e_wksheet3.Range("A1:I5000").Copy * * *e_wksheet1.Range("A19").PasteSpecial xlPasteValues but there would be sure to be circumstances where this was not a good idea. *Any idea how to find the index of the first empty row and use it in the script above? *I'm aware of a constant called xlDown, but not sure how to use it, or even if it's the right one.... Again, many thanks Edward We're playing 'stump the chump', are we? ;-)) Try something like this ... *Const xlPasteValues = -4163, xlDown = -4121, xlToRight = -4161 *sLRCorner = Range("A1").End(xlDown).End(xlToRight).Address(Fal se, False) *e_wksheet3.Range("A1:" & sLRCorner).Copy *e_wksheet1.Range("A19").PasteSpecial xlPasteValues This assumes there are no blank (empty) cells along the path traced (down and then right). *A reversal of the two constants can also be used (right and then down), if that will work better. If the desired range is the only block of data on the sheet then the location of the last cell can be found with this ... *Const xlLastCell = 11 *sLRCorner = Range("A1").SpecialCells(xlLastCell).Address(False , False) BTW, I figured all of this out by running the Macro Recorder in Excel and then reviewing the VBA code that was created - and a few references to the help documentation in the VB editor. Ok, I failed to "stump the chump". That should, of course, be "stump the champ" by the way. I, and my clients, are in your debt. Dunno how I'm going to pay it, but there you are. Thanks again. Edward |
#6
Posted to microsoft.public.sqlserver.dts,microsoft.public.sqlserver.programming,microsoft.public.excel.programming,microsoft.public.scripting.vbscript
|
|||
|
|||
vb script in dts throws error when operating on Excel file - helpplease!
On Oct 8, 3:52*am, Will wrote:
On Oct 6, 6:29*pm, Tom Lavedas wrote: On Oct 6, 10:24*am, Edward wrote: On Oct 6, 1:52*pm, Tom Lavedas wrote: On Oct 6, 6:07*am, Edward wrote: On Oct 5, 6:50 pm, Tom Lavedas wrote: On Oct 5, 1:17 pm, Edward wrote: {snip} This will fox you, though. *Let's say that the range of values to be {snip} Again, many thanks Edward We're playing 'stump the chump', are we? ;-)) Try something like this ... {snip} Ok, I failed to "stump the chump". *That should, of course, be "stump the champ" by the way. I, and my clients, are in your debt. *Dunno how I'm going to pay it, but there you are. Thanks again. Edward You are welcome. I enjoy the challenge and learn as I 'earn', so we all win. _____________________ Tom Lavedas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBS Script and ClearContents -- Code Throws An Error | Excel Programming | |||
Find Throws Error 91 | Excel Programming | |||
Excel 2000 - Excel 2003 now throws 'runtime error' | Excel Programming | |||
How do I perform a certain function if VBA throws up an error? | Excel Programming |