Reproducing text in visual basic for excel

[visualjoris]

I need to reproduce some text into a textfile and therefore I tried to use a for-loop.
The text that needs to be reproduced is the following:

new Array("r0c0","r0c1","r0c2"),
new Array("r1c0","r1c1","r1c2"),
new Array("r2c0","r2c1","r2c2"),
new Array("r3c0","r3c1","r3c2"),
new Array("r4c0","r4c1","r4c2")

These are no visual basic commands (javascript text). Only the text needs to be reproduced into a textfile.
r?c? which you see in the text represent cell values in excel.
The above text is for 5 lines (text new array 1 till 5)and 3 cell values (e.g. r0c0 till r0c2).
If we have for example 10 lines and 7 cell valuess then
the text would look like:

new Array("r0c0","r0c1","r0c2","r0c3","r0c4","r0c5","r0c6"),
new Array("r1c0","r1c1","r1c2","r1c3","r1c4","r1c5","r1c6"),
new Array("r2c0","r2c1","r2c2","r2c3","r2c4","r2c5","r2c6"),
new Array("r3c0","r3c1","r3c2","r3c3","r3c4","r3c5","r3c6"),
new Array("r4c0","r4c1","r4c2","r4c3","r4c4","r4c5","r4c6"),
new Array("r5c0","r5c1","r5c2","r5c3","r5c4","r5c5","r5c6"),
new Array("r6c0","r6c1","r6c2","r6c3","r6c4","r6c5","r6c6"),
new Array("r7c0","r7c1","r7c2","r7c3","r7c4","r7c5","r7c6"),
new Array("r8c0","r8c1","r8c2","r8c3","r8c4","r8c5","r8c6"),
new Array("r9c0","r9c1","r9c2","r9c3","r9c4","r9c5","r9c6")

So I created the following visual basic code:

Sub create_file()
Dim fso, txtfile
Set fso = CreateObject("scripting.filesystemobject")
Set txtfile = fso.createtextfile("c:\Windows\Desktop\qrt.txt", True)

For x = 1 To 5
txtfile.write ("new Array" & Chr(40) & Chr(34) & Cells(x, 1) & Chr(34) & vbNewLine)
Next x

End Sub

If I execute this code then the output would look like:

new Array("48"
new Array("49"
new Array("50"
new Array("51"
new Array("52"

48 till 52 are the numbers that are given in cells A1 till A5. I know how to reproduce the
number of lines but how do you create a loop to reproduce the number of cell values. Because
sometimes my excelworksheet contains data for a range of e.g. 36 rows and 56 colums or
e.g. 5 rows and 20 colums or any other number. Does anyone know how to create the second loop.

[jjortiz]

Why not just read in the values from the Excel Spread sheet. Use the Excel object. Or are you trying to place the values from the text file to the spread sheet?

[visualjoris]

No, what I want to is publish the values from of the excelsheet on the internet. That is why I want to create javascipt commands into a textfile. You can also use the build-in microsoft add-in for creating a table into a HTML file but then if you want to create 32 HTML files with all different tables of different sizes then you cannot use this add-in because the macro recorder does not give you the code.So you cannot automate this in a simple way. So the only thing I need is the text that is quoted above because that is the part of the javascript that is for each table different.
So what I want to know is how to create e.g. the following text:
new Array("48","85"),
new Array("49","45"),
new Array("50","78"),
new Array("51","2"),
new Array("52","90")

This must be flexible for the number of lines. So above you see 5 lines and two cell values but it can also be 7 lines and 5 cell values (I mean by that a table with 35 values).So I want to know how the change the sub below that it is flexibel for both.


Sub create_file()
Dim fso, txtfile
Set fso = CreateObject("scripting.filesystemobject")
Set txtfile = fso.createtextfile("c:\Windows\Desktop\qrt.txt", True)

For x = 1 To 5
txtfile.write ("new Array" & Chr(40) & Chr(34) & Cells(x, 1) & Chr(34) & vbNewLine)
Next x

End Sub

[WorkHorse]

Is this what you are looking for?

Dim intNumberOfRows As Integer
Dim intNumberOfColumns As Integer
Dim strLineText As String
 
intNumberOfRows = 5
intNumberOfColumns = 2
 
For x = 1 To intNumberOfRows
    For y = 1 To intNumberOfColumns
        strLineText = strLineText & Chr(34) & Cells(x, y) & Chr(34) & ","
    Next y
    txtfile.write ("new Array" & Chr(40) & strLineText & vbNewLine)
Next x

[WorkHorse]

Opps. You need to clear your string each time:

Dim intNumberOfRows As Integer
Dim intNumberOfColumns As Integer
Dim strLineText As String
 
intNumberOfRows = 5
intNumberOfColumns = 2
 
For x = 1 To intNumberOfRows
    
    strLineText = ""
    For y = 1 To intNumberOfColumns
        strLineText = strLineText & Chr(34) & Cells(x, y) & Chr(34) & ","
    Next y
    
    strLineText = "new Array(" & Left$(strLineText, Len(strLineText) - 1) & ")"
    If x < intNumberOfRows Then strLineText = strLineText & ","
    strLineText = strLineText & vbNewLine
    
    txtfile.write strLineText
    
Next x

[visualjoris]

Thank you WorkHorse you have solved my problem.