VBANoobNotNoob
New Member
My team gets a .CSV file emailed to us on an hourly basis, and I'm trying to write some VBA macros to automate our next steps with the file--my main obstacle is that the file opens with different file names on different computers. The email attachment is named "TodaysWires.csv", and some of my team members get that file name when they open it, but others don't. When I open it, for example, it opens as "TodaysWires(012).csv". Occasionally the number increases (not chronologically, either), but always seems to revert back to (012) the next day. The various names that the file uses on our computers all consistently fall within a "TodaysWires*.csv" range of names.
The code below gets the correct data if I copy/paste the entire .CSV sheet into Sheet3 of my active document, however, I *want* it to get the data from the open .csv file instead, and I can't figure out how to identify the correct file as long as it keeps opening with different file names on different computers. A workaround would be to use VBA to copy/paste the data to Sheet3, but I can't get that to work either, all related to that pesky file name variable.
The code below gets the correct data if I copy/paste the entire .CSV sheet into Sheet3 of my active document, however, I *want* it to get the data from the open .csv file instead, and I can't figure out how to identify the correct file as long as it keeps opening with different file names on different computers. A workaround would be to use VBA to copy/paste the data to Sheet3, but I can't get that to work either, all related to that pesky file name variable.
Code:
Private Sub Fetch_Wires_by_Time_button_Click()
Dim xRg As Range
Dim xCell As Range
Dim OriginalData As Long
Dim J As Long
Dim K As Long
OriginalData = Sheets("Sheet3").UsedRange.Rows.Count
J = Sheets("PasteHere").UsedRange.Rows.Count
If Wire_Time_List.Value = "9am" Then
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("PasteHere").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet3").Range("A2:A" & OriginalData)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) < 89000 Then
xRg(K).EntireRow.Copy Destination:=Worksheets("PasteHere").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
Unload Choose_Time_UF
Else
'continues with Else statements to cover all available times