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
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
>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
[quoted text clipped - 8 lines]
> ".csv", FileFormat:=xlCSV, CreateBackup:=False
> ActiveWorkbook.Close savechanges:=False
Bernie Deitrick - 28 Nov 2007 17:17 GMT
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,
>
[quoted text clipped - 77 lines]
>> ".csv", FileFormat:=xlCSV, CreateBackup:=False
>> ActiveWorkbook.Close savechanges:=False
Aaron - 28 Nov 2007 18:27 GMT
Worked like a charm! This will save me loads of time. Thank you so much!
> Ooops - forgot the .csv extension for the filename. Change:
>
[quoted text clipped - 89 lines]
> >> ".csv", FileFormat:=xlCSV, CreateBackup:=False
> >> ActiveWorkbook.Close savechanges:=False
Bernie Deitrick - 28 Nov 2007 18:33 GMT
You're quite welcome! Thanks for letting me know that you got my code to work.
Bernie
MS Excel MVP
> Worked like a charm! This will save me loads of time. Thank you so much!