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
HansVogelaar's avatar
HansVogelaar
MVP
Dec 05, 2022

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
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

Resources