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

"GS" <GS(a)discussions.microsoft.com> wrote in message
news:OkLUFeXuKHA.2072(a)TK2MSFTNGP02.phx.gbl...
>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?

The location for saved files is set in the program by a BrowseForFolder
dialogue, the filename is the same as the source file with the extension
changed to XLS.
It is a simple process to store and restore any binary object in a resource
file in VB6, so yes, we can do this in VB. But now I know the correct way to
create a file from scratch in code I'll look at that.
In all the tests of a variety of techniques I have never once had Excel
prompt to save the file, I wonder where you got that idea from?
The program is a in-house development to be used by one or two people to
replace a tedious weekly manual operation, I have total control over what is
on their PCs so I really don't have a lot to worry about.
I did not put pages of irrelevant code in the earlier post because it was
irrelevant, I could post the 28k of source code the app consists of but that
would be very stupid as I only had a problem with one or two lines.

Thanks for you interest
Dave O.


From: Dave O. on 2 Mar 2010 05:13

"Bob Butler" <noway(a)nospam.ever> wrote in message
news:ewsoG4VuKHA.3408(a)TK2MSFTNGP06.phx.gbl...
>
> "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...

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

Excellent, that works a treat, thank you V. much

Regards
Dave O.


From: GS on 2 Mar 2010 14:37
Hi Dave,

Thanks for your feedback!

<<In all the tests of a variety of techniques I have never once had
Excel prompt to save the file, I wonder where you got that idea from?>>
The qualifier in my statement was "If handed over to the user...". At
shutdown, Excel will always prompt to save any changes since last save
was done. If you control everything as you say you do here (including
the save) then Excel will not prompt to save unless changes happen
following your programmatic save. This is normal behavior for any
software where the active doc has been changed. In the case of a new
(ergo: unsaved) doc, apps will usually prompt to save any work done via
the Save As... dialog. So this is where I get this idea from!<g>

Also, the thought of including the binary as a resource in my project
never occurred to me once in all my programming lifetime. My quip
comment to Bob was not directed toward you personally since I assumed
you were saving this file as would be expected normal action within the
context of what you're doing. Assuming, in general, leads to providing
erroneous content and so I was just acknowledging my having assumed
your programming actions regarding the file save action. Bob's reply
assumes you need to save for the formatting to persist<IMHO>!

Please forgive my sense of humor and accept my apologies for any
offence taken by you or Bob!

Kind regards,
Garry
--

on 3/2/2010, Dave O. supposed :
> "GS" <GS(a)discussions.microsoft.com> wrote in message
> news:OkLUFeXuKHA.2072(a)TK2MSFTNGP02.phx.gbl...
>>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?
>
> The location for saved files is set in the program by a BrowseForFolder
> dialogue, the filename is the same as the source file with the extension
> changed to XLS.
> It is a simple process to store and restore any binary object in a resource
> file in VB6, so yes, we can do this in VB. But now I know the correct way to
> create a file from scratch in code I'll look at that.
> In all the tests of a variety of techniques I have never once had Excel
> prompt to save the file, I wonder where you got that idea from?
> The program is a in-house development to be used by one or two people to
> replace a tedious weekly manual operation, I have total control over what is
> on their PCs so I really don't have a lot to worry about.
> I did not put pages of irrelevant code in the earlier post because it was
> irrelevant, I could post the 28k of source code the app consists of but that
> would be very stupid as I only had a problem with one or two lines.
>
> Thanks for you interest
> Dave O.


From: Karl E. Peterson on 2 Mar 2010 16:14
Dave O. 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.

It looks to me like you're being bitten by the default property of the
object you're assigning to. Which, more than likely, is Value. How
about being explicit, and assigning directly to the Text property?

--
..NET: It's About Trust!
http://vfred.mvps.org


From: Dave O. on 3 Mar 2010 04:44

"Karl E. Peterson" <karl(a)exmvps.org> wrote in message
news:%23rhoq1kuKHA.3428(a)TK2MSFTNGP06.phx.gbl...
> Dave O. 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.
>
> It looks to me like you're being bitten by the default property of the
> object you're assigning to. Which, more than likely, is Value. How about
> being explicit, and assigning directly to the Text property?
>
> --
> .NET: It's About Trust!
> http://vfred.mvps.org
>

Interesting, the intellisense for the "Cells(x,y)" property implies that
there is nothing to go after the coordinates (ie if I place a . there, I get
no suggestions). It seems that yes, "Value" does work there but "Text"
gives: "Error 1004: Unable to set the Text property of Range class".
Anyway I've got it working now by setting the NumberFormat for the column.

Thanks
Dave O.


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