Prev: need socket ocx recommendation
Next: Is there a way to convert a .TIF file in VB6
From: Dave O. on 26 Feb 2010 11:26
Hi

I'm creating a new Excel file (referenced: Microsoft Excel 10.0 Object
Library) and I have a column of 13 digit numbers which Excel being helpful
as usual is showing in scientific notation, I've tried prefixing the column
content with an apostrophe which works for CSV files being read into Excel
but apparently not in XLS files.

Code Extracts:

Dim XL As Excel.Workbook
Dim XLA As Excel.Application
Dim XS As Excel.Worksheet

Set XLA = New Excel.Application
Set XL = XLA.Workbooks.Open(XLSName)
Set XS = XL.Worksheets(1)

tLn = Split(XLSContent, vbCrLf)
tCl = Split(tLn(i), "�")
For n = 0 To UBound(tCl)
XS.Cells(i + 1, n + 1) = tCl(n)
Next
Next

You get the idea I'm sure - What I need to know is how can I use the library
to force column 2 to text to stop Excel from being "helpful". Any other tips
like setting column width and simple formatting (bold text) would be nice
but not as important.

TIA
Dave O.


From: Dave O. on 26 Feb 2010 11:40
whoops, I omitted an important line when grabbing code extracts:

tLn = Split(XLSContent, vbCrLf)
For i = 0 to UBound(tLn) <-------Obviously!
tCl = Split(tLn(i), "�")
For n = 0 To UBound(tCl)
XS.Cells(i + 1, n + 1) = tCl(n)
Next
Next

Regards
DaveO.
(PS This is from work, don't expect any replies until Monday)


From: Nobody on 26 Feb 2010 11:51
"Dave O." <nobody(a)nowhere.com> wrote in message
news:eBeYsBwtKHA.3360(a)TK2MSFTNGP06.phx.gbl...
> Any other tips like setting column width and simple formatting (bold text)
> would be nice but not as important.

There is probably a property or method to set a column format, which seems
to be what you want. Press F2 in VB6 and search for "format" in Excel
library. I found the solution in less than a minute.



From: Paul Clement on 26 Feb 2010 12:12
On Fri, 26 Feb 2010 16:26:20 -0000, "Dave O." <nobody(a)nowhere.com> wrote:

� Hi
�
� I'm creating a new Excel file (referenced: Microsoft Excel 10.0 Object
� Library) and I have a column of 13 digit numbers which Excel being helpful
� as usual is showing in scientific notation, I've tried prefixing the column
� content with an apostrophe which works for CSV files being read into Excel
� but apparently not in XLS files.
�
� Code Extracts:
�
� Dim XL As Excel.Workbook
� Dim XLA As Excel.Application
� Dim XS As Excel.Worksheet
�
� Set XLA = New Excel.Application
� Set XL = XLA.Workbooks.Open(XLSName)
� Set XS = XL.Worksheets(1)
�
� tLn = Split(XLSContent, vbCrLf)
� tCl = Split(tLn(i), "�")
� For n = 0 To UBound(tCl)
� XS.Cells(i + 1, n + 1) = tCl(n)
� Next
� Next
�
� You get the idea I'm sure - What I need to know is how can I use the library
� to force column 2 to text to stop Excel from being "helpful". Any other tips
� like setting column width and simple formatting (bold text) would be nice
� but not as important.

Not sure that I understand what your code is doing, but the following worked for me:

XS.Cells(i + 1, n + 1) = Chr$(39) & XS.Cells(i + 1, n + 1)


Paul
~~~~
Microsoft MVP (Visual Basic)
From: GS on 26 Feb 2010 12:32
The cell format needs to be changed from 'General' to 'Number'. You can do
this in code by setting the .NumberFormat property as follows:
.NumberFormat = "0"

This will allow whole numbers of any length without thousands separators. To
get code any other number format, open Excel and record a macro, then change
the format manually to the desired setting, then stop recording. You can
view the generated code in the VBE (ALT+F11). Just paste the relevant parts
into your VB6 project, making sure you use all the necessary object refs.
--

HTH
Kind regards,
Garry

"Dave O." <nobody(a)nowhere.com> wrote in message
news:eBeYsBwtKHA.3360(a)TK2MSFTNGP06.phx.gbl...
> Hi
>
> I'm creating a new Excel file (referenced: Microsoft Excel 10.0 Object
> Library) and I have a column of 13 digit numbers which Excel being helpful
> as usual is showing in scientific notation, I've tried prefixing the
> column content with an apostrophe which works for CSV files being read
> into Excel but apparently not in XLS files.
>
> Code Extracts:
>
> Dim XL As Excel.Workbook
> Dim XLA As Excel.Application
> Dim XS As Excel.Worksheet
>
> Set XLA = New Excel.Application
> Set XL = XLA.Workbooks.Open(XLSName)
> Set XS = XL.Worksheets(1)
>
> tLn = Split(XLSContent, vbCrLf)
> tCl = Split(tLn(i), "�")
> For n = 0 To UBound(tCl)
> XS.Cells(i + 1, n + 1) = tCl(n)
> Next
> Next
>
> You get the idea I'm sure - What I need to know is how can I use the
> library to force column 2 to text to stop Excel from being "helpful". Any
> other tips like setting column width and simple formatting (bold text)
> would be nice but not as important.
>
> TIA
> Dave O.
>
>


 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: need socket ocx recommendation
Next: Is there a way to convert a .TIF file in VB6