Allow user select file to copy data from "Application.GetOpenFilen

[Rob Moore]

I have been working on a macro that will prompt the user to select a file and
then copy and paste (special values) that entire worksheet into a new
worksheet "old file". I have tried 'Application.GetOpenFilename' which
prompts me to select the file to copy but that file never opens up. Any
ideas.

Thanks,
Rob

 

[PY & Associates]

'Application.GetOpenFilename' only gets the filename but does nothing. The
second step is to

workbooks.open filename:= #### to open it.

 

[Rowan]

An example:

Private Sub OpenFile()

Dim flToOpen As Variant
Dim fFilter As String

fFilter = "Excel Files (*.xls), *.xls"
flToOpen = Application.GetOpenFilename(fFilter)

If flToOpen = False Then
Exit Sub 'if user hits cancel then macro ends
Else
Workbooks.Open Filename:=flToOpen
End If

End Sub

Hope this helps
Rowan

 

[Mike Fogleman]

'Application.GetOpenFilename' only gets the path to the file selected. It
does not open it. This should be real close to what you want.

Option Explicit
Dim FileToOpen As String
Dim MyBook As String
Dim OldBook As String

Sub CopyBookToBook()
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

MyBook = ActiveWorkbook.Name
FileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If FileToOpen = "False" Then
Exit Sub
End If
Workbooks.Open (FileToOpen)
OldBook = ActiveWorkbook.Name
Worksheets("Sheet1").UsedRange.Copy
Workbooks(MyBook).Worksheets("Old File").Range("A1").PasteSpecial (xlValues)
Workbooks(OldBook).Close
Range("A1").Select
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub


Mike F