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

         

Resources