0

I have written txt-files with excel and a macro in the past several times. I didn't hit 10000 lines or more. Never say Never...

My .csv file has over 87000 rows like that example "15k50,CityABC,56ab,CountryofCity,ID,Street". I use the Split() function in order to separate the values. The Macro formated and wrote the values as single lines to the txt file.

Around 9800 lines the txt-file closed... But why? I tried with Slepp() to make sure the print algho isn't overloaded or something else.

The counter 10000 is there because I want to make it easer to understand for you. If it goes over 10000 the problem is "solved".

Information txt-File format:

  • ASCII
  • Unix (LF)

Shortcut, after several comments

  • with Minimal, Reproducible Example overworked the code (deleted sleep, simplification variable names, try to make code from scratch)
  • changed SplitString() to Split(), because call over function is stupid...
  • after printed line 9000 to the txt-file the following error popup "Run-Time Error 5: Invalid Procedure Call or Argument" at code line fso.WriteLine ("# " & strArr(0) & " # " & strArr(1) & ...
Option Explicit

#If VBA7 Then
 Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
 Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If

Sub formattedToTxt()

Dim strArr() As String
Dim strB As String
Dim intC As Integer

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

strB = filePathExport & "\" & filenameExport & fileFormatExport
Set fso = fso.CreateTextFile(strB, True)

Do While counter <= 10000
    strArr = Split(ActiveCell.Value, ",")
    intC = CalcWhitespace(strArr(5), 40)
    fso.WriteLine ("#  " & strArr(0) & "  #  " & strArr(1) & "  # " & strArr(2) & " # " & strArr(3) & "  # " & strArr(4) & " # " & strArr(5) & Space(intC) & "#")
    ActiveCell.Offset(1, 0).Select
    If ((counter Mod 1000) = 0) Then
        Debug.Print ("Entry " & counter & " written")
    End If
    counter = counter + 1
Loop

End Sub

Function CalcWhitespace(rawStr As String, maxLen As Integer) As Integer
    CalcWhitespace = maxLen - Len(rawStr)
End Function

Any Idea?

EngineerTrooper
  • 27
  • 6
  • Which error do you get and where in the code? What is your code supposed to do and what does it instead? Actually it cannot run like it is there is no sub or function (see [mcve]). Note that if you put a `Sleep 1000` in a loop with 10000 that is a total of 2.77 hours of just waiting! • Why don't you just fill a worksheet and use the safe as text feature in Excel? • Your question is pretty unclear. – Pᴇʜ Jan 25 '21 at 13:16
  • What do you mean with *"the txt-file closed"*? Program continued to run but no data written? Program stopped? Any runtime error? Forget about the `sleep`, that's not leading to anywhere. And why are you working with `Activecell` and `Select`? – FunThomas Jan 25 '21 at 13:18
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jan 25 '21 at 13:19
  • @Pᴇʜ Sleep is a function and the unit is milli second, 1000ms = 1s I can't the the feature safe as text because the output format printed to the txt file is diffrent. I try to use [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example), thx for tipp – EngineerTrooper Jan 25 '21 at 13:25
  • How `CalcWhitespace` function does look? Does `SplitString` something different than `Split'? – FaneDuru Jan 25 '21 at 13:30
  • @FunThomas the txt-file close itself. I didn't wrote a close command. The Macro stopped with the error Message *Run-Time Error 5: Invalid Procedure Call or Argument* – EngineerTrooper Jan 25 '21 at 13:35
  • 2
    @EngineerTrooper In which line of the code do you get the error? – Pᴇʜ Jan 25 '21 at 13:37
  • @Pᴇʜ at the line "fso.WriteLine ("# " & strTempArr(0) & " # " & strTempArr(1) & ..." – EngineerTrooper Jan 25 '21 at 13:38
  • Use an intermediate variable to store the string before writing it: Is there anything special when the error occurs? Maybe the length of the string? Check the value of `intTemp`. BTW: omit the parentheses at `WriteLine` and `Debug.Print` (or use `Call`) – FunThomas Jan 25 '21 at 13:55
  • Does it stop all the time on the same `counter` value? What does `CalcWhitespace` return in case of no spaces needing? Is it designed to return a `Long` should it be `0` (zero)? Is it possible to return a negative number? Please, edit your question and paste the `CalcWhitespace` function code, too. – FaneDuru Jan 25 '21 at 13:55
  • I am afraid of `Space(negative numbmer)`, which creates the problem... – FaneDuru Jan 25 '21 at 14:07
  • @FaneDuru I checked it three times. The Macro Error pop up if line 9000 was written to the txt-file. I copyed ```CalcWhitespace``` to the code above. Also I changed the ```SplitString()``` to ```Split()``` instead, because that function call was stupid... – EngineerTrooper Jan 25 '21 at 14:09
  • I think, there is the problem. If `Len(rawString)` is bigger than `maxLength`, it will return a negative number and the error comes from `Space(negative numbmer)`. Please, try inserting a code line in the function, just before `CalcWhitespace = maxLength - Len(rawString)`: `if Len(rawString) > maxLength Then Stop`. You may have the surprise to find the code stopped there... – FaneDuru Jan 25 '21 at 14:18
  • Now, why this `maxLength` of 40 is necessary? What can you do if my supposition is correct? Can it be increased for all the range? Should `rawString` be trucate to a maximum length? – FaneDuru Jan 25 '21 at 14:25
  • Besides that, your long 'sausage' line can be shorten using `fso.WriteLine ("# " & Join(strTempArr, " # ") & space(intTemp) & "#")`. And all the code adapted to avoid selection. I tried showing you in my answer, but you did not react in any way. I deleted it when I started to understand that the real problem must be searched in a different place... – FaneDuru Jan 25 '21 at 14:34
  • @FaneDuru sry for that. I currently working on a solution with the tips of you all. The 40 is a part of a problem. And yes, I got a negative number for ```intTemp``` after short overwork now called ```intC``` at the code above. I tested it with 80 instead and the macro goes over 10000 without a problem. The Value in strArr(5) was to long... Thanks for your help! – EngineerTrooper Jan 25 '21 at 14:47
  • I will post a clean code solution if I cleaned it up! Thx all you again! – EngineerTrooper Jan 25 '21 at 14:49
  • Should I undelete my answer to just copy from there the solutions I suggested? No need to vote it up. I do not care about reputation... I am glad helping you. – FaneDuru Jan 25 '21 at 14:50

1 Answers1

1

Thanks to all helpful VBA people from the comments above. I cleaned up the code. The following snipped is the full solution.

The .csv table contains diffrent strings with diffrent length in each line. In the final solution the whitespaces are checked before. It is importend to know the max-Length strings at the data. To format the txt-file output readable.

Maybe, an other solution has a better performance, but that works well in my case.

Have a great day!

'Find max string length for each whitespace
counter = 0
ActiveSheet.Cells(2, 1).Select   'ignore Headlinedata, because diffrent format in compare to data
intC = (UBound(arrWhitespace) - LBound(arrWhitespace))
Do While ActiveCell.Value <> ""
    strArr = Split(ActiveCell.Value, ",")
    For counterTwo = 0 To intC
        If Len(strArr(counterTwo)) > arrWhitespace(counterTwo) Then arrWhitespace(counterTwo) = Len(strTempArr(counterTwo))
    Next counterTwo
    counter = counter + 1
    ActiveCell.Offset(1, 0).Select
    If ((counter Mod 1000) = 0) Then
        Debug.Print ("Entry " & counter & " checked")
    End If
Loop

'Print Body of txt-file
Do While ActiveCell.Value <> ""
    strArr = Split(ActiveCell.Value, ",")
        
    'build string for each line
    strB = ""
    strB = strB & "#  " & strArr(0) & "  #"
    intC = CalcWhitespace(strArr(1), arrWhitespace(1))
    strB = strB & "  " & strArr(1) & Space(intC) & "  #"
    intC = CalcWhitespace(strArr(2), arrWhitespace(2))
    strB = strB & " " & strArr(2) & Space(intC) & " #"
    intC = CalcWhitespace(strArr(3), arrWhitespace(3))
    strB = strB & " " & strArr(3) & Space(intC) & "  #"
    intC = CalcWhitespace(strArr(4), arrWhitespace(4))
    strB = strB & " " & strArr(4) & Space(intC) & " #"
    intC = CalcWhitespace(strArr(5), arrWhitespace(5))
    strB = strB & " " & strArr(5) & Space(intC) & "  #"
    fso.WriteLine (strB)
    ActiveCell.Offset(1, 0).Select
    If ((counter Mod 1000) = 0) Then
        Debug.Print ("Entry " & counter & " written")
    End If
    counter = counter + 1
Loop

Function CalcWhitespace(rawStr As String, maxLen As Integer) As Integer
    CalcWhitespace = maxLen - Len(rawStr)
End Function

Next time I will use minimal-reproducible-example to avoid .Select

EngineerTrooper
  • 27
  • 6