GET FILE NAME
Example No.1
Sub GetFileName()
Dim FileName As String
Dim Path As String
Path= “C:\Users\Desktop\macro\macro.xlsx”
FileName= Right(Path, Len(Path) – InStrRev(Path, “\”))
End Sub
Example No.2
Sub GetFName2()
Dim FName As String
Dim PName As String
PName= "C:\Users\Desktop\macro\macro.xlsx"
FName = Mid(PName, InStrRev(PName, "\") + 1, _
InStrRev(PName, ".") - InStrRev(PName, "\") - 1)
End Sub
Example No.3
Sub GetFname3()
Dim FileName As String
Dim Elements() As String
Dim Path As String
Elements (UBound(Elements()))
Path= "C:\Users\Desktop\macro\macro.xlsx"
Elements() = Split(Path, "\")
Path = Elements
Example No.4
Sub GetFileName4()
Dim FileName As String
FileName = Dir("C:\Users\Documents\makro\makro.xlsx")
End Sub
Example No.5
Sub GetFileNameByFileDialog ()
Dim FileName As Variant
Dim FileFilter As String
Dim FilterIndex As Integer
Dim Title As String
FileFilter = "Excel Files (*.xlsx;*.xls),*xlsx;*.xls," & _ '1
"Excel Files XLSX (*.xlsx),*.xlsx," & _ '2
"Excel Files 97-2003 (*.xls),*.xls," & _ '3
"All Files (*.*),*.*" '4
FilterIndex = 4 ' We are chosing default filter - 4: all files
Title = "Open File "
FileName= Application.GetOpenFilename(FileFilter, FilterIndex, Title)
If FileName= False Then
MsgBox "No File was chosen! Try again. "
Exit Sub
End If
FileName= Right(FileName, Len(FileName) - InStrRev(FileName, "\"))
End Sub
Sub load_files()
Dim catalog As String, file As String
catalog = "C:\Users\Agnes\Desktop\Test\" ' ours file source
file= Dir(catalog & "*.txt")
While Plik <> ""
Worksheets.Add.Name = file
'we need to call our function/procedure
Call sav(file, catalog & file)
file= Dir
Wend
End Sub
Function sav(Asheet As String, Asource As String)
With Sheets(Asheet).QueryTables.Add(Connection:="TEXT;" _
& Asource, Destination:=Range("$A$1"))
.Name = arkusz
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 852
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Function
Heart4DataScience 