From: Tony Toews [MVP] on
"Dave O." <nobody(a)nowhere.com> wrote:

>I'm creating a new Excel file (referenced: Microsoft Excel 10.0 Object
>Library)

Once you have the code working to your satisfaction I'd recommend
changing the code to use late binding.

Late binding is very useful when you don't know version of the
external application will reside on the target system. Or if your
organization is in the middle of moving from one version to another.
Or if you don't know if the user even has Excel installed on their
system.

For more information including additional text and some detailed links
see the "Late Binding in Microsoft Access" page at
http://www.granite.ab.ca/access/latebinding.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
From: Dave O. on

"Paul Clement" <UseAdddressAtEndofMessage(a)swspectrum.com> wrote in message
news:v20go5tv7517jhfv0njtdnfhq0ps32srvq(a)4ax.com...

> 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

Thanks but all that does is prefix an apostrophe which does not work with
XLS files, at least it does not work for me - when the file is opened with
Excel the column is all in scientific notation.

Thanks anyway
Dave O.



From: Dave O. on

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

Thanks, the macro was:

Columns("B:B").Select
Selection.NumberFormat = "0"

But I could not find any permutation of references which worked, there were
several which executed but none seemed to work, whenever the XLS file was
opened the column was still in scientific notation.

Thanks Anyway
Dave O.


From: Nobody on
"Dave O." <nobody(a)nowhere.com> wrote in message
news:OObInTSuKHA.4908(a)TK2MSFTNGP06.phx.gbl...
> Thanks, the macro was:
>
> Columns("B:B").Select
> Selection.NumberFormat = "0"
>

Why not using this and avoiding the selection?

Worksheets("Sheet1").Columns("B").NumberFormat = "0"


From: Dave O. on

"Nobody" <nobody(a)nobody.com> wrote in message
news:u7kAGQwtKHA.4220(a)TK2MSFTNGP05.phx.gbl...
> "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.
>
Hi

Been there done that, I certainly found several "solutions" that looked like
they should work, but even after an hour or so of looking through the VBA
samples on MSDN and trying to get them to work with VB6 I still could not
get it to alter the format of any cells from code.
This is what I hate about OOP, it's great if you already know every property
& method but if you don't the documentation is lamentable and very cryptic,
with Microsoft excelling in their ability to publish help that tells you
everything except anything you need to know.

thanks anyway
Dave O.