insert filename into column

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
401
Office Version
  1. 2016
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

VBA Code:
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,244
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
wb2.Sheets(2).Range("q2:q1000").Value = wb2.Name
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,244
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,393
Messages
5,769,824
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top