2

I have a master Excel spreadsheet and I am updating this every day with 10 flat files for 10 different tabs. So, every morning I am opening my excel spreadsheet, deleting yesterday's tabs and putting the most recent txt files. Txt files follow same patterns like the following:

For today I have "FILE_1_45", "FILE_2_45", "FILE_3_45".... "FILE_10_45".

Tomorrow I will have "FILE_1_46", "FILE_2_46", "FILE_3_46".... "FILE_10_46" so those are incremental.

How can I automate this process? Can I just enter the # corresponding to my day and hit one button for excel to create me those?

Community
  • 1
  • 1
NickFallin
  • 21
  • 2
  • 3
    Welcome to the community. First of all please be aware that a good question usually gives better answers than bad ones. The answer to your question is "Yes", case closed? The standard here is to do a little research on the web and then post your efforts so far with a SPECIFIC question ("I get error X on line 5, what is wrong?", "My current script takes very long, is it not optimal?,...). please rephrase the question and do some research before posting here. – K_B Nov 13 '12 at 16:02
  • There is definitely a way to do this, probably with VBA, but you'd definitely need to provide more detail on the nature of the files, your data, and your spreadsheet to give better help. But yes, it should be possible to have a macro where it asks for the first number in the list of files and then gets the following 10. – jdotjdot Nov 13 '12 at 16:26
  • K_B, thanks for your comment. I believe what I am asking is not similar to the questions you mentioned above. I am simply asking how to automate this. @jdotjdot89 I should have given more details. I have txt files with same number of columns with varying # of rows (around 1000 rows). All I want is to eliminate some of the manual work going on there. I believe it is possible with VBA but with no knowledge i can't seem to work it through. Thanks all again – NickFallin Nov 13 '12 at 22:37

1 Answers1

0

Easy way that comes to mind for me is to write a perl script. You can use SpreadSheet-ParseExcel &/or SpreadSheet-WriteExcel to either update your existing spreadsheet and generate a new one based on the new data files. Many examples of this idea are available.

Quick search: http://www.thegeekstuff.com/2011/12/perl-and-excel/

DannyK
  • 1,342
  • 16
  • 23