asked on

Close a .CSV File when the name of the file changes daily

Hello Experts,

I am trying to cut down on the amount of effort I have to do in getting files into a directory, changing their name and then automatically importing the file data into a Excel spreadsheet.

Each day I get a file called Unsuccessful_Account_Validation[previous days date], for example Unsuccessful_Account_Validation_Mar_10_2014. I can get the file by using vPath = Application.GetOpenFilename("CSV (Comma Delimited) (*.csv),*.csv"

But because the file name is dynamic I cannot close the file using Windows(filename.csv").Activate
ActiveWindow.close

Any help you could give me would be greatly appreciated.

Regards,
InfoSec36

nutsch

How about

dim wbkCSV as workbook
vPath = Application.GetOpenFilename("CSV (Comma Delimited) (*.csv),*.csv"

wbkCSV=workbooks.open(vPath)
'do your stuff here

wbkCSV.close(false)

Open in new window

SStory

If the data is a CSV and the same format every day and comes without headers, I'd change it from .xls to a simple .CSV and append the data to the CSV file.

So Example:
MainFile.csv (Excel and LibreOffice or any spreadsheet can read this)

NewFileWhateverName.CSV

From the commandline
Type NewFileWhateverName.CSV >> MainFile.csv

the >> will concatenate (append the data in NewFileWhateverName.CSV to MainFile.csv

You can then open that file...or not, but the data will be there.

Sonia Bowditch

ASKER

Hello nutsch,

Thanks for getting back to me so quickly.

I can't get it to work. For some reason it falls over at wbkCSV=workbooks.open(vPath). The message I get is 'Object doesn't support this property or method'.

Regards,
InfoSec36

Sonia Bowditch

ASKER

Hello SStory,

The .CSV files I get each day are imported into Excel for further formatting and analysis.

Regards,
InfoSec36

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sonia Bowditch

ASKER

Hello Nutsch,

I am getting an object required error when it hits:-

wbkCSV = Workbooks.Open(vPath)

Regards,
InfoSec36

Sonia Bowditch

ASKER

Thanks Nutsch,

I did a bit of fiddling with my code and got it to work.

Thank you so much.

Regards,
InfoSec36