Need an excel macro/script to populate static data fields in a csv worksheet

Copper Contributor


sample csv file.PNG


I have a csv excel worksheet that I have to produce daily from a data file, exported from a fuel recorder box. I recorded a micro/script in excel to produce this worksheet. When completed, I have to upload the .csv file to a software program. It has to stay clean and contain all required headers and fields or it will fail. 


The data and cells change daily and are neveer the same. There could be 20 rows of data one day, then 34 the next day. No static values. 


My problem is that now I have 3 new static fields that I need to populate and I cant figure out how to edit my macro/script to do this. I have added the headers for the 3 static fields ( State, Location, City) to my micro but cant figure out how to populate them correctly, because the amount of rows change every day. 


 I need a script to add to my current one that will first look at the populated cells in row C, then populate the static fields in D,E,F.   If there is no data in C, then leave D,E,F blank. 

The static data in field D should be "KY, in E should be "OWEN", in F shoulkd be "RUSSELLVILLE"


Thanks for your help.


6 Replies


You can set the values in the ranges in D, E, and F by starting at C3 and using the following statements:


ActiveSheet.Range("c3", ActiveSheet.Range("c3").End(xlDown)).Offset(0, 1).Value = "KY"
ActiveSheet.Range("c3", ActiveSheet.Range("c3").End(xlDown)).Offset(0, 2).Value = "OWEN"
ActiveSheet.Range("c3", ActiveSheet.Range("c3").End(xlDown)).Offset(0, 3).Value = "RUSSELLVILLE"

This assumes that from C3 to the end of the rows, there will be no blanks in column C.  If you have blanks, then this will only set the values in the top section before you hit a blank cell in column C.


If you have blanks, then you can use something like this:

ActiveSheet.Range("c3", ActiveSheet.Range("c" & ActiveSheet.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Offset(0, 1).Value = "KY"
ActiveSheet.Range("c3", ActiveSheet.Range("c" & ActiveSheet.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Offset(0, 2).Value = "OWEN"
ActiveSheet.Range("c3", ActiveSheet.Range("c" & ActiveSheet.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Offset(0, 2).Value = "RUSSELLVILLE"


There wont be any blanks. The first macro works perfectly. I may have another question before I finish my macro.
Thanks for your help.


Hello. I need to modify/edit my macro and need your help, please.

I have already modified it to "save" the converted workbook in the "csv" format to a specific folder. The file name remains the same each save and just overwrites.

But here is what i really need
1. to "save as" a specific file name to include the current date. Example "Owen-Promiles_3/6/2024" It could also contain the time, if needed.
2. Save to a folder called "Ready to Edit" which is on my desktop.
3. Then simply "exit" Excel

Thanks for your help.
here is that part of my current macro

Selection.Delete Shift:=xlUp
ChDir "C:\Users\rsrig\Desktop\New Fuel Box\Ready to Edit"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\rsrig\Desktop\New Fuel Box\Ready to Edit\Todays_Fuel_File_To Edit.csv" _
, FileFormat:=xlCSV, CreateBackup:=False
End Sub
You can use Format(Now(), "yyyy-mm-dd"). Change the format code to suit your needs.
For example:
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\rsrig\Desktop\New Fuel Box\Ready to Edit\Todays_Fuel_File_To Edit" & Format(Now(), "yyyy-mm-dd") & ".csv" _
, FileFormat:=xlCSV, CreateBackup:=False