Modify Excel File and save as CSV using vba

Question

I am working on populating a new application full of resident data. I have to export a list of residents for each property. Unfortunatly the new application has to have the residents imported 1 property at a time. This means im stuck loading 200+ properties by exporting an excel file, slightly modifing the data and then importing into the new application.

For each property that is exported I must remove a '-' from the first column and i have to remove all of the ',' throughout the entire document. I then change the formating on a date column to 'mm/dd/yyyy'. Then the document is saved as a CSV and can be imported.

I would like to write a script that can perform the updates to the excel file and save it to a csv. Please advise if this is worth my time. This is a one time load so it might be better to just power through.

Thank You

do you know any scripting languages? It might be easier to convert it to csv first (just using save-as in Excel), then use something like Python, Ruby or another language to manipulate the data and change the files. — Jeff Tratner
– Jeff Tratner, Commented Oct 7, 2012 at 22:33
So, to clarify, you're performing the same action on 200 different worksheets? Are you currently using replace all in the find & replace dialog, or do you require more fine-grained control than that? — mkingston
– mkingston, Commented Oct 8, 2012 at 0:13

mkingston · Accepted Answer · 2012-10-08 00:23:04Z

1

Possibly a little prematurely, as I'm not certain this is what you want to achieve, but you could try this (save first):

Sub replaceStuff()

    ActiveSheet.Range("A:A").Replace "-", ""
    ActiveSheet.Cells.Replace ",", ""
    ThisWorkbook.SaveAs "doc", xlCSVWindows

End Sub

answered Oct 8, 2012 at 0:23

mkingston

2,74820 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Modify Excel File and save as CSV using vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related