How to create a Macro for saving copy as csv with incremental file number
Category: [Excel] Demo Available
How to create a Macro for saving copy as csv with incremental file number
Requirement:
The user is managing an inventory using a barcode scanner, and the way this is done is to scan the code on each cell of the "Entradas" sheet and adding 1 to the count of the product that matches that code.
Should the person make a mistake, he can substract the product by scanning again but on the "Salidas" sheet, the sum goes to "CSV" sheet and then they upload the CSV file to the web and that pretty much does the trick.
The user wants to try to make it as automatic as posible, therefore avoiding human mistakes, so wants to make the macro that saves the "CSV" sheet as csv with an incremental file number, like file001 and then file002 and so on and that then deletes the previous data entries so they can keep using the same file without having to do any additional work.
Here is a sample workbook for a better understanding of what needs to be done:
https://docs.google.com/spreadsheets/d/1YkXJ9p_4_1dL4EgY5Yue-oz7sJo2_Q7JSQoTFG6cKA4/edit#gid=650622337
Solution:
Try runnng the Save_CSV_Sheet macro in the attached workbook, which is your workbook changed to a macro-enabled workbook (.xlsm file).
Sample workbook Macro.xlsm
The file001.csv, file002.csv, etc. files are created in the same folder as the workbook.
Code:
Public Sub Save_CSV_Sheet()
Dim csvFileName As String
csvFileName = GetNextFileName(ThisWorkbook.Path & "\file.csv")
ThisWorkbook.Worksheets("CSV").Copy
ActiveWorkbook.SaveAs csvFileName, FileFormat:=xlCSV
ActiveWorkbook.Close False
MsgBox "Saved CSV sheet as " & csvFileName
End Sub
Private Function GetNextFileName(filePath As String) As String
Dim n As Integer
n = 0
Do
n = n + 1
GetNextFileName = Replace(filePath, "", Format(n, "000"))
Loop Until Dir(GetNextFileName) = vbNullString
End Function
Obtained from the OzGrid Help Forum.
Solution provided by John_w.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy and paste value with V from another file |
| How to merge multiple excel files into one file in separate sheets with source file name |
| How to paste from multiple Excel workbooks into one workbook (Across the page & file names) |
| How to download a file using VBA |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.