Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / November 2007

Tip: Looking for answers? Try searching our database.

Output on .csv with 90 line limit

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Aaron - 28 Nov 2007 16:29 GMT
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 - 28 Nov 2007 17:06 GMT
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!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.