Solved

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

Posted on 2014-03-10
7
248 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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.

726 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