Open external data (varying file names) and closing file once data is retrieved

[Corrie]

I'm creating a tool that will be used by multiple users. Although the
external data will be same, the users may save it differently. I
thought I would use GetOpenFileName so that the user would select there
own file. However, I don't know how to close the file once the data
has been copied and pasted into "Daily DL" tab. I am using the
following code but it locks up... Any ideas?

Sub ImportDaily()
Dim WorkbookName As String
Dim FileToOpen As String
Dim WorkbookName1 As String

Sheets("Daily DL").Select
Cells.Select
Selection.Clear
Selection.FormatConditions.Delete
Selection.Interior.ColorIndex = xlNone
Range("A1").Select
WorkbookName = Range("A1").Parent.Parent.Name
MsgBox "Please select the file with the Daily Route data you wish
to import."
FileToOpen = Application _
.GetOpenFilename("excel(*.xls), *.xls")
If FileToOpen = "False" Then
End If
Workbooks.Open Filename:=FileToOpen
Range("A1").Select
WorkbookName1 = Range("A1").Parent.Parent.Name
Cells.Select
Selection.Copy
Windows(WorkbookName).Activate
Range("A1").Select
ActiveSheet.Paste
Windows(WorkbookName1).Close
Windows(WorkbookName).Activate
Range("A1").Select

 

[Dave Peterson]

You can use some object variables that represent the worksheet and workbook.
Then you can refer to them later in the code:

Option Explicit
Sub ImportDaily()

Dim DailyDLWks As Worksheet
Dim FileToOpen As Variant
Dim ImportWkbk As Workbook

Set DailyDLWks = Worksheets("Daily DL")

With DailyDLWks
.Cells.Clear
'doesn't .clear clean up this stuff, too???
.Cells.FormatConditions.Delete
.Cells.Interior.ColorIndex = xlNone
End With

FileToOpen = Application.GetOpenFilename _
(filefilter:="Excel Files, *.xls", _
Title:="Please select the file with the " & _
"Daily Route data you wish to import.")

If FileToOpen = False Then
'do nothing
Else
Set ImportWkbk = Workbooks.Open(Filename:=FileToOpen)
With ImportWkbk.Worksheets(1) 'or use the name of the worksheet?
.Cells.Copy _
Destination:=DailyDLWks.Range("a1")
End With
ImportWkbk.Close savechanges:=False
End If

End Sub

 

[Tom Ogilvy]

Sub ImportDaily()
Dim WorkbookName As String
Dim FileToOpen As String
Dim sh as Worksheet, sh1 as Worksheet

set sh = Sheets("Daily DL")
sh.Cells.clear
sh.cells.FormatConditions.Delete
sh.Cells.Interior.ColorIndex = xlNone
WorkbookName = sh.Parent.Name
MsgBox "Please select the file with the" & _
" Daily Route data you wish to import."
FileToOpen = Application _
.GetOpenFilename("excel(*.xls), *.xls")
If FileToOpen = "False" Then exit sub
Workbooks.Open Filename:=FileToOpen
set sh1 = Activeworkbook.Worksheets(1)
sh1.cells.copy sh.Range("A1")
sh1.parent.Close Savechanges:=False
sh.Select
sh.Range("A1").Select
End Sub