Create several Formatted csv file from cells using VBA Macro

[raffeldt]

I'm a novice to VB programming especially when it come to VBA. I do have a lot of programming experience in other fields. I want to have a spreadsheet that will create or generate csv files so I can import the text into another program. My first problem is that the program that uses the csv file has to be in a specific format. I have spent 2 days trying to get this to work only to run into the problem of the code adding "" quotes to the csv data file. the file should look like this

Text Group,1-English Group 1
1,Windows,Text 1,0,MS Sans Serif,ANSI,8,Regular
2,Windows,Text 2,0,MS Sans Serif,ANSI,8,Regular
3,Windows,Text 3,0,MS Sans Serif,ANSI,8,Regular
4,Windows,Text 4,0,MS Sans Serif,ANSI,8,Regular
5,Windows,Text 5,0,MS Sans Serif,ANSI,8,Regular
6,Windows,Text 6,0,MS Sans Serif,ANSI,8,Regular
7,Windows,Text 7,0,MS Sans Serif,ANSI,8,Regular
8,Windows,Text 8,0,MS Sans Serif,ANSI,8,Regular
9,Windows,Text 9,0,MS Sans Serif,ANSI,8,Regular
10,European,Station Name,0

but what I get is

"Text Group","1-English Group 1"
1,"Windows","Text 1",0,"MS Sans Serif","ANSI",8,"Regular"
2,"Windows","Text 2",0,"MS Sans Serif","ANSI",8,"Regular"
3,"Windows","Text 3",0,"MS Sans Serif","ANSI",8,"Regular"
4,"Windows","Text 4",0,"MS Sans Serif","ANSI",8,"Regular"
5,"Windows","Text 5",0,"MS Sans Serif","ANSI",8,"Regular"
6,"Windows","Text 6",0,"MS Sans Serif","ANSI",8,"Regular"
7,"Windows","Text 7",0,"MS Sans Serif","ANSI",8,"Regular"
8,"Windows","Text 8",0,"MS Sans Serif","ANSI",8,"Regular"
9,"Windows","Text 9",0,"MS Sans Serif","ANSI",8,"Regular"
"10","European","Station Name",0

Notice the "" around each of the text. If I can get rid of that then I could continue with my idea.
I don't know how to get the excel file uploaded so you can see the code so I'm just going to post it

Dim myFile As String, rng As Range, Text As Variant, i As Integer, j As Integer
Dim Font1 As String, Font2 As String, Font3 As String, FCSet As String, FCStyle As String, StationName As String
Dim Text1 As String, Text2 As String, Text3 As String, Text4 As String, Text5 As String, Text6 As String, Text7 As String, Text8 As String
Dim ID As Integer, FCPitch As Integer, FCSize As Integer
Private Sub CommandButton1_Click()
Font1 = "Windows"
Font2 = "European"
Font3 = "MS Sans Serif"
FCSet = "ANSI"
FCStyle = "Regular"
FCPitch = 0
FCSize = 8
ID = 1
StationName = ActiveSheet.Cells(4, 3).Value
'To get this program to work I assigned these static values. Need to figure out how to make them variables
Text1 = ActiveSheet.Cells(4, 2).Value
Text2 = ActiveSheet.Cells(5, 2).Value
Text3 = ActiveSheet.Cells(6, 2).Value
Text4 = ActiveSheet.Cells(5, 3).Value
Text5 = ActiveSheet.Cells(6, 3).Value
Text6 = ActiveSheet.Cells(4, 4).Value
Text7 = ActiveSheet.Cells(5, 4).Value
Text8 = ActiveSheet.Cells(6, 4).Value
myFile = Application.DefaultFilePath & "\Main1TextExport.csv"
'Set rng = Sheet1 Cells

' 1 = cell B4, 2 = Cell B5, 3=cell B6
Open myFile For Output As #1
Write #1, "Text Group", "1-English Group 1"
    For i = 1 To 9
        If ID = 1 Then
            Write #1, ID, Font1, Text1, FCPitch, Font3, FCSet, FCSize, FCStyle
                ElseIf ID = 2 Then
                    Write #1, ID, Font1, Text2, FCPitch, Font3, FCSet, FCSize, FCStyle
                ElseIf ID = 3 Then
                    Write #1, ID, Font1, Text3, FCPitch, Font3, FCSet, FCSize, FCStyle
                ElseIf ID = 4 Then
                    Write #1, ID, Font1, Text4, FCPitch, Font3, FCSet, FCSize, FCStyle
                ElseIf ID = 5 Then
                    Write #1, ID, Font1, Text5, FCPitch, Font3, FCSet, FCSize, FCStyle
                ElseIf ID = 6 Then
                    Write #1, ID, Font1, Text6, FCPitch, Font3, FCSet, FCSize, FCStyle
                ElseIf ID = 7 Then
                    Write #1, ID, Font1, Text7, FCPitch, Font3, FCSet, FCSize, FCStyle
                ElseIf ID = 8 Then
                    Write #1, ID, Font1, Text8, FCPitch, Font3, FCSet, FCSize, FCStyle
                ElseIf ID = 9 Then
                    Write #1, ID, Font1, "Text 9", FCPitch, Font3, FCSet, FCSize, FCStyle
        End If
        ID = ID + 1
    Next i

Write #1, "10", "European", StationName, FCPitch

'Set rng = Selection
'For i = 1 To rng.Rows.Count
'    For j = 1 To rng.Columns.Count
 '       cellValue = rng.Cells(i, j).Value
'     If j = rng.Columns.Count Then
'    Write #1, cellValue
'Else
'    Write #1, cellValue,
'End If
'   Next j
'Next i

'Closes the File
Close #1
    
    
End Sub

I want to be able to change the text in the cells that say Text 1, text 2... then press a command button in the spreadsheet and take the data in those cells and create the csv file that includes all the other text fields. this way I can import the text into the other program very easily.

Any help is greatly appreciated in advance.

 

[oldbrewer]

why not just save the .xls file as a .csv file ?