From an 2-D array into file 1

[krzysrb]

Hello guys,

Does any of you know any good tutorials or any samples where I can learn how to put a 2-dimensional array into a .txt file. Originally, I intended to put the array into .xls[Excel] file, but some friends of mine told me it is not possible. Do you know of any good references where I can look for help on this?

Thanks a lot
vlada

 

[johnwm]

The usual way to store straightforward text as a 2-dimensional array in a text file would be as a comma-separated list (or pipe-separated, or tab-separated list). That is a list where each row (or record) has the individual strings marked apart with a known character (which would not otherwise appear in your text)

For instance a set of names and addresses could be stored as:

Fred|Smith|1|High Street|Maldon
Joe|Bloggs|Oaklea|Hanger Road|Heybridge

using the pipe symbol as a delimiter (as you might find a comma in a text field in an address).

If you use a comma separated list you can recover the text via the ODBC text driver, but for any separater you can open the file, read it all in and use the Split function to get back the original values.

If you want to do this from Excel, just Save As, and choose .CSV as the filetype. For a normal 2 dimensional array you could also use a spreadsheet or a database

For broader help do a Google on 'comma separated values' or 'comma separated lists'

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[krzysrb]

johnwm,

I had made a progress, learned about Put statement, and I have done this to my array:

Open ExpPath For Random As #1

For i = 1 To 7
For j = 1 To 30
Put #1, , Schedule(i, j)
Next j
Next i

Close #1

I am not sure if I used comma or pipe seperation, nor if this what I have is the best way of doing it.

However, it is something else that puzzles me, and that is, if there is a way to get this (array) into Excel spreadsheet? My program doesnt have to be too fast, so even if I have to do it from few steps, it would be ok, as long as I can get it into Excel.

Thanks in advance
vlada

 

[KaySyd]

If your client has Excel loaded on his computer, you can access Excel directly by using the Excel.Application object, open your file, and place your data in the worksheet. If you are not sure what code to use from the Excel.Application object, record a macro in excel mimicking what and then learn the techniques by looking at the macro code.

The following is code I tested w/ VB6 for Excel XP (should be backward compatible to Excel 97). This opens a file name "exchange.xls" and inserts the phrase "VB Rocks" into cell A12 of the worksheet "Exchange".

Dim myExcel As Object, myWorkBook As Object, myWorkSheet As Object
Set myExcel = CreateObject("Excel.Application&quot
'myExcel.Visible = True
Set myWorkBook = myExcel.Workbooks.Open("C:\Exchange.xls&quot
Set myWorkSheet = myWorkBook.Sheets("Exchange&quot
'Next line inserts text into cell "A12"
myWorkSheet.Cells(1, 12).Value = "VB Rocks"
myWorkBook.Close True ' Saves changes and closes
Set myWorkSheet = Nothing
Set myWorkBook = Nothing
Set myExcel = Nothing

You should be able to use this to insert your data from your array. You can also format the cell based on criteria of your data.

Good Luck, it is a lot of fun.

 

[krzysrb]

KaySyd,
a * for you for help. I got it working, it runs well. The only thing though, you said it works with XP, but for me it works only with Excel 97. However, it works.

The next question that I have is about the spreadsheet and workbook. Would I be able to create them on fly, rather than having them pre-created, so the user is able to name them as they wish? I would perhaps need to be using some other method that creates Excel 97 file before I export the data to it?

Thanks a lot for your help
vlada