?
Solved

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

Posted on 2014-03-10
7
Medium Priority
?
252 Views
Last Modified: 2014-03-11
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
Comment
Question by:Sonia Bowditch
  • 4
  • 2
7 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39918376
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
 
LVL 25

Expert Comment

by:SStory
ID: 39918387
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
 

Author Comment

by:Sonia Bowditch
ID: 39918421
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Sonia Bowditch
ID: 39918427
Hello SStory,

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

Regards,
InfoSec36
0
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 39918723
My bad, try instead

Set wbkCSV=workbooks.open(vPath)
0
 

Author Comment

by:Sonia Bowditch
ID: 39919948
Hello Nutsch,

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

wbkCSV = Workbooks.Open(vPath)


Regards,
InfoSec36
0
 

Author Closing Comment

by:Sonia Bowditch
ID: 39920339
Thanks Nutsch,

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

Thank you so much.

Regards,
InfoSec36
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question