Why is my macro truncating my output??

[sajjaddaya]

Hi Guys,

I have a macro below that outputs a text file that is pipe delimited, and has quotation marks around the output - this part is perfect!

The problem I am having is that the output in each cell is truncated after a certain point. I have some cells that have GIGANTIC amounts of data in them - how do I modify this macro to output everything in the cell without any truncating?

You're help will be VERY appreciated!

    Public Sub OutputQuotedCSV()
        Const QSTR As String = """"
        Const DELIMITER As String = "|"
        Dim myRecord As Range
        Dim myField As Range
        Dim nFileNum As Long
        Dim sOut As String
        
        nFileNum = FreeFile
        Open "File1.txt" For Output As #nFileNum
        For Each myRecord In Range("A1:A" & _
                    Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                For Each myField In Range(.Cells(1), _
                            Cells(.Row, 256).End(xlToLeft))
                    sOut = sOut & DELIMITER & QSTR & _
                        Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
                Next myField
                Print #nFileNum, Mid(sOut, 2)
                sOut = Empty
            End With
        Next myRecord
        Close #nFileNum
    End Sub

Thanks
Sajjad

 

[sajjaddaya]

Any thoughts guys?

 

[shg]

The Text property of a cell only returns the first 1024 characters. If that's what's causing the problem, use the Value2 property instead.

 

[sajjaddaya]

Thanks - that certainly worked for the truncated output, and fixed that particular issue.

I am having another problem. We were only test 4 records, and those 4 records are now exporting correctly. When we try and export the full file - about 5000 records, it looks as though the macro is running for about a minute, and then it ends - as you would expect. However, no file is generated.

Any idea why the macro would not work for 5000 records but would work for 4 records?

 

[shg]

No idea.

It creates the file in the current directory, wherever that is. Have you looked around?

 

[sajjaddaya]

Yeah, I've run a search on the whole computer - no file outputted called file1.txt. Usually it drops it into the My Docs folder, but when there are 5000 records, no file outputted.

Bizzare!

 

[shg]

What if you write 5 lines? 50? 500?

 

[sajjaddaya]

I copied the data to another sheet and tried to export - and it worked!

Not sure what the problem was?

Thanks for all your help, it is greatly appreciated!

 

[shg]

Good job, glad you got it sorted.