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

"GS" <GS(a)discussions.microsoft.com> wrote in message
news:eMpk9rVuKHA.1852(a)TK2MSFTNGP05.phx.gbl...
> The reason it's not working from VB6 is because you need an absolute
> reference to the Excel instance AND the worksheet. Using the example code
> you posted it would look something like this:
>
> XS.Columns("B").NumberFormat = "0"

Hi

I could have sworn that I did try that but when I tried with a blank
spreadsheet created in Excel it does work, however when I tried it on a
spreadsheet that was initially created as zero byte XLS file it does not
work. So either I'll stick with a pre-created blank spreadsheet stored in
the resource file or work out how to create a file properly. I think I'll
stick with the file in the resource file, but I can still use this code for
further formatting enhancements.

Thanks again
Dave O.


From: Bob Butler on 1 Mar 2010 11:39

"Dave O." <nobody(a)nowhere.com> wrote in message
news:uh6Ah1VuKHA.4220(a)TK2MSFTNGP05.phx.gbl...
>
> "GS" <GS(a)discussions.microsoft.com> wrote in message
> news:eMpk9rVuKHA.1852(a)TK2MSFTNGP05.phx.gbl...
>> The reason it's not working from VB6 is because you need an absolute
>> reference to the Excel instance AND the worksheet. Using the example code
>> you posted it would look something like this:
>>
>> XS.Columns("B").NumberFormat = "0"
>
> Hi
>
> I could have sworn that I did try that but when I tried with a blank
> spreadsheet created in Excel it does work, however when I tried it on a
> spreadsheet that was initially created as zero byte XLS file it does not
> work. So either I'll stick with a pre-created blank spreadsheet stored in
> the resource file or work out how to create a file properly. I think I'll
> stick with the file in the resource file, but I can still use this code
> for further formatting enhancements.

Set XLA = New Excel.Application
Set XL = XLA.Workbooks.Add ' create new workbook
Set XS = XL.Worksheets(1)
....
XL.SaveAs "path"


From: GS on 1 Mar 2010 14:43
I would have thought that the OP would have saved the resulting file
via the automated instance of Excel just like you suggest. I guess I
should not have assumed this, but is how I would handle it if the
instance was under my app's control. If handed over to the user then
Excel would prompt to save the file on shutdown.

I certainly would not try to work with a pre-defined worksheet from a
resource file. (Assumes OP means to include this into his VB project
<.res>! Can we even do this in VB?) Usually, we automate an instance,
use it for whatever, output to file, and quit -OR- initialize an
instance, configure the workspace, open any files, make it visible and
(optionally) turn control of the GUI over to the user.

In the OP's context, the results may require the user to specify the
path and filename via a browse dialog before it can be saved. Is the OP
suggesting that the results should persist somewhere without saving, as
if by magic?
--

Garry
--

on 3/1/2010, Bob Butler supposed :
> "Dave O." <nobody(a)nowhere.com> wrote in message
> news:uh6Ah1VuKHA.4220(a)TK2MSFTNGP05.phx.gbl...
>>
>> "GS" <GS(a)discussions.microsoft.com> wrote in message
>> news:eMpk9rVuKHA.1852(a)TK2MSFTNGP05.phx.gbl...
>>> The reason it's not working from VB6 is because you need an absolute
>>> reference to the Excel instance AND the worksheet. Using the example code
>>> you posted it would look something like this:
>>>
>>> XS.Columns("B").NumberFormat = "0"
>>
>> Hi
>>
>> I could have sworn that I did try that but when I tried with a blank
>> spreadsheet created in Excel it does work, however when I tried it on a
>> spreadsheet that was initially created as zero byte XLS file it does not
>> work. So either I'll stick with a pre-created blank spreadsheet stored in
>> the resource file or work out how to create a file properly. I think I'll
>> stick with the file in the resource file, but I can still use this code for
>> further formatting enhancements.
>
> Set XLA = New Excel.Application
> Set XL = XLA.Workbooks.Add ' create new workbook
> Set XS = XL.Worksheets(1)
> ...
> XL.SaveAs "path"


From: GS on 1 Mar 2010 15:52
After looking more closely at your code, it appears that the values are
being inserted in separate columns <XS.Cells(i +1, n + 1)>. So the
first iteration puts the value in Cells(Row(i), Column(1)). The next
iteration puts the value in Cells(Row(i), Column(2); ..and so on. If
this is the case then you need to set NumberFormat for each column.
Here's the needed change to your code:

For n = 0 To UBound(tCl)
With XS.Cells(i + 1, n + 1)
.Value = tCl(n)
.NumberFormat = "0" 'formats the target cell only
'.EntireColumn.NumberFormat = "0" 'formats the entire column
End With
Next
--

As Bob suggests, you must save the workbook to have the results persist
to a file.

HTH
Garry
--

Dave O. presented the following explanation :
> "GS" <GS(a)discussions.microsoft.com> wrote in message
> news:eMpk9rVuKHA.1852(a)TK2MSFTNGP05.phx.gbl...
>> The reason it's not working from VB6 is because you need an absolute
>> reference to the Excel instance AND the worksheet. Using the example code
>> you posted it would look something like this:
>>
>> XS.Columns("B").NumberFormat = "0"
>
> Hi
>
> I could have sworn that I did try that but when I tried with a blank
> spreadsheet created in Excel it does work, however when I tried it on a
> spreadsheet that was initially created as zero byte XLS file it does not
> work. So either I'll stick with a pre-created blank spreadsheet stored in the
> resource file or work out how to create a file properly. I think I'll stick
> with the file in the resource file, but I can still use this code for further
> formatting enhancements.
>
> Thanks again
> Dave O.


From: GS on 1 Mar 2010 16:36
It might be better to set the NumberFormat BEFORE entering the value.
It really shouldn't matter if Calculation is set to xlAutomatic (the
default) because Excel will update the cell. It does matter if this is
turned off for any reason.

For n = 0 To UBound(tCl)
With XS.Cells(i + 1, n + 1)
.NumberFormat = "0" 'formats the target cell only
'.EntireColumn.NumberFormat = "0" 'formats the entire column
.Value = tCl(n)
End With
Next
--

Garry
--

GS explained on 3/1/2010 :
> For n = 0 To UBound(tCl)
> With XS.Cells(i + 1, n + 1)
> .Value = tCl(n)
> .NumberFormat = "0" 'formats the target cell only
> '.EntireColumn.NumberFormat = "0" 'formats the entire column
> End With
> Next


First  |  Prev  |  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