RE: opening, modifying, and saving an existing xls workbook in rpg

If you are needing to read the Windows directory on the PC (as opposed to
getting to it via /QNTC), then this little bit of VB code will do it for you
and output the results to a text file.

This is VB6 code...probably an easier (i.e. less code) way to do it with
.NET but I've not needed to do this for a long time so I've never rewrote it
in .NET.

Someone else (Vern maybe) could probably show you the .Net version of this.

HTH

Shannon O'Donnell


Option Explicit

Private Sub Command1_Click()
Dim ff As Integer
Dim s As String

ff = FreeFile
Open "C:\DirContentsList.txt" For Output As #ff
s = FindFiles("C:\MYDIR", "C:\MYDIR")
Print #ff, s
Close #ff
Label1.Visible = True
End Sub

Private Function FindFiles(ByVal sSearchDir As String, ByVal Path As String)
As String
'returns a string of all the files and dirs in the sSearchDir as follows:
'(sSearchDir|file,file,file,file)
'where file refers to both files and other dirs. If it is another dir,
'then it will *also* be in this format (dir|file,file,etc)
'path is path to directory being searched, including trailing "\"
Dim aList() As String
Dim nDir As Integer
Dim i As Integer
Dim s As String
Dim fn As String

If Right$(Path, 1) <> "\" Then Path = Path & "\"

'first, find all directories and files
fn = Dir$(Path & "*.*", vbDirectory) 'will find directories AND normal
files (not system though)
While Len(fn)
If (fn <> "..") And (fn <> ".") Then
If GetAttr(Path & fn) And vbDirectory Then 'dir
ReDim Preserve aList(nDir)
aList(nDir) = fn
nDir = nDir + 1
Else 'file
' s = s & fn + vbCrLf
s = s & fn + vbCrLf
End If
End If
fn = Dir$
Wend

'second, recursively search any directories found
For i = 0 To nDir - 1

s = s & "," & FindFiles(aList(i), Path & aList(i) & "\")
Next i
FindFiles = Mid$(s, 2)
Erase aList
End Function





-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of tim
Sent: Wednesday, January 07, 2009 11:00 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: opening, modifying, and saving an existing xls workbook in rpg

Can someone point me to any examples of opening, modifying, and saving an
existing xls workbook. I would want to:



Open the workbook

Open a sheet

Remove existing rows from a certain point on

Add new rows from certain point on

Save sheet

Open next sheet and repeat





I have been working with Scott K HSSF stuff, but have only been creating new
workbooks, not updating existing.



Thanks