Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with GetFileName in code
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. -- HTH, Barb Reinhardt "Ayo" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with GetFileName in code
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 "Barb Reinhardt" wrote: 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. -- HTH, Barb Reinhardt "Ayo" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with GetFileName in code
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) Ayo wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
Code expantion , with code! | Excel Discussion (Misc queries) | |||
Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |