Hi, I've got the following Import macro, that imports data from another Excel file:
Sub Import()
Dim File As Variant
Dim Filename As Variant
Dim Master As Variant
MsgBox "Choose file", vbOKOnly
File = Application.GetOpenFilename("Excel files (*.xls),*.xls", , "Open an Excel file...")
If Not File = False Then Workbooks.Open (File)
Master = ThisWorkbook.Name
Filename = Right(File, 20)
Sheets(1).Activate
Range("G11").Select
Selection.Copy
Windows(Master).Activate
Range("G11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(Filename).Activate
Range("G19").Select
Selection.Copy
Windows(Master).Activate
Range("G19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(Filename).Activate
ActiveWorkbook.Close False
MsgBox "Import completed"
End Sub
Everything works fine as long as the file name consists of 20 (or whatever number we will put there) signs (including .xls) due to this line Filename = Right(File, 20).
Is there a way to change the code in a way that it will just get the file name without specifying its exact number of signs?
Thanks in advance!
Bookmarks