• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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
0
Sonia Bowditch
Asked:
Sonia Bowditch
  • 4
  • 2
1 Solution
 
nutschCommented:
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

0
 
SStoryCommented:
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.
0
 
Sonia BowditchInformation Security OfficerAuthor Commented:
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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Sonia BowditchInformation Security OfficerAuthor Commented:
Hello SStory,

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

Regards,
InfoSec36
0
 
nutschCommented:
My bad, try instead

Set wbkCSV=workbooks.open(vPath)
0
 
Sonia BowditchInformation Security OfficerAuthor Commented:
Hello Nutsch,

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

wbkCSV = Workbooks.Open(vPath)


Regards,
InfoSec36
0
 
Sonia BowditchInformation Security OfficerAuthor Commented:
Thanks Nutsch,

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

Thank you so much.

Regards,
InfoSec36
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now