Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
How do I reference external data from a file, file name found in . | Excel Discussion (Misc queries) | |||
Refreshing External Data on File Open | Excel Programming | |||
Updating Data Retrieved from an HTML file | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |