Generating excel files with VB

[habenero]

This is what I want to do;

I would like to generate some files (csv) with VB. For example for my fantasy sports teams I keep track of the teams stats, lineups and standings. All 3 for 24 weeks. So if I could generate (CSV) files such as filename-homers_lineup_wk_1 (CSV). The word lineup would be; lineup, stats or standings. The wk_1 would have to increment for all 24 weeks. The GUI I thought of would be to use option buttons for the week and check boxes for stats, lineup and standings.

to show a desired output of that program:

in a dir:

homers_lineup_wk_1
homers_lineup_wk_2
ETC
homers_stats_wk_1
homers_stats_wk_2
ETC
homers_standings_wk_1
homers_standings_wk_2

ETC for all 24 or whatever time period

Can someone point me in the correct way. I realize that I do have to start an instance of excel

[ChrisPadgham]

If you just want a csv file you don't need excel.

open "c:\datafiles\homers_lineup_wk_1" for output as #1
write #1, var1, var2, var3
close #1

[geric823]

You may refer to this collection, but it is in C#. http://www.codeproject.com/Articles/198572/Summarize-C-Control-Excel-Skills

[AndreRet]

to create a new workbook you can use something like -

Dim iCountMake As Integer, iRowMake As Integer, iCountModel As Integer, iRowModel As Integer, iCountType As Integer, iRowType As Integer

Dim objExcl As Excel.Application
Set objExcl = New Excel.Application

objExcl.Workbooks.Open (txtExcellPath.Text)
objExcl.Visible = True
''objExcl.SheetsInNewWorkbook = 1
''objExcl.Workbooks.Add

Dim r As Integer

For r = 0 To lstMake.ListCount - 1
    lstMake.ListIndex = xMakeCount


    Dim Y As Integer

    lstModel.Clear

        objExcl.ActiveSheet.cells(xRecAdded, 1).Value = lstMake.Text
        objExcl.ActiveSheet.cells(xRecAdded, 2).Value = "No Model Listed"

        xRecAdded = xRecAdded + 1

        xModelCount = xModelCount + 1
            Else

    Y = 0

    For Y = 0 To lstModel.ListCount - 1
        lstModel.ListIndex = Y

        objExcl.ActiveSheet.cells(xRecAdded, 1).Value = lstMake.Text
        objExcl.ActiveSheet.cells(xRecAdded, 2).Value = lstModel.Text

        xRecAdded = xRecAdded + 1

        xModelCount = xModelCount + 1
    Next Y
    End If
xMakeCount = xMakeCount + 1
Next r

I've just copied the sample over from some old project, modify it to work for you.
Also, reference excell in your project as well.

[RobCr]

Your request is not clear (or is not detailed enough).
You talk about creating a .csv file which is very do'able.
As someone mentioned that does not require Excel.
Do you then want that file opened immediately in Excel, or does someone do that later ?

You talk about Option Buttons and Checkboxes, but you do not clarify where and when they are required.
Are they meant to be in a VB6 form which is used at the time the .csv file is being created ?
Or are they meant to be in the Excel display ?

Are you talking about using VB6 (NOT Excel's VBA) to create the .csv file ?

You finish with -
"The GUI I thought of would be to use option buttons for the week and check boxes for stats, lineup and standings, to show a desired output of that program"
I reckon that could do with a lot more clarification as well.

If you make the effort to provide a clear and detailed request, you will find that you get a quicker resolution of your problem.

Rob
PS I know how to create an excel file (.xls) 'out of thin air', and Excel does not need to be present on the PC when creating it.
Excel of course would be required to view it.
But I will await your clarifications, before adding to the confusion in this thread.

[habenero]

thanks to all that have replied, those tips are a treasure for me to accomplish my goal. After re-reading my request I can understand that it is confusing.

Those csv files to be generated are empty. I want to just generate those files with those parameters (sub type lineup, sub type stats, sub type standings) then when I go to get my data off the web I'll just re-save the data over the blank generated file. So each file would be for example (filename)_lineup or stats or standings and then a week #1 corresponding to that current data for that week. (filename)_sub type_wk #(x).csv

The option buttons and check boxes would be used during run time to allow the user to select week #s and or the sub type(s) I indicated above. Thinking about it; what would even be better is if this program could show the user at run time the size of the file(s) in a text box. I know some looping is required and that causes me gray hair LOL.

Thanks to all

[habenero]

I guess what I want to do is automate the generation of *.csv files. I'm just really unsure how I can use strings inside filenames

[AndreRet]

Just google search the term "Use strings in filenames in vb6". There are tons of sample code...

My code above opens an excel workbook as blank, let you add the data from a file etc.

[habenero]

Thank you much, Will get my miners helmet on and do some mining :)

[AndreRet]

Only a pleasure. :) Please mark this as solved, thanks.