I am looking to for a standard Userform and accompanying code that will
allow the user to save or load a txt file whose contents reflect data
for a sheet.

The sheet would contain 6 columns of strings.

(in my case, 6 columns of R1C1 strings)

Example txt file

R1C23 , R5C34, R66C2, R2C2, R89C2, R33C34
R1C24 , ...
R5C29 ,...
R4C4 ,...
R39C195 ,...
..
..
..

The VBA code would take each address from the txt file and write it a
column in the sheet for loading, and for saving would take each address
from the columns and write them to the txt file.

I believe I could write all the code and create the Userform (I have
Walkenbach's Power Programming to guide me), but I am sure people have
created something similar before. Anyone have a good place to start (or
some code that does something similar)? It'd be nice to not have to
start from scratch. Also, any pointers on things to watch out for?

I am programming for Windows computers only, so no need to worry about
Mac's different syntex for directories.

This will get you a long way.
I have run it on a text file with:
A1,B1,C1
A2,B2,C2
and it works fine, but you may need some altering to make it work with the
RC notation.


Function OpenTextFileToArraySplit(txtFile As String, _
arr As Variant, _
LBCol As Long) As Long

'this is used for filling 2-D arrays when the number of rows is unknown
'elements must be separated by commas and rows by vbCrLf
'it will return the number of rows
'----------------------------------------------------------------------

Dim str As String
Dim arrOld
Dim arrTemp1
Dim arrTemp2
Dim LR As Long
Dim LR2 As Long
Dim R As Long
Dim c As Long

On Error GoTo ERROROUT

'get the whole file in string
'----------------------------
str = OpenTextFileToString2(txtFile)

'split the rows
'--------------
arrTemp1 = Split(str, vbCrLf)

LR = UBound(arrTemp1)

'get the real last row
'---------------------
For R = 0 To LR
If InStr(1, arrTemp1(R), Chr(44), vbBinaryCompare) 0 Then
LR2 = LR2 + 1
End If
Next

'fill the supplied array
'-----------------------
For R = 0 To LR2 - 1
'split the columns
'-----------------
arrTemp2 = Split(arrTemp1(R), Chr(44))
If R = 0 And c = 0 Then
ReDim arr(LBCol To (LR2 - (1 - LBCol)), _
LBCol To UBound(arrTemp2) + LBCol)
End If
For c = 0 To UBound(arrTemp2)
arr(R + LBCol, c + LBCol) = _
Replace(arrTemp2(c), """", "", 1, -1, vbBinaryCompare)
Next
Next

OpenTextFileToArraySplit = LR

Exit Function
ERROROUT:

arr = arrOld
OpenTextFileToArraySplit = -1

End Function


Function OpenTextFileToString2(ByVal strFile As String) As String

Dim hFile As Long

hFile = FreeFile

Open strFile For Input As #hFile

OpenTextFileToString2 = Input$(LOF(hFile), hFile)

Close #hFile

End Function

Sub SaveArrayToText(ByVal txtFile As String, _
ByRef arr As Variant, _
Optional ByVal LBRow As Long = -1, _
Optional ByVal UBRow As Long = -1, _
Optional ByVal LBCol As Long = -1, _
Optional ByVal UBCol As Long = -1, _
Optional ByRef fieldArr As Variant)

Dim R As Long
Dim c As Long
Dim hFile As Long

If LBRow = -1 Then
LBRow = LBound(arr, 1)
End If

If UBRow = -1 Then
UBRow = UBound(arr, 1)
End If

If LBCol = -1 Then
LBCol = LBound(arr, 2)
End If

If UBCol = -1 Then
UBCol = UBound(arr, 2)
End If

hFile = FreeFile

Open txtFile For Output As hFile

If IsMissing(fieldArr) Then
For R = LBRow To UBRow
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, arr(R, c)
Else
Write #hFile, arr(R, c);
End If
Next
Next
Else
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, fieldArr(c)
Else
Write #hFile, fieldArr(c);
End If
Next
For R = LBRow To UBRow
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, arr(R, c)
Else
Write #hFile, arr(R, c);
End If
Next
Next
End If

Close #hFile

End Sub


Sub test()

Dim i As Long
Dim c As Long
Dim arr

OpenTextFileToArraySplit "C:\RangeTest.txt", arr, 1

For i = LBound(arr) To UBound(arr)
For c = LBound(arr, 2) To UBound(arr, 2)
arr(i, c) = Range(arr(i, c))
Next
Next

SaveArrayToText "C:\RangeTest.txt", arr

End Sub


RBS


"Abe" wrote in message
oups.com...
I am looking to for a standard Userform and accompanying code that will
allow the user to save or load a txt file whose contents reflect data
for a sheet.

The sheet would contain 6 columns of strings.

(in my case, 6 columns of R1C1 strings)

Example txt file

R1C23 , R5C34, R66C2, R2C2, R89C2, R33C34
R1C24 , ...
R5C29 ,...
R4C4 ,...
R39C195 ,...
.
.
.

The VBA code would take each address from the txt file and write it a
column in the sheet for loading, and for saving would take each address
from the columns and write them to the txt file.

I believe I could write all the code and create the Userform (I have
Walkenbach's Power Programming to guide me), but I am sure people have
created something similar before. Anyone have a good place to start (or
some code that does something similar)? It'd be nice to not have to
start from scratch. Also, any pointers on things to watch out for?

I am programming for Windows computers only, so no need to worry about
Mac's different syntex for directories.