How save save CVS file with multiple worksheets

[wheretheidivides]

I can convert a dat file (from margrets db class) to a CSV file shown. I was wondering is there a way to save to a CSV with more than 1 worksheet? it's be nice to have worksheet1 in excel as income, worksheet 2 in excel as expenses. That way the person doesn't have to have 2 separate CSV files to open up? Maybe database1.dat becomes worksheet 1 and database 2.dat becomes worksheet 2. The CSV probably would need to have it's own name.

Any one? Bueller?

Sub Button4021_Click
'---------------------------------------------------
   If File.Exists(File.DirRootExternal, "/Saved/PDTK/Databases/dbIncome.dat") = True Then
      ExportToCSVIncome 'SR
      ToastMessageShow("Conversion of dbIncome.Dat to dbIncome.CSV complete.  Now ready to E-Mail.", True)
   Else
      ToastMessageShow("Can not convert." & CRLF & "dbIncome.Dat doesn't exist." & CRLF & "Try 'Add Income' and then 'Save DB'.", True)
   End If
'--------------------------------------------------

End Sub

Sub ExportToCSVIncome
'--------------------------------------------------------------------------
'Convert database to Excel CSV
   Dim CSVList As List
    CSVList.Initialize

'--------------------------------------------------------------------------
'Create header on top row of Excel CSV
   Dim Header As String
   
   For ii = 1 To db.ActiveFields
        If ii < db.ActiveFields Then
      
         Select ii
            Case 1
               Header = Header & "Year" & ","
            Case 2
               Header = Header & "Month" & ","
            Case 3
               Header = Header & "Weekday" & ","
         End Select
      '-----------------------------------------------
       Else
           Select ii
            Case 1
               Header = Header & "Year"
            Case 2
               Header = Header & "Month"
            Case 3
               Header = Header & "Weekday"

         End Select
        End If
   Next
   
'-------------------------------------------
'add the row #1 with the header
   CSVList.Add(Header)

'--------------------------------------------------------------------------
'Create rows for each record of database into CSV
    db.FirstRecord
    For i = 0 To db.Record_Count -1
        Dim NewRecord As String
        For ii = 1 To db.ActiveFields
            If ii < db.ActiveFields Then
                NewRecord = NewRecord & db.GetField("var" & ii) & ","
            Else
                NewRecord = NewRecord & db.GetField("var" & ii)
            End If
        Next
        CSVList.Add(NewRecord)
        db.NextRecord
    Next

'--------------------------------------------------------------------------
'Save database CSV to correct folder
   File.WriteList(File.DirRootExternal & "/Saved/PDTK/DataBases", "dbIncome.csv", CSVList)

'--------------------------------------------------------------------------
End Sub

 

[Vader]

Not possible. A CSV file is a simple text file. It loads into Excel as a worksheet.

What you are trying to do is load multiple worksheets (i.e. a Workbook).

This can be done, but you need to create a different file, not just a CSV.

Here's some links to assist:
Microsoft started with the Office XML format:
Microsoft Office XML formats - Wikipedia, the free encyclopedia

But then moved to the Office Open XML format:
Office Open XML - Wikipedia, the free encyclopedia

Unfortunately for us (with B4A), your official choices in doing this are going to be a little limited. Here is a link to the Open XML SDK:
Generating Excel 2010 Workbooks by using the Open XML SDK 2.0

Looks to me that someone could maybe make some money (or lots of kudos) by recreating a library to do this.

 

[wheretheidivides]

Actually, I found out how. You need to create a macro in excel and save it as a xls file. Then all you have to do is save all the CSV files in the same directory.

1) Create a directory like C:/data on a windows machine
2) Save the excel XLS(that you created with the macro on it) and multiple CSV sheets in the same directory that you want to import.
3) Open the XLS file, click on the button that has the macro set up (you need to make a button on the sheet and assign the macro to it). It'll import all the CSV files in the same folder and each one will be it's own sheet.

Basically, you need to create a button and assign a macro to it. Then when the user opens the XLS file, he clicks on the button and the macro assigned to it will run. The following link has the code you can just copy and paste (it's not the first message but the message next message with the response). you can also program in stuff to do like delete the original sheet and save as a different name.

==================================
Here is a link to copy over the code. You have to create a text box on the excel sheet, create a macro, paste this code on the macro, assign the macro to the box.
[SOLVED] Import multiple csv files into current workbook as separate sheets

Here is link to help on excel macros. BTW this is an example of a real tutorial. I hope people writing tutorials for B4A will take note.
Section 1: Programming in Excel (Macros)

 

[Vader]

Ok, so you found out how to import csv files into a workbook, not actually how to create a workbook.

Nice thinking outside the box though, well done.