Export sheet from Excel to CSV
I am creating a spread sheet to help ease the entry of data into one of our systems. They are entering inventory items into this spread sheet to calculate the unit cost of the item (item cost + tax + S&H). The software we purchased cannot do this.
Aan invoice can have one or more lines (duh!) and I calculate the final unit cost. This is working fine. I then want to take that data and create a CSV from that so they can load it into our inventory system. I currently have a second tab that is laid out like I want the CSV, and I do an equal cell (=Sheet!A3) to get the values on the "export sheet". The problem is when they save this to a CSV, there are many blank lines that need to be deleted before they can uploa开发者_开发百科d it. I want a file that only contains the data that is needed.
I am sure this could be done in VBA, but I don't know where to start or know how to search for an example to start. Any direction or other options would be appreciated.
Look at Range.SpecialCells(xlBlanks).EntireRow.Delete
, I think this is what you are looking for!
expanding on @dwo's answer - this code will allow you to both remove the blank rows and export to CSV:
Sub export_to_csv(sheetname As String, OutDir As String)
Sheets(sheetname).Select
Set wb = ActiveWorkbook
Set newwb = Workbooks.Add()
wb.ActiveSheet.Copy newwb.ActiveSheet 'copy sheet to new workbook
newwb.ActiveSheet.Activate
ActiveSheet.UsedRange 'refresh the used range
Range("A1:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).Select 'select relevant cells
Selection.SpecialCells(xlBlanks).EntireRow.Delete 'remove empty rows
Application.DisplayAlerts = False 'avoid warning message when overwriting existing files
newwb.SaveAs OutDir & sheetname, xlCSVWindows 'save as CSV
newwb.Close 'close new workbook
Application.DisplayAlerts = True 'reset warning messages
End Sub
Sub test()
export_to_csv sheetname:="Sheet1", OutDir:="C:\temp\"
End Sub
The solution on this page worked best for me. I just had to modify it to allow it to work for what I needed.
精彩评论