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.

 

[SkipVought]

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 IMPORT first CSV in c:\users\user\desktop\exported

Actually, my thought was that

1) ALL this code would be in the masterdoc.xlsm workbook and
2) IMPORT via an existing/pre-determined for .csv (COMMA separated values) into the IMPORT sheet.

Are you saying that that will yield variable parsing results?

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

 

[Rossco82]

Correct, this is only because of the discovery today of the " causing issues. And this was only noticed as I tried the import as requested previously.

So. One a line that currently has something like:

Supplier PoRef date blah blah blah comments- Being delivered on some other day"

Then becomes

Supplier PoRef date blah blah blah
comments- Being delivered on some other day"

Once imported. It's not the - as this is on multiple lines and has no issues.

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

[sub]One day, while sitting in his library, R.D. became distracted by a ruckus below his window.
As the disturbance continued, he exclaimed, “I can’t think!” And **puff** he vanished.[/sub]

Could you upload a sample of the .csv data in question normal rows before and after?

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

 

[strongm]

[blue][green]' Updates active sheet with rows from CSVs in srcFolderPath. Thus assumes CSVs have been detached from email and placed alone in specific folder
' Requires references to Microsoft ActiveX Data Objects Library and Microsoft Scripting Runtime
' Assumes ActiveSheet refers to what it says - the active worksheet. Straightforward if this is VBA in Excel …[/green]
Private Sub PopulateMaster(srcFolderPath As String)
    Dim xlcon As ADODB.Connection
    Dim xlrs As ADODB.Recordset
    Dim header As ADODB.Field
    Dim fieldcount As Long  
    Dim FSO As New FileSystemObject
    Dim myfile As File
    Dim SQL() As String
    Dim lp As Long
    Dim lastrow As Long

    Set xlcon = New ADODB.Connection
    xlcon.Provider = "Microsoft.Jet.OLEDB.4.0"
    xlcon.ConnectionString = "Data Source=" & srcFolderPath & ";" & "Extended Properties='text;HDR=Yes;FMT=Delimited';'"
    xlcon.Open
    
    Set xlrs = New ADODB.Recordset
    With xlrs
        .CursorLocation = adUseClient
        [green]' Build apprpriate SQL UNION query, adding date and PORef columns[/green]
        For Each myfile In FSO.GetFolder(srcFolderPath).Files 
            ReDim Preserve SQL(lp) As String
            SQL(lp) = "SELECT Now() as [Date], [Order Number], left([Order Number],Instr([Order Number],""" & "-" & """)-1) as PORef, * FROM [" & myfile.Name & "]"
            lp = lp + 1
            [green]'myfile.delete ' Uncomment this line if you want to add some houskeeping as you go ...[/green]
        Next
        [green]' Run the query[/green]
        .Open Join(SQL(), " UNION "), xlcon
        
        [green]' Insert headers (assumed to be first row of CSV files) if this is an empty sheet, using a simplistic check[/green]
        If ActiveSheet.Cells(1, 1) <> "Date" Then
            fieldcount = 1
            For Each header In .Fields
                    ActiveSheet.Cells(1, fieldcount) = header.Name
                    fieldcount = fieldcount + 1
            Next
        End If
    End With
    
        [green]' Now append UNION query result into active sheet[/green]
        lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1 [green]' Cheap and cheerful way to find last used row of a column[/green]
        ActiveSheet.Cells(lastrow, 1).CopyFromRecordset xlrs
   
    [green]' All done
    ' Going out of scope, so don't really need to do this cleanup, but what the heck ...[/green]

    xlrs.Close
    xlcon.Close
     
    Set xlrs = Nothing
    Set xlcon = Nothing
End Sub[/blue]

 

[SkipVought]

**puff** you got a good solution from strongm!

You may just need to add another SQL step in strongm's code to "insert" a PO Ref composite field.

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

 

[Rossco82]

strongm "Straightforward if this is VBA in Excel …" ummmmmmmmmm......

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[strongm]

>You may just need to add another SQL step in strongm's code to "insert" a PO Ref composite field

Nope, already included in the UNION query

[tt]left([Order Number],Instr([Order Number],""" & "-" & """)-1) as PORef[/tt]

 

[SkipVought]

didn’t notice that.
STRONG!

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

 

[Rossco82]

Morning, again this is my level of knowledge. The above looks amazing, but I can't run it?. I have enabled the right references (Microsoft ActiveX Data Objects Library and Microsoft Scripting Runtime), but it isn't even seen as an available Macro. If I remove "srcFolderPath As String" from the sub field it runs down to "xlcon.Open" then says it is an invalid object. I've tried the various data a library versions etc too to try them, no joy.

So my questions:

1. Huh?.
2. I see various bits in the code about srcfilepath etc, but there is no reference to the file path itself?. Should I be populating that specific section? (my assumption no but want to check). If no how does it know where the files are?.
3. I can attach images for the references if need be, but they are right, could it be that others are still being used in conjunction?. I did get errors about the title being wrong, this was when I went through them all and still had no luck.

Feel free to shake heads and tut. But if you don't know, you don't know. That's why mortals like me have to ask the likes of your good selves

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[strongm]

>it isn't even seen as an available Macro

Ok, firstly this code is not really a macro. Macros (of the type that appear in the Macro dialog) cannot not take parameters/arguments (which is why when you remove "srcFolderPath As String" it suddenly magically appears as a macro - but will now not work as designed) . What we have here is purely a VBA subroutine (also loosely known as a 'method').

>I see various bits in the code about srcfilepath

er .. where? Do you mean srcFolderPath? If so, as seen above you've actually removed this ...

Basically what is supposed to happen is that you call this method, passing the path of the folder containing the CSVs as the argument. Subsequently within the subroutine srcFolderPath will hold that value

So, how do we do that? Several ways, but let's go with what you appear to be familiar with, a macro

Simply add the code below to your module (filling in the pathname where indicated ..). Subsequently Test will appear in the Macro dialog ...

[blue]Public Sub Test()
    PopulateMaster "[b][red]put the path to the folder in here[/red][/b]"
End Sub[/blue]

 

[Rossco82]

Well f*** me (excuse the expletives). It's only bloody working!!!!

Gentlemen (and ladies if there was any). THANK YOU SO MUCH!

Strong, you are a legend my friend, never let it be said otherwise!.

Skip, again, thanks for the help!!!

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[Andrzejek]

Isn't it just.... WOW!
You ask a simple question, you get a simple answer. And after a few good questions from some good people you ended up with a solution you didn't even knew you needed.
And that's the power of Tek-Tips.


---- Andy

There is a great need for a sarcasm font.