I am assuming that you want to import and Excel file into an existing
worksheet. Is that right?
Sub Import()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim x As Long
Dim FilesToOpen
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Excel Files to Open")
Set Wb1 = ActiveWorkbook
For x = LBound(FilesToOpen) To UBound(FilesToOpen)
Set Wb2 = Workbooks.Open(Filename:=FilesToOpen(x))
Wb2.Worksheets.Copy _
After:=Wb1.Sheets(Wb1.Sheets.Count)
Wb2.Close False
Next x
'Sheets("Control Sheet").Select
End Sub
Next, turn on the macro recorder, click Data > Import External data > New
Web Query. Then, type any URL into the address bar. Search around until you
find what you want, click the little yellow arrow (next to the data that you
want to import) and it will turn into a green check box. Finally, click on
the 'Import' button in the lower right hand side of the active window.
I did this and got the code below:
Sub Macro1()
With
ActiveSheet.QueryTables.Add(Connection:="URL;
http://finance.yahoo.com", _
Destination:=Range("A1"))
.Name = "finance.yahoo"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Regards,
Ryan---