Help with GetFileName in code

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

I am having a problem with this line in my code:
Set f = fs.GetFileName(RFDSFile)
A Type mismatch error message.
Any ideas?
=>RFDSFile is a filepath

Sub RFDS_File(RFDSFile As String)
Dim fs, f

Workbooks.Open RFDSFile, ReadOnly:=True
Sheets("RFDS").Select
Worksheets("RFDS").Copy After:=Workbooks("RFDS Tracker GA Market_Form
4C.xls").Sheets("RFDS Tracker (2)")
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFileName(RFDSFile)
Workbooks(f).Close SaveChanges:=False

'Extract_RFDSData

'''''''''''' DELETE RFDS SHEET '''''''''''''''''''''''''''''''''''''''''''''''
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In Worksheets
If Left(sh.Name, 4) = "RFDS" Then
sh.Select
ActiveWindow.SelectedSheets.Delete
End If
Next sh
Worksheets("RFDS Tracker").Range("A4").Select
End Sub
 
I'd make sure you've got

Dim RFDSFile as String

in your original code. I generally put Option Explicit before any Subs in
each module so that it forces me to declare all variables. Some don't like
to play nicely if not declared properly.
 
This is the originalcode:
Private Sub cmdExtractData_Click()
Me.Hide
If Me.opt_File.Value = True Then
RFDS_File Me.txtFilePath.Value
ElseIf Me.opt_Folder.Value = True Then
Run "DoIt"
Application.ScreenUpdating = False
RFDS_Folder Me.txtFilePath.Value
Run "DoIt"
End If
End Sub

Me.txtFilePath.Value contains the file path
 
fs.getfilename will return a string. So you don't need (and can't use) "Set".

f = fs.GetFileName(RFDSFile)

But you don't need FSO to return the name of the file, either.

Dim RFDSWkbk as workbook
....

set rfdswkbk = workbooks.open(filename:=rfdsfile,readonly:=true)
msgbox rfdswkbk.name 'is sufficient
or avoid the name completely later:
rfdswkbk.close SaveChanges:=False

Or you could use use instrrev() to pick out the everything from the last \ in
the filename.

f = Mid(RFDSFile, InStrRev(RFDSFile, "\", -1, vbTextCompare) + 1)
 
Back
Top