Help w/VB6 Code for Formatting Excel Workbook

[TheShaner]

Ok, I'm working on a database project using Access 2003, which naturally comes with Visual Basic 6. The question I have is with VB6 code.

I have a button that will be creating an Excel workbook. Everything works fine with it. All the info I want is there, however, the values need formatting. I have values like "000123" that are being reduced to "123" because the column is treating all the cells in that column as a number format when I want them as string formats (or text format rather). Is there some code I can apply to the cell before or after inserting the data so that it does not chop my values? I've even done something like this which did not work:
xlTmp.ActiveSheet.Cells(i, 1) = CStr(rs("FileNum").Value)
And:
xlTmp.ActiveSheet.Cells(i, 1) = "" & rs("FileNum").Value
Excel is seeing that these are all numbers, and so treating them as thus. However, I want the zeros in front. What can I do about this?

Also, is there a way to apply formulas to a cell? I think that would greatly reduce the time it takes to create the Excel report seeing that right now I'm having my VB code calculate the values, which is making a lot of look-ups to my recordset.

Maybe even easier would be if someone could supply a link of where I could just get most or all of the code for VB6 that interacts with Excel! That way I won't have to post follow-ups, hehe. I've done a lot of googling and have been more or less unsuccessful. I've only been able to come up with very small tutorials that just demonstrate the basics.

Thanks for any help with this.

-Shane

[TheShaner]

Anyone ever written VB6 code (or any VB code for that matter) to create Excel spreadsheets? I just need to know where to find the code to format cells with VB. Maybe I should just try VBA code? I know it's very similar, but not sure if it'll be the same syntax or not. Any help will be greatly appreciated!

EDIT:
This problem is mostly solved. I looked up VBA code and most of it is syntactically exact. I used this:
Set xlTmp = New Excel.Application
xlTmp.ActiveSheet.Columns("A:B").NumberFormat = "000000"
to format my first two columns to always have digits with leading zeros to make it 6 characters long. However, I would still like to know if there is some resource out there that I can get VB code to write and format to Excel. Only small dumb tutorials exist as far as I know.

-Shane

[harlequin2k5]

Hang on Shaner - I think I can find the answer - just have to test it real quick :)

[harlequin2k5]

try this one...


Range("J15:L24").Select
Selection.NumberFormat = "00000"

where the range is your range and the numberformat is the number of digits

all that I did was create a macro in excel of what you were trying to achieve and then clicked to run the macro but selected to edit it instead - although it pulls up the vba editor it coincides with other examples from my vb 6.0 book

I hope that helps - I'm trying to find the cd for the book and/or the website for the cd to send to you - the book is Mastering VB 6.0 from sybex (http://www.sybex.com) - the author is evangelos petroutsos

[TheShaner]

Thanks a lot Harlequin. That's pretty much what I found also. I found that same example, among a few others, when I just searched for VBA and left out the fact that I was using VB 6.0.

I have Microsoft's Programming Visual Basic 6.0 book and I had looked in the index for Excel, but it had nothing. But just now, I thought of seeing if it had anything about VBA. It has about 70 pages devoted to VBA, haha. So I'm going to look in there and see what I can find. It's introduction says that Visual Basic contains all of VBA and more, so maybe VBA's syntax coincides perfectly with VB6, so I just need to look up VBA code instead.

EDIT: Although my book talks about VBA, it doesn't go into using it on any applications, like Excel. So currently I'm having trouble with adding formulas to Excel columns and/or cells via VB.

-Shane

[harlequin2k5]

my book also seems to imply that you would use vba to automate other office products - although I may be all wet I think it's because you're asking vb to work with excel's "application" object? but my book makes the same references to vba for word and outlook as well

lol I'm having my own troubles with vb but maybe between both books we may be able to get it all figured out? :D

I personally just need to find out how to make my vb app connect to a database stored on my website :( any thoughts on that one?

[TheShaner]

Ah ha! I"m slowly moving in the right direction. This worked for a formula:

xlTmp.ActiveSheet.Cells(2, 11).Formula = "=G2*(H2+I2)"

What had stumped me before was that I didn't have the equal sign in the string for the formula, and so it wasn't being treated as a formula.

Now, I'd like to place the entire column with that formula. This works:

xlTmp.ActiveSheet.Columns("K:K").Formula = "=G:G*(H:H+I:I)"

However, the first row I'm using as headers for the columns. Excel doesn't know that since they're just text that I've entered for those rows. Is there a way to declare the first row as a header row so that all formulas will only be applied to rows after that first row? As you can see, I'm not completely adept at Excel, which is slowing down my progress in writing VB code for it.

If there isn't a way, it's fine, because I've tested the first piece of code and used an iterator to loop through the cells so that each cell gets the formula, rather than applying it to the entire row, like so:

xlTmp.ActiveSheet.Cells(i, 11).Formula = "=G" & i & "*(H" & i & "+I" & i & ")"

-Shane

[TheShaner]

As for VBA, that is the programming language behind all Microsoft products (excel, outlook, word, etc.). So using the VB language, you can write code the integrates into all of those. My main problem was that each application has it's own specific libraries. Like Excel has code for opening a Workbook, Worksheet, applying formulas, etc., whereas Outlook will have specific code that applies to creating emails and such. All of them share the control structures of VB, but my problem was the specific library for Excel because that's the code I could not find and needed.

As for your problem, what kind of database are you connecting to. There are lots of tutorials and resources out there about setting up DAO, ADO, ODBC, and OLE DB connections. My book alone covers all of them, hehe. If you're more specific, I'm sure I can find out. For me, my connection is simple. I'm using Access, lol.

-Shane

[harlequin2k5]

ah okies - I misunderstood what you were saying

as far as my issue - I have an access db on my website - I want an app that connects to it but I can't even get as far as creating the data source for it - I've setup datasources for local db's but never for one on the web and no matter what I try I can't get it - I've tried jets I've tried odbc's - I've finally found the one thing I can't figger out *chuckles* - took more than 10 years of pathetically basic programming to find something I can't get lol

[TheShaner]

Haha, well, don't feel to bad. From my understanding, that may not be possible anyway without using an ASP page to make the connection to your remote MDB. I may be wrong tho, as often times I am, lol.

-Shane

[harlequin2k5]

don't laugh - that makes sense

that's one of the errors I get in access if I try to connect to the web db is that it can't find a data access page - I realize that a dap/adp is different from an asp

I click on file->open->my network places...blah...blah

wait several seconds and the following message comes up:

Microsoft is unable to open the data access page

The file you attempted to load was not recognized as HTML

You may have selected the wrong file, or tried to open a database file off of a web server

so how do I build this asp thingy? Edit: Please don't tell me I need to build an asp site?

[TheShaner]

This is about the time where my help runs out because I've never done this before, lol. I've used ASP for connecting to DBs and such, but never attempted to have a local app use ASP to connect to a remote DB. ASP pages re processed by the server, so the ASP page would have to be hosted on the server. If you have an Access DB on your server, then ASP will be supported seeing that your server most likely uses IIS6.

With that in mind, you can have your ASP page connect and query your Access DB. The problem that I don't know is how to have your VB app grab the results from the ASP page. I've never created a VB app that accessed anything on the internet, like an ASP page. I think Google or somewhere else will have to continue with this, hehe.

-Shane

[wayne80]

I have met the similar problem when using excel.application to export table contents.
the data will keep its original format if you set the cell as text format. I think the js code below will work:
var oXL = new ActiveXObject("Excel.Application");
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
oSheet.Cells.NumberFormat = "@"; // @ means saving as "text" format

So you only need to set NumberFormat = "@" in VB code.