insert filename into column

[jordanburch]

Hey all,

I have the below. It searches in a file folder for a partial filename and then opens that file, now the issue im having is that the partial filename is only showing up in a column in excel. I need it to insert the actual filename in the column and not just the partial filename.

myfile = "CO21*.xlsx"

this is where it inserts the filename

wb2.Sheets(2).Range("q2:q1000").Value = myfile

Instead of myfile I just need it to be the actual filename. Any thoughts?
Thanks

Jordan

Sub COSARimportfinal21currentmonth()
Dim myfile As String
Dim myfile2 As String
Dim erow As Long
Dim filepath As String
Dim filepath2 As String
Dim wb1 As Workbook, wb2 As Workbook

    Dim fn As String
    Dim fn2 As String
    Dim fn3 As String
    Dim fn4 As String
    Dim ShtName1 As String
    Dim ShtName2 As String
    Dim ShtName3 As String
'    ShtName1 = Sheets(2).Name
    ShtName2 = "Detail"
    ShtName3 = "Detail -"
    
fn = Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6)
fn2 = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 2)
fn3 = Right(ThisWorkbook.Worksheets("Variables").Range("A6").Value, 2)


Application.ScreenUpdating = False
Worksheets.Add(After:=Worksheets(1)).Name = "CO SAR"
Set wb1 = ThisWorkbook

fn4 = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 5)
filepath = "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\21 Field Details\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & ThisWorkbook.Worksheets("Variables").Range("A1").Value & "\Field Detail Lines\"

myfile = "CO21*.xlsx"
 
Dim strFileName As String
Dim strFileExists As String

    strFileName = filepath & myfile
    strFileExists = Dir(strFileName)

   If strFileExists = "" Then
           MsgBox "The current month 21 CO SAR file does not exist"
           
    Else
    erow = wb1.Sheets("CO SAR").Cells(Rows.Count, 14).End(xlUp).Offset(1, 0).Row
    Set wb2 = Workbooks.Open(filepath & myfile)
    ShtName1 = Sheets(2).Name
    With wb2
    
     Sheets(2).Select
     With ActiveSheet
    If .AutoFilterMode Then
        If .FilterMode Then
            .ShowAllData
        End If
    Else
        If .FilterMode Then
            .ShowAllData
        End If
    End If
    End With
'    Dim ShtName As String
'ShtName = Sheets(2).Name
If Evaluate("isref('" & ShtName1 & "'!A1)") Then
   'sheet exists do something
Else
   'sheet doesn't exist do something else
End If
   If Evaluate("isref('" & ShtName1 & "'!A1)") Then
    wb2.Sheets(2).Range("q2:q1000").Value = myfile.Name
        .Sheets(2).Range("c2:q1000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
        
        .Close savechanges:=False
        ElseIf Evaluate("isref('" & ShtName3 & "'!A1)") Then
        .Sheets(2).Range("c2:p1000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
        .Close savechanges:=False
        
        ElseIf Evaluate("isref('" & ShtName2 & "'!A1)") Then
        .Sheets(2).Range("c2:p1000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
        .Close savechanges:=False
        
        End If
        
        
      End With
    End If
   
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Try

wb2.Sheets(2).Range("q2:q1000").Value = wb2.Name

 

[jordanburch]

Try

wb2.Sheets(2).Range("q2:q1000").Value = wb2.Name

thanks FLUFF! another winner here!

Jordan

 

[Fluff]

Glad to help & thanks for the feedback.