Forum Discussion
opening a file from file dialog box
The SUB below copies a range from a worksheet of another workbook (selected from file dialog box) then paste it (values only) on the opened "RaD Analyzer.xlsm" file..
I am requesting for a vba for file dialog box to appear then choose a file (excel) instead of typing the filename for the "SourceFile".
many many thanks
Sub CopyFromSource()
** vba code for: open file dialog box then choose a file that will replace "SourceFile"
Windows("SourceFile").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
End Sub
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
16 Replies
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 SubPlease 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
EUREKA !!!
after so much trial - I think I get it..
would you kindly check if it is so.
thank you for your time...
Sub CopyFromSource()
Dim strFileToOpen As String
Dim mwrbk As Variant
strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="")
Workbooks.Open Filename:=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("Copy of RAD Analyzer.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
End SubHi 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
Mr. Amairah
I'm hope below is possible..
many many thanks
Sub CopyFromSource()
Dim strFileToOpen As String
strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="") 'FileFilter:="Excel Files *.xls* (*.xls*),")
'** do not show opening of file by Application.EnableEvents = False?
'** opened file = mfilename
Windows(mfilename).Activate
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
Range("A2").Select
End Sub..
