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

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

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

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


Corrie wrote:

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

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

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


--
Regards,
Tom Ogilvy


"Corrie" wrote in message
ups.com...
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