I've got a macro that was generously created by a user on this forum. I've been using it in another sheet, and ported it to a new one I'm using.
Basically I want to export a specific tab, but as the file can have 5 rows or 5000 (max 10,000) I want it to clear any blank lines so when I import the file into our POS it won't try and import a bunch of blank lines.
Anyhow, with this formula now I'm getting an error opening the file (what has 8 rows I think) that it exceeds the 1 million lines error. Can anyone view the code and see if its possible to mod it better to remove all blank lines?
Thanks!
Sub ExportNewParts()
Sheets("Data Load").Copy
'SaveAs CSV
Dim v As Variant, FullPath As String
FullPath = "\\Win2k8doc\shares\PUBLIC\Computer Folder\Windward Docs\Price Book Import Tool\Import PART CREATOR\New Part Creator.csv"
v = Application.GetSaveAsFilename( _
InitialFileName:=FullPath, FileFilter:="Comma Separated Values (*.csv), *.csv, All Files, *.*", _
Title:="Save As CSV...")
If v <> False Then
ActiveSheet.SaveAs v, FileFormat:=xlCSV
ActiveWorkbook.Close False
CleanCSV v
End If
End Sub
Sub CleanCSV(fn As Variant)
Dim txt As String
txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\r\n,+" '<--- all blank row(s)
'.Pattern = "([\r\n]+,+[\r\n]+)+(?=$)" <--- blank row(s) after end of data
txt = .Replace(txt, "")
End With
Open fn For Output As #1
Print #1, txt
Close #1
End Sub
Bookmarks