import based on partial filename

[jordanburch]

Hey guys



I have the below. Seems like an easy adjustment but I cant figure it out.

myfile = "CO21" & fn4 & ".xlsx"

the file name is CO21SEP21

I just want it to import the file that has the partial name of co21 vs co21sep21. Any thoughts?


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" & fn4 & ".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)

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('" & ShtName & "'!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

.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

 

[GWteB]

You could use

myfile = "CO21*.xlsx"

because you're checking its existence using VBA's Dir function. Note that Dir will give you only the first occurrence of a file that meets the given file specification.

 

[jordanburch]

You could use

myfile = "CO21*.xlsx"

because you're checking its existence using VBA's Dir function. Note that Dir will give you only the first occurrence of a file that meets the given file specification.

worked thanks! now I have to figure out why else it isnt working lol thanks!

 

[GWteB]

Glad to help.