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
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 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
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
Hi Lorenzo,
I think you mean the weekly digest email?
I receive such an email weekly (every Sunday).
This email notifies you about new topics or articles posted in boards you subscribe to
- Mr. Amairah pardon my butting in the query below. I'd posted it at the forum for quite sometime now and haven't had any reply. I am just curious what the statement below means. if you would indulge me on this... many many thanks "you have earned so and so subscriptions"
Hi Lorenzo,
What is the statement you are asking for?
Mr. Amairah
Thank you for your time and assistance.
more power to you..