VB & Excel
(P.S. am using ... objActiveWkb.Worksheets(1).Cells(lCell, (lIndex)) = ' to populate.)
Thanks Greg
[358 byte] By [gupex] at [2007-11-11 8:49:45] 
Am having a prob in exporting data wherein Excel is (incorrectly) deciding that '2-1101' is not a date but wants to convert '2-2210' to 'Feb-10' - neither are dates, so how do I tell xl to leave them as is?
(P.S. am using ... objActiveWkb.Worksheets(1).Cells(lCell, (lIndex)) = ' to populate.)
Thanks Greg
# 1 Re: VB & Excel
Normally in excel to leave a numeric value as a string you type '12345Pass the value as a string, or put a ' infront of the number to force it to a string (general)
RHelliwell at 2007-11-11 17:24:59 >
# 2 Re: VB & Excel
Thanks Rich - worked a treat!I am an Excel novice, so rather than bother you with q. after q., is there some resource you can point me to?
Would also like to know the rest of 'to force it to a string (general)' - i.e. how do you 'force' other formats?
I now wish to do things like set column width to accomodate the data in it (and who knows what else). I am using :-
"Set objActiveWkb = Excell.Application.ActiveWorkbook" and assumed that when I type things like "objActiveWkb." I would see a list of what was available, but I don't :-(
P.S. Have you got your 'Ashes' tickets yet?
gupex at 2007-11-11 17:25:59 >
# 3 Re: VB & Excel
The syntax for VB in office apps. is known as VBA (Visual Basic for Applications).There's loads of stuff on the net about VBA.
google search (http://www.google.co.uk/search?num=20&hl=en&q=Excel+VBA&btnG=Search&meta=)
This one looks good! (http://www.excel-vba.com/macros/index.htm)
What I tend to do in Excel is record a macro and copy the code.
For instance, if you want code to resize a column:
1 - Start a new sheet
2 - Start recording a macro
3 - perform the function (resize column A to 20)
4 - stop recording
5 - edit the macro, you'll see the code to perform the action.
Columns("A:A").ColumnWidth = 20
You could use this method to set the format of a cell, or any other property you wish. Here's 2dp percentage:
Selection.NumberFormat = "0.00%" 'with selection being the ACTIVE cell.
Also, the object/method browser is very limited. Unfortunately, that's just the way it is :(
P.S. I'm in the USA now, so the only chance i'll get to see us beat you again is on bbc.co.uk
RHelliwell at 2007-11-11 17:26:59 >
# 4 Re: VB & Excel
Thanks again Rich :-)gupex at 2007-11-11 17:28:04 >
# 5 Re: VB & Excel
Let me (us) know how you get on :) - always here to help!RHelliwell at 2007-11-11 17:29:03 >
