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..
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
Thank you for your reply.
I'm sorry - my message might have been garbled.
Sometime ago I recv'd message saying that I earned so and so "subscriptions"..
I wonder what this means...
many many thanks
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..
Mr. Amairah
Thank you for your reply.
Your suggestion is very nice but I need to select files instead of typing them. I found an article (pls see code below) . It opens the file dialog box and I can choose the file I need, the only problem is how do I continue after I chose a file - that is to replace SourceFile with the selected file? I'm at a lost here.. your help would be most appreciated.
also How do you attach the file? Mr. Damien told me to find the Browse button below but there is only the Choose Files - is this the same? (I am attaching herewith an image of my message box).
Sub sbVBA_To_Open_Workbook_FileDialog()
Dim strFileToOpen As String
strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")
If strFileToOpen = False Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
Workbooks.Open Filename:=strFileToOpen
End If
End Sub- I would prefer this routine, It looks simpler
thank you Mr. Amairah Sub sbVBA_To_Open_Workbook_FileDialog()
Dim strFileToOpen As String
strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")
If strFileToOpen = False Then *** Error message is appearing here..
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
Workbooks.Open Filename:=strFileToOpen
End If
End Sub
