Forum Discussion

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Jul 10, 2018
Solved

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 f...
excel
Macros and VBA
  • Haytham Amairah's avatar
    Haytham Amairah
    Jul 11, 2018

    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 Sub

     

    Regards

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Jul 10, 2018

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

 

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Jul 11, 2018

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 

.. 

Resources