Changing file name

Copper Contributor

image.jpg

Hi and thanks in advance. 

I am currently using a macro to import data from a file named

2022-12-04_07-23-20_detail_inbound.csv

it imports data from Column D2 down to 60 and the same on column L


when I receive a new file.

2022-11-29_01-11-10_detail_inbound.csv

it comes back with an error.

 

now I know it’s due to the change of the file name and as the file name I keep receiving changes how can I get my macro to pick up on this or is there another way? 

any help would be appreciated

 

 

 

16 Replies

@John_cameron121 

Perhaps the easiest way would be to make sure that the .csv file is the active workbook when you start the macro. That way, you can refer to ActiveWorkbook and ActiveSheet.

If you need to refer to it later on, assign it to a variable:

    Dim CSVSheet As Worksheet
    Set CSVSheet = ActiveSheet
    ' rest of the code goes here

@Hans Vogelaar 

 

Thankyou for your assistance, I can’t seem to get this sorted, any chance you could look at the example above so I can put some code in and trial it, I think I could get it working from there :smiling_face_with_smiling_eyes:

@John_cameron121 Could you post your code? I'm too lazy to type it in from a hard-to-read screenshot.

@Hans Vogelaar  Hi, I really appreciate your time.

 

I'm not very good with VBA but I'm slowly learning.

 

in line 10 and 15 is the file I'm trying to open. The file name keeps changing and will never be the same except for the last words "detail_inbound.csv"

The file comes from an external provider to our email address. I would be great if this could work with anybody that imports the file to their email address. What we would do is open up the email address click on the file which opens it.  Then click on a button which has the macro, then the information is transferred as per below. It would be great if it could close the csv file as well.

 

Please ask if you require any more information

 

Highly appreciated

 

John

 

Sub import()
'
' import Macro
'


'
    ActiveSheet.unprotect
    Range("A5").Select
    Windows("2022-11-29_01-11-10_detail_inbound.csv").Activate
    Range("D2:D60").Select
    Selection.Copy
    Windows("FF OT Wellington 2022-2023.xlsm").Activate
    ActiveSheet.Paste
    Windows("2022-11-29_01-11-10_detail_inbound.csv").Activate
    Range("L2:L60").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("FF OT Wellington 2022-2023.xlsm").Activate
    Range("B5").Select
    ActiveSheet.Paste
    Range("A5").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Hand Backs").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Hand Backs").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("J4:J100"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Hand Backs").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

@John_cameron121 

Try this version. It will prompt you to open the csv file.

Sub import()
    Dim varCSV As Variant
    Dim wbkCSV As Workbook
    Dim wshCSV As Worksheet
    Dim wshXLS As Worksheet
    varCSV = Application.GetOpenFilename(FileFilter:="CSV files (*.csv),*.csv")
    If varCSV = False Then
        Beep
        Exit Sub
    End If
    Set wshXLS = ActiveSheet
    wshXLS.Unprotect
    Set wbkCSV = Workbooks.Open(filename:=varCSV)
    Set wshCSV = wbkCSV.Worksheets(1)
    wshCSV.Range("D2:D60").Copy Destination:=wshXLS.Range("A5")
    wshCSV.Range("L2:L60").Copy Destination:=wshXLS.Range("B5")
    With wshXLS.AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=wshXLS.Range("J4")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    wshXLS.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Hi, thank you for taking the time to do this. Any chance it could take the data from just when it’s open? It comes from outlook and the file is temp stored in a file deep down in the computer.

Hope this makes sense

@John_cameron121 

What is the name of the target worksheet (the sheet you want to paste the data to)?

Target work book is line 13 and 19 and work sheet is line 24 from my earlier post.

Thank you

@John_cameron121 

Thanks.

Make sure that the CSV file is the active workbook when you run the macro.

Sub import()
    Dim wshCSV As Worksheet
    Dim wshXLS As Worksheet
    Set wshXLS = ThisWorkbook("Hand Backs")
    wshXLS.Unprotect
    Set wshCSV = ActiveSheet
    wshCSV.Range("D2:D60").Copy Destination:=wshXLS.Range("A5")
    wshCSV.Range("L2:L60").Copy Destination:=wshXLS.Range("B5")
    With wshXLS.AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=wshXLS.Range("J4")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    wshXLS.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Hi once again :smiling_face_with_smiling_eyes:

Under line 4 of your last code it comes up with run-time error 438
Object doesn’t support this property or method.

Another quick question, should this code go under a module or a sheet under Microsoft excel objects?

@John_cameron121 

This is a standard macro, so it goes into a standard module, Corrected version:

Sub import()
    Dim wshCSV As Worksheet
    Dim wshXLS As Worksheet
    Set wshXLS = ThisWorkbook.Worksheets("Hand Backs")
    wshXLS.Unprotect
    Set wshCSV = ActiveSheet
    wshCSV.Range("D2:D60").Copy Destination:=wshXLS.Range("A5")
    wshCSV.Range("L2:L60").Copy Destination:=wshXLS.Range("B5")
    With wshXLS.AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=wshXLS.Range("J4")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    wshXLS.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Wow thank you so much, we are real close. When I open the csv file then f8 through the code it works fine. I think that’s because the csv file is activated.

When I click on the macro button on the handouts spreadsheet it doesn’t activate the csv file so doesn’t copy across, any way around this?

@John_cameron121 

If you run the macro while the workbook with the macro is the active workbook, we don't know how to access the csv file, since we don't know its name.

So activate the csv file, then press Alt+F8 to activate the Macros dialog.

Select the import macro in the list and click Run.

Just to make it easier for others to use is there another way where we don’t have to press alt F8? I would love to press a button with a macro attached and it would open or something similar

@John_cameron121 

That would have been possible with the earlier version that prompted the user to open the csv file, but you didn't want that.

With the current setup, where the csv file is the active workbook, that is too complicated.

@Hans Vogelaar thanks you're the quick responses.

I tried to send a video of explaining what I'm trying to achieve but unfortunately it was too big.

 

I would love to use the folder option but more than 1 person will be using the document. when the document comes to our emails it saves deep into the work system in a temp folder and looking at everyone's file paths, they look different as well.

 

I think I will be able to get them to use alt F8. thank you so much for your help.  this means so much and going to save a lot of people time and energy. 

 

Regards

 

John