Output on .csv with 90 line limit

[Aaron]

I have the following code that I use to save to a csv. The .csv file for
upload has a limit of 90 lines, therefore I would need to have multi files
created with lines 1 to 90 on one file with a 1 on the end of the file name.
Then lines 91 to 181 with a 2 on the end of the file name until all the lines
are on different files. Is that possible?

Sheets("PO upload").Copy

ActiveSheet.SaveAs _
Filename:=strPOnum1 & " " & Format(Now, "mmddyy") & _
".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close savechanges:=False

 

[Bernie Deitrick]

Aaron,

The macro below will do that - assumes that your output table starts in cell A1 and is contiguous.
It will save the files in the folder where the workbook with the macro is stored. If it starts
elsewhere, change the line

With Range("A1").CurrentRegion

to reflect the address of the upper left cell.

HTH,
Bernie
MS Excel MVP


Sub ExportTo90LineCSV()

Dim fName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim I As Integer
Dim FCount As Integer
Dim strPOnum1 As String

strPOnum1 = "Whatever"

fName = ThisWorkbook.Path & "\" & strPOnum1 & " " & Format(Now, "mmddyy")

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

With Range("A1").CurrentRegion
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

For I = StartRow To EndRow Step 90
FCount = FCount + 1
Open fName & " " & FCount For Output Access Write As #FNum

For RowNdx = I To I + 89
WholeLine = ""
For ColNdx = StartCol To EndCol
If WholeLine = "" Then
WholeLine = Cells(RowNdx, ColNdx).Text
Else
WholeLine = WholeLine & "," & Cells(RowNdx, ColNdx).Text
End If
Next ColNdx
Print #FNum, WholeLine
Next RowNdx
Close #FNum

Next I

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True

End Sub

 

[Bernie Deitrick]

Ooops - forgot the .csv extension for the filename. Change:

Open fName & " " & FCount For Output Access Write As #FNum

to

Open fName & " " & FCount & ".csv" For Output Access Write As #FNum


HTH,
Bernie
MS Excel MVP

 

[Aaron]

Worked like a charm! This will save me loads of time. Thank you so much!

 

[Bernie Deitrick]

You're quite welcome! Thanks for letting me know that you got my code to work.

Bernie
MS Excel MVP