Thread: Printing spreadsheet results to a text file

Hello,

I'm trying to do something very simple, but I haven't had any luck searching for a function in excel that will let me do it.

Column A is a list of directories on a server, i.e. 01april, 02april, etc.
Column B is the path to those main directories, i.e. /raid0/data/documents/april/
Column C is where users can can type "1" if they want the directory or leave it blank if they do not want it selected.
Column D, if the corresponding row in column C=1, will show the complete source path/filename based on Column A and Column B.
Column E, if the corresponding row in column C=1, will show the complete destination path/filename based on Column A and Column B.

This is all working fine.

What I want to be able to do now though, is somehow, when someone sends the document back to me, create an FTP queue. I can do this with BulletProof FTP, because it allows really simple text queues.

So, what I need excel to do is, when someone has finished selecting their files, is write the results to a text file like exactly like this (without the Line1:, etc.):

Line1: <text from D2>
Line2: 0
Line3: 0
Line4: 0
Line5: ?
Line6: <text from E2>
(then repeated for each row in the excel document until column A is returning blank)

Seems simple enough.. Any ideas?

Also, an extra question--> is there any way I can make Excel list a directory structure automatically? Could it display the file size for the entire directory? I don't necessarily want it to list every file, just the directory paths and size of each directory.

Thank you!

You can create the ftp instruction file by using I/O commands.

Code:

Sub MakeFTP()
    Dim intUnit As Integer
    Dim lngRow As Long
    Dim strFileName As String
    
    strFileName = "C:\ftp.txt"
    intUnit = FreeFile
    Open strFileName For Output As intUnit
    
    lngRow = 2
    Do While Len(Cells(lngRow, 1).Value) > 0
        Print #intUnit, Cells(lngRow, 4).Value
        Print #intUnit, "0"
        Print #intUnit, "0"
        Print #intUnit, "0"
        Print #intUnit, "?"
        Print #intUnit, Cells(lngRow, 5).Value
        lngRow = lngRow + 1
    Loop
    Close intUnit
    
End Sub

Excel will not automaticaly create a detailed list of information about a directory. You can however use VBA to do it for you.

This very simple routine will list all .xls files in a given folder.

Code:

Sub DirInfo()
    
    Dim lngRow As Long
    Dim strPath As String
    Dim strFile As String
    
    lngRow = 1
    strPath = "C:\temp\"
    strFile = Dir(strPath & "*.xls")
    Do While Len(strFile) > 0
        Cells(lngRow, 1) = strFile
        lngRow = lngRow + 1
        strFile = Dir
    Loop
    
End Sub

If you really do require more information about a file you can use the FileSystemObject to search for files.

Code:

Sub DirInfoFull()
'
' Requires reference to Microsoft Scripting Runtime
' C:\WINDOWS\SYSTEM32\scrrun.dll
'
    Dim fsoTemp As New FileSystemObject
    Dim fsoFiles  As Files
    Dim fsoFile As File
    Dim lngRow As Long
    
    Set fsoTemp = New FileSystemObject
    Set fsoFiles = fsoTemp.GetFolder("C:\temp").Files
    
    lngRow = 1
    For Each fsoFile In fsoFiles
        Cells(lngRow, 2) = fsoFile.Name
        Cells(lngRow, 3) = fsoFile.Size
        Cells(lngRow, 4) = fsoFile.DateLastModified
        lngRow = lngRow + 1
    Next
    
End Sub

Andy,

Might

Code:

    Dim fsoTemp As New FileSystemObject

better be

Code:

    Dim fsoTemp As FileSystemObject

Yes. No need to double up on the New syntax.
I copied the declaration bit from a previous post and never noticed the 2 New's.

Just add a conditional test,

Code:

Sub MakeFTP()
    Dim intUnit As Integer
    Dim lngRow As Long
    Dim strFileName As String
    
    strFileName = "C:\ftp.txt"
    intUnit = FreeFile
    Open strFileName For Output As intUnit
    
    lngRow = 2
    Do While Len(Cells(lngRow, 1).Value) > 0
        if cells(lngrow,3).Value = 1 then
           Print #intUnit, Cells(lngRow, 4).Value
           Print #intUnit, "0"
           Print #intUnit, "0"
           Print #intUnit, "0"
           Print #intUnit, "?"
           Print #intUnit, Cells(lngRow, 5).Value
        end if
        lngRow = lngRow + 1
    Loop
    Close intUnit
    
End Sub