• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Emailed file opens with different file names on different computers, how to set variable?

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.

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
 
You could save the .CSV file to an empty folder. the open the *.csv file. Since its the only one in the folder ....
 
I eventually found what worked on my own. Turns out I needed to use a wildcard for both the "TodaysWires" file that gets emailed AND the working file because we occasionally add to the database it uses and we need to add version numbers to ensure everyone is using the up to date file--ie "Working File v3" and "Working File v4". My workaround pastes the CSV sheet into a hidden sheet in my working file (Sheet3), allowing my previous code that referenced the code there to work without changing it. I added this code to the macro that opens the userform referenced in the above code:


Code:
Sub Get_CSV_Data()

Dim w As Workbook
For Each w In Application.Workbooks
  If (w.Name) Like "TodaysWires*" Then
     Exit For
  End If
Next w
If Not w Is Nothing Then
w.Activate

Range("A:$O").Select

Selection.Copy

For Each w In Application.Workbooks
  If (w.Name) Like "WorkingFile*" Then
     Exit For
  End If
Next w
If Not w Is Nothing Then
w.Activate
  End If
Worksheets("Sheet3").Activate

Selection.PasteSpecial

Else
    MsgBox "No Todays Wires workbook open!"

End If

Choose_Time_UF.Show

End Sub
 
Back
Top