Solved

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

Posted on 2014-03-10
7
237 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:infosec36
  • 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:infosec36
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:infosec36
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 500 total points
ID: 39918723
My bad, try instead

Set wbkCSV=workbooks.open(vPath)
0
 

Author Comment

by:infosec36
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:infosec36
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now