Delete rows from excel sheet using vbscript in DTS

[sparhawk83]

Hi folks,

I am using a DTS package to first copy a template excel file, then populate it with pre-defined data from a view, then convert the numerical data back to being numerical using VBScript within the DTS package as I have not found a way to get SQL Server to output the data as a number, i.e. you can open the excel workbook and it will show as a number rather than text,

The problem i have is that the vbscript copies in '0' to convert, i'll paste in the code below this text, it leaves '0's in columns where there is no data, what i need to do is either have a way to remove those rows from the excel sheet using the vbscript, or to convert the numericals back only where there are populated rows, see below:

Function Main()

Dim xlApp

Set xlApp = CreateObject("Excel.Application")

Set xlWB = xlApp.Workbooks.Open("\\ServerName\Share\Folder\sheetname.xls")

Set WS = xlApp.Application.Workbooks(1).Sheets(1)



xlApp.Range("J1").Select

'enter 1 in an empty cell to use for conversion

xlApp.ActiveCell.FormulaR1C1 = "1"

xlApp.Range("J1").Select

xlApp.Selection.Copy

'copy the 1 for conversion

xlApp.Range("F2:F873").Select

xlApp.Selection.PasteSpecial -4104, 4, False, False

'delete the 1

xlApp.Range("J1").Delete





'Additional stuff I added for simple conversions (not text to number)

xlApp.Columns("H:H").Select

xlApp.Selection.NumberFormat = "0.00%"





'Save and close open workbook

xlApp.ActiveWorkbook.Save

xlApp.ActiveWorkbook.Close



Main = DTSTaskExecResult_Success

End Function

I've found some code in some websites, but they seem to be based on VBA within excel and macros rather than within the DTS package which is what I need,

Alternatively if there is a way of getting DTS to output the data into excel and leave any numerical data as numerical data that would get around the whole problem,

Many thanks for any assistance!

S

 

[AlexCuse]

Perhaps I am missing something, but I would alter your DTS' source query to simply exclude the rows with no data?

Or if this is an issue of tehre not always being 873 rows, I would try and set up a global variable, and populate it with the rowcount from the query you are using to fill your spreadsheet. You could then use this global variable to only perform the multiplication by 1 on a smaller set of rows I believe (although you may have to do it one row at a time, I have not tried this before)

Also, if you remember where the VBA code that you found to accomplish this task was from, can you please post? It is probably fairly simple to convert it to VBScript for use by DTS.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sparhawk83]

It would be the latter of your thoughts, I have to do the number convert to cover the maximum number of rows, so on Monday it could be 873 but on Tuesday it could be 800,

Unfortunately I can't remember but I'll do another search of the web using the same criteria, chances are I'll find it again, I'll post it if I find it

I'll go have a look at your suggestion re: global variables, we'll see how I get on, thanks for the reply,

Greig

 

[AlexCuse]

Before you go to the trouble of using global variables, I would try this:

dim strRng 

strRng = "F2:F873"

xlApp.Range(strRng).Select

And see if that works. If it does, then global variable will certainly be a good option, and probably less work than using VBScript to delete the rows. If you have to jump through hoops to establish the range it'd probably not be worth it though.

Please post back with your eventual solution as I would be interested to see it.

Thanks,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sparhawk83]

Thanks Alex, that works fine as a replacement for just having a line of:

'xlApp.Range("F2:F873").Select'

I'll let you know how I go with working out a solution,

G

 

[AlexCuse]

Ok, you'll be able to build a string using the global variable value to throw in there then. Let me know if you run into any trouble.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom