Dec 04 2022 12:48 AM - edited Dec 04 2022 01:21 AM
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
Dec 05 2022 12:38 AM
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
Dec 09 2022 02:06 PM
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:
Dec 09 2022 02:08 PM
@John_cameron121 Could you post your code? I'm too lazy to type it in from a hard-to-read screenshot.
Dec 09 2022 04:23 PM
@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
Dec 10 2022 03:59 AM
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
Dec 11 2022 12:13 PM
Dec 11 2022 12:49 PM
What is the name of the target worksheet (the sheet you want to paste the data to)?
Dec 11 2022 01:45 PM
Dec 11 2022 02:12 PM
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
Dec 11 2022 04:47 PM
Dec 11 2022 11:20 PM
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
Dec 12 2022 12:26 AM
Dec 12 2022 12:55 AM
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.
Dec 12 2022 01:15 AM
Dec 12 2022 01:53 AM
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.
Dec 12 2022 02:00 AM
@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