Code to pull files and rename them (variable file names) 4

[Rossco82]

Hi All,

I've scoured everywhere to find what I am looking for but simply can't get to the bottom of it.

Scenario.

1. 3 x mail received with attachments.
2. Attachments are pulled and moved to a folder on my desktop.
3. I need to rename these files sequentially so I can then manipulate them. (Files come in as TRACKINGLATE-41694-519854194.csv as example, the digits always change and can be different lengths.

Reason for this is this is done daily, so monotonous.
The 3 files can just be called 1.csv, 2.csv, 3.csv if needs be. But once they are sequential I have other things I need to do which I can't based on a variable file name.

Users should simply press a button, it changes the file names to 1,2,3, the rest of my code can then complete, and the files are deleted.

I have no sample code as I have tried about 50 ways of doing this so far and got no where hence having to ask.

 

[Andrzejek]

So you want all csv files in the folder to be renamed "sequentially"

If you have:[tt]
TRACKINGLATE-41694-519854194.csv
XYZ.csv
ABCD.csv
1234.csv[/tt]

you want to have:[tt]
1.csv
2.csv
3.csv
4.csv[/tt]

Option Explicit

Private Sub cmdRenameCSVFiles_Click()
Dim strPath As String
Dim strFNmae As String
Dim intX As Integer

strPath = "[red]E:\TT\MyFiles\[/red]" [green]'Provide your own Path here[/green]
strFNmae = Dir(strPath)

Do While strFNmae <> ""
    If Split(strFNmae, ".")(1) = "csv" Then
        intX = intX + 1
        
        Name strPath & strFNmae As strPath & intX & ".csv"
    End If

    strFNmae = Dir
Loop

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[mintjulep]

Alternately, since it seems like you don't really care what the names are, and are deleting the files at the end...

Because you need to use the existing names along the way to rename the files, just do what you need to do with the files named as they are.

Option Explicit

Private Sub cmdRenameCSVFiles_Click()
Dim strPath As String
Dim strFNmae As String
Dim intX As Integer

strPath = "E:\TT\MyFiles\" 'Provide your own Path here
strFNmae = Dir(strPath)

Do While strFNmae <> ""

[b][COLOR=#4E9A06]Just do your whatever here[/color][/b]

    strFNmae = Dir
Loop

End Sub

 

[Rossco82]

Thanks both, Andy your solution works perfectly. I've wasted so much time on this due to my lack of knowledge, so thank you, this has helped massively!!!.

 

[Andrzejek]

You are welcome.

Out of curiosity, what do you do with those renamed files (that you could not do when they had original names) ?


---- Andy

There is a great need for a sarcasm font.

 

[Rossco82]

Ok, to elaborate for you. The files come in on e-mail and are never deleted, so putting them into a file externally only takes up disc space (hence deleting them). The files names change daily and we have a lot of cutting/pasting/inserting to do to put them into the right format before we can add them to the master sheet. So without your code we would have to continue to do them manually. You have dropped the work time from over 30 mins to about 10 seconds now. That may not seem a big deal to some, but when you are up against the clock each day that is a HUGE difference.

With the names being of variable length and containing different numbers each time, I just couldn't suss how to work with the file names as they were. And as I will be passing the form on to someone else to work with there needs to be as little human input as possible until someone comes to us in future to say its not working for some reason.

Again, thanks Andy. I had put this on other sites and just got stupid, or sarcastic answers back when it was a genuine issue I couldn't get passed. Really glad I signed up to Tek-Tips now!.

 

[mintjulep]

I just couldn't suss how to work with the file names as they were.

Ok. But do you understand that Andy's code works with the file names as they were along the way to renaming them sequentially?

You couldn't figure out how to use existing file name, so you decided that you need simple sequential file names.

But code doesn't care.

Renaming the files only adds complexity with no benefit at all.

Now that Andy has shown you how to use the original file names, just use the original file names.

 

[Andrzejek]

Rossco82,
I am glad my help cut your 'busy' time so much.
But I am with mintjulep, although to be fully with mintjulep, I would have to know the WHOLE story of what you do with those files from the very beginning to the very last step. I suspect there is some (a lot?) unnecessary coding/logic/steps that you do that could be (should be / have to be) improved and make a lot simpler to write and use. Which may shorten your time (and effort of users) to almost nothing - like a few clicks and few seconds. Who knows...?

But it is up to you if you want to go that way....



---- Andy

There is a great need for a sarcasm font.

 

[Rossco82]

Sorry for the lenghty delay in replying chaps/ladies (not assuming gender).

So, am I to assume that with mint's code this will execute the on each file in the location including my code entered? I.e it will leave the filenames as is, (then this is my stuff) open each doc copy out what I need, paste it to the right location, then end?. I ask as the my code bit is inside the loop. If this is the case then yes this would absolutely work and there would be no need to change the filenames. Andy's solution does exactly what I need it to, but if we can take it a step further, not dick about changing names, and complete all the work in one small loop I am all for it.

So this is what happens -

1. There is a master doc , for now lets say "C:\Users\users\desktop\Masterdoc"
2. Daily we receive three e-mails that all need the attachments to be pulled from outlook and dumped to a folder to work on (This part I have a macro for and it works perfectly).
3. Once these attachments (spreadsheets) are in the folder, the macro needs to open them one by one, copy from line two to the bottom of the last blank cell (not all cell are filled out, but it is very rare for there to be a whole line blank, in fact I haven't seen it yet).Data in these sheets runs from A:O.
4. Each of the 3 attachments is missing a column that we need to add for a PO ref which is comprised of the first set of digits in column C (so C has 826414-2018-10-08
, then D would be 826414. These can be five or six digit).
5. Once the PO column is added and populated the data is then copied to the last row on "C:\Users\users\desktop\Masterdoc", and the date is entered next to the entry in column A (this will always be today).
6. Repeat for the remaining files.

Master doc with PO column

One of the attachments

Excuse the "supplier" being entered, this is just to remove identifiable details.

 

[Rossco82]

Ok, so this is it in a nut shell.

1. Open master doc c:\users\user\desktop\masterdoc.xlsx (assuming the doc needs to be open to paste the following information?)
2. Look at folder c:\users\user\desktop\exported files (each file is a CSV)
3. open first CSV in c:\users\user\desktop\exported
4. insert column D
5. Shorten the PO number in C to the required length and enter to D.(Copy step for each row).
6. Copy all data on the sheet excluding the header row.
7. Paste to last row of master doc
8. Enter date next to each entry added to the master doc.
9. Delete all 3 files in c:\users\user\desktop\exported files that have just been used.

Its not that vast, but some of the steps go way beyond my knowledge. I know various people that are really good with Excel and they couldn't figure it out either (so maybe they aren't that good? or it is just a tricky one???)

You've been a star to date Andy, really appreciate you taking the time to help. (This was in reply to your deleted comment)

 

[Andrzejek]

Yes, I deleted my post because I realized you did what I was asking for

I see you have another thread here: thread707-1790542
That's great. Stick with Skip, he will help you - if you: answer his every question in full, follow every suggestion he may have, provide sample (attachment, not a picture) if asked. if anybody can figure your issues out, it's him.


---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

Do you empty / clear masterdoc.xlsx before you add the data from all those csv files?
Or do you keep adding to masterdoc.xlsx file day after day after day?

I assume all csv files have the same record format?
Do you have a header row in every csv file, or no header, just data?

In any event, you may consider:
1. Read (copy) each csv file in the folder into just one csv file
2. In Excel, Import the newly created csv file
3. insert column D and all other steps just once - since all data is in one file now.



---- Andy

There is a great need for a sarcasm font.

 

[strongm]

We could pretty much do it all three steps through a single SQL UNION query ...

 

[Rossco82]

Unfortunately strongm it's all sat in excel spreadsheets. If you can show me how to set it all up and work with it as a SQL UNION query I'm always game lol.

Andy, yeah tbh I did think that at the time haha. Not too worry. And yes, Skip found the issue in one go. I was actually trying to find the problem when he posted, however I managed to find the problem around the same time (so skip wins hands down, I couldn't see it hence posting). I fixed it eventually, and then got back online to skip's reply. Again tek-tips doesn't fail to be of help , you guys know your stuff.

 

[Rossco82]

Hi Again, I replied then saw your other post.

Each month has its own master doc, so monthly I will need to update the code (unless that can be figured out too?) Data is added to the bottom until the new month begins.
All CSV's are the same format.
Header row is in every CSV this is why I was copying from row 2. I don't know how that will effect things?.

That is actually a good idea, but I have just tried it. They sometimes have quotation marks in the final field, and even though not set as a delimiter it then drops them on to a new row. It does however import as a query straight to a table?.

 

[SkipVought]

Unfortunately strongm it's all sat in excel spreadsheets.

FORTUNATELY, your data is is Excel spreadsheets.

I haven’t followed this thread, but if you’re trying to get .csv text files into one Excel table, you can set up a sheet as a variable IMPORT. Your process would be,

1) IMPORT a .csv,
2) copy the imported data to the master table,
3) repeat for each .csv

Personally, I avoid inserting/deleting/switching columns. Append your PO Ref column on the right. Then REPORT it any way you like. But inserting columns, needlessly complicates a process.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

>it's all sat in excel spreadsheets

Well, spreadsheets and CSVs ... but doesn't matter ...

 

[Rossco82]

Skip, NOICE! I'll have a look and see what I can get it to do. Two things though:
1. Unfortunately I can't put the PO on the right. This is due to the format of the master doc, I have strict instructions not to change it.
2. The master doc is not formatted as a table (assumption being you specifically mean a table based on your post. But if you just mean paste to the data area then yep, cool, I'll give it a bash).

strongm potato potaaaaato

 

[SkipVought]

Potato, potaaaaato, spud
Cogito ergo spud (discarted by Descartes)
I think, therefore I yam.

Set up an IMPORT sheet with this one-time manual process to ADD a QueryTable to your IMPORT sheet. NOTE: performing this multiple times will needlessly ADD a QueryTable object to your IMPORT sheet. In the event that you must repeat this one-time process, DELETE the sheet and start again.

Data > Get External Data > Get Text Files... and drill down to your file. Choose the delimiter, specify columns according to data type and Finish.

Once established, turn on your macro recorder and record Editing your query and Finish.

Turn off your macro recorder, copy the code and paste back here.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Rossco82]

skip "I think, therefore I yam." That's a sig if ever I did see one!!!.

Issue being as I mentioned before. If importing from text it sees " in the final field and puts these on to a new row even though not specified as a delimiter. I suspect this is whoever creates the doc accidentally copying and pasting them in as there is only one at the end of a sentence. So it wouldn't work as this then throws the formatting out. However I have just re-read it and think I got the gist wrong to start. To confirm-

Your suggestion is to copy the data to a new doc as a table, then put that table into the import doc and do the process manually once?. This is absolutely my level of knowledge here, you are probably making 100% sense to those that know more. I'm punching a little above my weight, but that's business life I'm afraid!.