Forum Discussion
opening a file from file dialog box
Hi Lorenzo,
The last one you mentioned is the best.
It seems that my suggestion is not perfect as it brings only the opened Excel files into the dropdown list.
However, I've updated the last code you mentioned this way so that it closes the source file after complete the process.
Also, I've deleted this variable because it's unused!
Dim mwrbk As Variant
This is the updated code:
Sub CopyFromSource2()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim strFileToOpen As String
strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="")
Set targetedWB = Workbooks.Open(strFileToOpen)
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Range("A2:J" & LastRow).Select
Selection.Copy
Windows("RAD Analyzer.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
targetedWB.Close SaveChanges:=False
Range("A2").Select
On Error GoTo 0
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End SubRegards
Hi Lorenzo,
You need to create a new UserForm like this:
And to use this code behind the UserForm:
After that, you have to modify the code you mentioned as follow:
Sub CopyFromSource()
' vba code for: open file dialog box then choose a file that will replace "SourceFile"
On Error Resume Next
Application.ScreenUpdating = False
Dim FileNameBoxInstance As New FileNameBox
FileNameBoxInstance.Show
Dim fileName As String
fileName = FileNameBoxInstance.ComboBox1.Value
Windows(fileName).Activate
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Range("A2:J" & LastRow - 1).Select
Selection.Copy
Windows("RAD Analyzer.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
On Error GoTo 0
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Please find all this in the attached file.
Regards
Mr. Amairah
Pardon me for bothering you again.. the issue about attaching a file to this message box..
I noticed that you have attached a file - How did you do it?
I am attaching the image of my message box -- is this the regular format?
many thanks
No problem.
As the screenshot you mentioned, the file MovieDatabase is already attached.
You can attach as many files as you want using the Choose Files Button.
Regards
Mr. Amairah
thank you for your time and assistance..