jordanburch
Active Member
- Joined
- Jun 10, 2016
- Messages
- 443
- Office Version
- 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
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