Forum Discussion

John_cameron121's avatar
John_cameron121
Copper Contributor
Dec 04, 2022

Changing file name

  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 ...
excel
Formulas and Functions
Macros and VBA
John_cameron121's avatar
John_cameron121
Copper Contributor
Dec 09, 2022

HansVogelaar 

 

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 😊

HansVogelaar's avatar
HansVogelaar
MVP
Dec 09, 2022

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

  • John_cameron121's avatar
    John_cameron121
    Copper Contributor
    Dec 10, 2022

    HansVogelaar  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

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Dec 10, 2022

      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
      • John_cameron121's avatar
        John_cameron121
        Copper Contributor
        Dec 11, 2022
        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

Resources