File Spec

[Victor Moss]

I'm trying to create a file spec from a spreadsheet, although I'm constantly getting an "Invalid Procedure Call or Argument" error,

Please can someone help!?

Sub Macro3()



Dim columno As Integer
Dim rowno As Integer
Dim BlankValue As String
Dim BUFFER As String
Dim done As Boolean
Dim fpath As String
Dim Acdate As String
Dim FName As String
Dim msg As String
Dim CountRec As Integer
Dim TAmount As Integer


On Error GoTo errorhandler


    FName = "C:\TEST\" & InputBox("Please enter a file name.") & ".txt" ' & Format(Trim(Left(fpath, 8)), ">") & ".txt"
    Open FName For Output As #1
    
    CountRec = 0
    TAmount = 0
    
    Range("A3").Select
    rowno = 2
  
    Do Until done
    
     rowno = rowno + 1
        Range("A" & rowno).Select
         
        If ActiveCell.Value = "" Then
            done = True
            Exit Do
            
        Else
        'check if the value is zero
            Range("B" & rowno).Select
            If Val(ActiveCell.Value) = 0 Then    'NOTHING
                       
            Else
            
                CountRec = CountRec + 1

               
 '1-9  Transaction Number
                BlankValue = 8 - Len(BUFFER)
                BUFFER = BUFFER & Space(BlankValue)
                BlankValue = 9 - Len(CountRec)
                BUFFER = BUFFER & Space(BlankValue) & Format(CountRec, "00000")

                
'10-13  Method of Payment
                BlankValue = 9 - Len(BUFFER)
                BUFFER = BUFFER & Space(BlankValue) & "2  "

               
 '13-15  Account Type
                BlankValue = 12 - Len(BUFFER)
                BUFFER = BUFFER & Space(BlankValue) & "1  "

               
 '16-23  From Branch code - A
                BlankValue = 15 - Len(BUFFER)
                BUFFER = BUFFER & Space(BlankValue)
                Range("A" & rowno).Select
                BlankValue = 6 - Len(Trim(Left(ActiveCell.Value, 6)))
                BUFFER = BUFFER & Space(BlankValue) & Trim(ActiveCell.Value)

               
 '24-44  From Account number - B
                BlankValue = 23 - Len(BUFFER)
                BUFFER = BUFFER & Space(BlankValue)
                Range("B" & rowno).Select
                BlankValue = 21 - Len(Trim(Left(ActiveCell.Value, 21)))
                BUFFER = BUFFER & Space(BlankValue) & Trim(ActiveCell.Value)

               
 '45-66  To Name - C
                BlankValue = 44 - Len(BUFFER)
                BUFFER = BUFFER & Space(BlankValue)
                Range("C" & rowno).Select
                BlankValue = 22 - Len(Trim(Left(ActiveCell.Value, 22)))
                BUFFER = BUFFER & Format(Trim(Left(ActiveCell.Value, 22)) & Space(BlankValue), ">")

               
 '67-79  Amount - D
                BlankValue = 66 - Len(BUFFER)
                BUFFER = BUFFER & Space(BlankValue)
                Range("D" & rowno).Select
                BlankValue = 13 - Len(Format((ActiveCell.Value), "##############0.00")) 'BlankValue = 13 - Len(Trim(ActiveCell.Value))
                BUFFER = BUFFER & Space(BlankValue) & Format(Trim(Trim(Val((ActiveCell.Value)))), "##############0.00") 'BUFFER = BUFFER & Space(BlankValue) & Trim(ActiveCell.Value) '(ActiveCell.Value)
                                
                TAmount = TAmount + Trim(ActiveCell.Value)

                
'80-96  To Description on Statement - E
                BlankValue = 79 - Len(BUFFER)
                BUFFER = BUFFER & Space(BlankValue)
                Range("E" & rowno).Select
                BlankValue = 17 - Len(Trim(Left(ActiveCell.Value, 20)))
                BUFFER = BUFFER & Format(Trim(Left(ActiveCell.Value, 20)) & Space(BlankValue), ">")

               
 '97  Print - ("N" for Ad-Hoc)
                BlankValue = 96 - Len(BUFFER)
                BUFFER = BUFFER & Space(BlankValue) & "N"
                
                'MsgBox BUFFER
                Print #1, BUFFER
                
        End If
        End If
       
    Loop
    
errorhandler:
    
     If Err.Number <> 0 Then
  
    MsgBox ("There is an Error in record " & rowno)
          
    msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
    MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
    
      Close #1
      Kill FName
      Exit Sub
          
    End If

'Resume Next
End Sub

 

[Rosen]

I am not entirely sure what your trying to do here, but the Space() function cannot have a negative value passed as a parameter. Try checking BlankValue before passing it into the Space function and if negative (and appropriate to do so) change it to zero. Also, are you trying to write an exponentially longer file for each row?

To clarify:

the BUFFER variable never gets reset, therefore each row data is simply added to the end of the BUFFER string so if Row 1 created a BUFFER abc and Row 2 was to be 123 by the end of row 2 the BUFFER would abc123 and your file would contain :

abc
abc123

and so on.

 

[Victor Moss]

Hi Rosen,

I am trying to create text file spec from a spreadsheet. I see that the Space() function has a -4 value, and pardon me, this is the first time I am working with this type of macro, but how could you determine that there was a negative value being passed through as a parameter?

 

[Rosen]

First, to answer your question, the first row this code would be run on the BUFFER would be a null string (the default value of all undeclared string variables) so the Len function would return 0, and 8 minus 0 is 8 meaning the BlankValue, when used as a parameter in Space, will return a string of 8 spaces which is assigned to BUFFER. Then 9 minus the length of CountRec (which will have a value of 1) will be 1 character meaning the result of BlankValue would be 8 now, so Space would return another 8 spaced string, concatenate that with the previous 8 spaced string would mean BUFFER is now a 16 spaces string then you concatenate it with "00001" (our CountRec of 1 formatted with 4 0s in front). By the end of the first three lines after your comment "'1-9 Transaction Number" you would have a BUFFER of 21 characters. Then you take 9 subtract the length of the BUFFER (21) and then you pass the result in Space, this value would be -12 (our negative parameter).

Now, I'm still not sure I understand what your trying to do. When you said 'File Spec' I read File Specification, which to me would be the document which describes the layout/structure of a file, but it seems to me you are trying to put content into this text file in a specific layout/structure. Maybe if you provided what you expected the first couple of lines of the text file to look like (using some other character then space in place of the spaces for readability) we can look at ensuring the code is doing what you intend it to do.

 

[Victor Moss]

I mean to say, File Spec, a text file populated from Excel, as per a specific file specification. The content of the text file should look like this:

STARPLEX AA11 SALARY - 21 0 LIVE 2014 11 12 01
VICTOR1 2 1 250655 0000000062352001160 VICTOR MOSS 00000010000 SALARY N

I have not yet figured out how to create the first line (batch header), I'm currently working on creating the body (in bold). This is the file spec:

Batch Header
Field	Length	Position	Value	Description
1	42	1	STARPLEX	Contra Account Name
2	6	43	AA11	Company Code
3	19	49	SALARY	User Reference
4	3	68	"+/-"	Credit/Debit Indicator
5	4	71	81	Class of Entry
6	3	75	0	Tax Code
7	6	78	LIVE	Run Type
8	14	84	2014 11 08	Date
9	2	98	01	Batch Number
Transactions / Body
Field	Length	Position	Value	Description
1	9	1	VICTOR1	Transaction Number
2	3	10	2	Method of Payment	2 = Bank Transfer
3	3	13	1	Account Type	1 = Current
4	8	16	250655	Branch Code
5	21	24	0000000062352001160	Account Number
6	22	45	VICTOR MOSS	Account Name
7	13	67	00000010000	Amount
8	17	80	SALARY	Beneficiary Statement Reference
9	1	97	N	RTGS Indicator

<colgroup><col width="64" style="width: 48pt;" span="3"> <col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;"> <col width="217" style="width: 163pt; mso-width-source: userset; mso-width-alt: 7936;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <tbody> </tbody>

I saw this macro method once, so I'm trying to use it. Am I on the right path?

 

[Rosen]

Assuming I am understanding you correctly this amended code should do what you've requested.

Function BUFFER(ByVal Value As String, Length As Long, Optional Front As Boolean = False, Optional BuffChar As String = " ") As String
    If Len(Value) > Length Then Err.Raise 2001, "BUFFER UDF", "Value exceeds length requirements"
    If Len(Value) = Length Then
        BUFFER = Value
        Exit Function
    End If
    If Front Then
        Value = BuffChar & Value
    Else
        Value = Value & BuffChar
    End If
    BUFFER = BUFFER(Value, Length, Front, BuffChar)
End Function

Sub Macro3()
    Dim columno As Integer
    Dim rowno As Integer
    Dim BlankValue As String
    Dim Line As String
    Dim done As Boolean
    Dim fpath As String
    Dim Acdate As String
    Dim FName As String
    Dim msg As String
    Dim CountRec As Integer
    Dim TAmount As Integer
    Dim File As Integer
        
    On Error GoTo errorhandler
    FName = "C:\TEST\" & InputBox("Please enter a file name.") & ".txt" ' & Format(Trim(Left(fpath, 8)), ">") & ".txt"
    File = VBA.FreeFile
    Open FName For Output As File
    
    CountRec = 0
    TAmount = 0
    
    Range("A3").Select ' not sure what the point of this is?!?
    rowno = 2
  
    Do Until done
    
     rowno = rowno + 1
        Range("A" & rowno).Select
         
        If Selection.Value = "" Then
            done = True
            Exit Do
            
        Else
        'check if the value is zero
            Range("B" & rowno).Select
            If Val(Selection.Value) = 0 Then    'NOTHING
            
            Else
            
                CountRec = CountRec + 1
                
                ' I have no clue what this stuff is supposed to be so I have left it in as comments...
' '1-9  Transaction Number
'                BlankValue = 8 - Len(BUFFER)
'                BUFFER = BUFFER & Space(BlankValue)
'                BlankValue = 9 - Len(CountRec)
'                BUFFER = BUFFER & Space(BlankValue) & Format(CountRec, "00000")
'
'
''10-13  Method of Payment
'                BlankValue = 9 - Len(BUFFER)
'                BUFFER = BUFFER & Space(BlankValue) & "2  "
'
'
' '13-15  Account Type
'                BlankValue = 12 - Len(BUFFER)
'                BUFFER = BUFFER & Space(BlankValue) & "1  "
                
                Line = BUFFER(Range("A" & rowno).Text, 9)
                Line = Line & BUFFER(Range("B" & rowno).Text, 3)
                Line = Line & BUFFER(Range("C" & rowno).Text, 3)
                Line = Line & BUFFER(Range("D" & rowno).Text, 8)
                Line = Line & BUFFER(Range("E" & rowno).Text, 21)
                Line = Line & BUFFER(Range("F" & rowno).Text, 22)
                Line = Line & BUFFER(Range("G" & rowno).Text, 13)
                Line = Line & BUFFER(Range("H" & rowno).Text, 17)
                Line = Line & BUFFER(Range("I" & rowno).Text, 1)
                MsgBox Line
                Print #File, Line
            End If
        End If
    Loop
    Close File
errorhandler:
     If Err.Number <> 0 Then
        MsgBox "There is an Error in record " & rowno
        msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
        MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
        Close File
        Kill FName
    End If
'Resume Next
End Sub

Always remember to backup your work before running new code.

Hope this helps!

 

[Victor Moss]

WOW! That makes things a whole lot easier for me to understand, plus I can play with the spec a lot simpler.

I only have 3 more questions, and I'm sorry to do this, but;

1. Only some columns' text in notepad should be justified to the right, how would I do this?
2. Whatever spaces weren't used, how can I replace them with "0" in notepad?
3. How would I run a macro to start writing to row 2 in notepad?

If I could understand these, I could build some pretty useful VB Apps.

Your help is being greatly appreciated.

Thank you!

 

[Rosen]

1) If you want to change the direction of the buffer set the Front parameter to True (example: Line = Line & BUFFER(Range("E" & rowno).Text, 21, True))
2) If you want the change what character is used to buffer a string change the BuffChar parameter (example: Line = Line & BUFFER(Range("E" & rowno).Text, 21, True, "0"))
3) Print #File, "" placed before the loop but after you open the file.

 

[Victor Moss]

Thank you so much, everything works perfectly. I still need to build the header though, but I'm pretty sure from here I can figure it out. The only problem I still have, I added Print #File, "" as you said, although it then prints to every 2nd row in notepad, I need it to only start on row 2, then every row after that. I've tried playing around with it with no luck.

 

[Rosen]

Put it right after the line

Open FName For Output As File