Excel output always as text? 1

[mutley1]

Afternoon all,

I'm trying to output some details to Excel and have no problem getting them into the spreadsheet. Basically, I have a template which i copy to be the "live" sheet to be populated, then insert the data. The problem I have is that there is a numeric output and it always puts it in as text (preceeded by a single quote (')). i have tried using the "create table" in DTS, but it works the first time, but if i clear the data and use that as a template it goes back to text again. I have also tried formatting the columns on the template sheet that gets copied but that doesn't work either.

Any help greatly appreciated.

Cheers,

M.

 

[AlexCuse]

Depending how comfortable you are with VB, you could possibly create an activeX task to go in and format the spreadsheet on completion?

My first thought was a formatted template, but I see that didn't work for you. Let me know what you end up using for the solution. I can think of a few places I would like to do this (but not badly enough to invest much time in).

Good Luck,

Alex





It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[mutley1]

Hi Alex,

Thanks for the response. Unfortunately VB is not a stong point of mine, but I might have a scout around for some code for formatting. I'm sure one of the Guru's out there has written something once upon a time!

If I decide to persue it I'll post back a response and let you know, but the same as you, I won't invest loads of time it it. It's just a "nice to have".

Cheers,

M.

 

[AlexCuse]

Hi Mutley,

Whenever I had a minute today, I spent a little time working on this problem. I came up with this, based off an excel macro I recorded. Hat tip to PHV for helping me get the numeric constants right for the pastespecial operation.

Function Main()
             Dim xlApp
             Set xlApp = CreateObject("Excel.Application")
             Set xlWB = xlApp.Workbooks.Open("c:\test.xls")
             Set WS = xlApp.Application.Workbooks(1).Sheets(1)
          
	xlApp.Range("H1").Select  	
		'enter 1 in an empty cell to use for conversion
    	xlApp.ActiveCell.FormulaR1C1 = "1"
    	xlApp.Range("H1").Select
    	xlApp.Selection.Copy		
		'copy the 1 for conversion 
 	xlApp.Range("C2:C58").Select
	xlApp.Selection.PasteSpecial -4104, 4, False, False
		'delete the 1	
	xlApp.Range("H1").Delete


		 'Additional stuff I added for simple conversions (not text to number)
             xlApp.Columns("B:B").Select
             xlApp.Selection.NumberFormat = "General"
             xlApp.Columns("D:D").Select
             xlApp.Selection.NumberFormat = "0.00%"
              

		'Save and close open workbook
             xlApp.ActiveWorkbook.Save
             xlApp.ActiveWorkbook.Close
       
             Main = DTSTaskExecResult_Success
End Function

couple notes, I originally added the entire column instead of xlApp.Range("C2:C58").Select but this caused my test file to go from 18k to 2,361k. With large files this could be a problem. If you have a variable number of rows being put out, I would consider capturing the rowcount and assigning this to a global variable, and then using that number as the upper part of your range.

I'm also pretty sure there is room to make this faster (ie don't select, just copy ranges directly, etc...) but I have not had time to test those changes.


Hope this helps,

Alex


BTW, I love the handle. Dastardly and Mutley has always been my favorite cartoon. I imagine that's where it came from?





It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[mutley1]

Thanks Alex,

I'll give that a go - have a star! Yeah, I got the nickname from the guys in the office at my first job because (due to the stress of IT causing me a 20 a day Marlboro habit!) my laugh is just the same....

Cheers,

Mutley