Help with GetFileName in code



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
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


'''''''''''' DELETE RFDS SHEET '''''''''''''''''''''''''''''''''''''''''''''''
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In Worksheets
If Left(sh.Name, 4) = "RFDS" Then
End If
Next sh
Worksheets("RFDS Tracker").Range("A4").Select
End Sub

Barb Reinhardt

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()
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

Dave Peterson

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 =,readonly:=true)
msgbox '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)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question