Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Internet Table Transfer
Hi Everyone,
Does anyone have any experience trying to retreive data from the web into Excel without using web query? I've been trying for a few weeks and can only get as far as Excel Data into the Internet, I manually select the data and copy then resume the macro.I'm not sure how to get the info from the Internet into Excel. I'm not using any add-ins. I hope this clarifies my earlier question, but I'm crunched for time and I'm hoping there is someone out there who can help me. Thanks!!! Const url As String = "https://www65.americanexpress.com/opm/en_US/ViewStatement" Dim ie As Object, sampleData(1 To 2) As String, cTables As Object, X As Integer Dim MyArr As Variant, cl As Range, y As Worksheet, Account As String '///Setting For Each cl In Range([a2], [a33].End(3)) Account = cl.Value Set ie = CreateObject("internetexplorer.application") With ie Dim ws As Worksheet Dim newSheetName As String .Visible = True .navigate url Do While .ReadyState < 4: Loop '///Send data to IE With .Document.all .acct.Value = Account .submit1.Click Do While ie.ReadyState < 4: Loop End With End With Sheets.Add Type:="Worksheet" With ActiveSheet .Move After:=Worksheets(Worksheets.Count) .Name = cl.Value End With X = 1 For X = 1 To 10 Answer = MsgBox("Would You Like To Paste?", vbYesNo) If Answer = vbYes Then ActiveSheet.Paste ActiveCell.SpecialCells(xlLastCell).Select Else: Cells.Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit Range("A1").Select Exit For End If Next X Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Internet Table Transfer
I have used URLDownloadTo File with some sucess. You could use this to save
the html file to your local machine. I would open the file and parse through it and write the information to a worksheet or some other Excel object. URLDownloadToFile is an api function. More information can be found at the MSDN web site. ----------------------------------------------------------------------------------- Downloads bits from the Internet and saves them to a file. Syntax HRESULT URLDownloadToFile( LPUNKNOWN pCaller, LPCTSTR szURL, LPCTSTR szFileName, DWORD dwReserved, LPBINDSTATUSCALLBACK lpfnCB ); Parameters pCaller Pointer to the controlling IUnknown interface of the calling Microsoft ActiveX component (if the caller is an ActiveX component). If the calling application is not an ActiveX component, this value can be set to NULL. Otherwise, the caller is a Component Object Model (COM) object that is contained in another component (such as an ActiveX control within the context of an HTML page). This parameter represents the outermost IUnknown of the calling component. The function attempts the download within the context of the ActiveX client framework and allows the caller's container to receive callbacks on the progress of the download. szURL Pointer to a string value containing the URL to be downloaded. Cannot be set to NULL. If the URL is invalid, INET_E_DOWNLOAD_FAILURE is returned. szFileName Pointer to a string value containing the name of the file to create for bits that come from the download. dwReserved Reserved. Must be set to 0. lpfnCB Pointer to the caller's IBindStatusCallback interface. URLDownloadToFile calls this interface's IBindStatusCallback::OnProgress method on a connection activity, including the arrival of data. IBindStatusCallback::OnDataAvailable is never called. Implementing IBindStatusCallback::OnProgress allows a caller to implement a user interface or other progress monitoring functionality. It also allows the download operation to be canceled by returning E_ABORT from the IBindStatusCallback::OnProgress call. This can be set to NULL. Return Value Returns one of the following values. S_OK The operation succeeded. E_OUTOFMEMORY The buffer length is invalid or there was insufficient memory to complete the operation. INET_E_DOWNLOAD_FAILURE The download of the specified resource has failed. Remarks The client can choose to be notified of progress through a notification callback. Function Information Stock Implementation urlmon.dll Custom Implementation No Header Urlmon.h Import library Urlmon.lib Minimum availability Internet Explorer 3.0 Minimum operating systems Windows NT 4.0, Windows 95 Unicode Implemented as ANSI and Unicode versions. -------------------------------------------------------------------------------- Just my 2 cents Paul "MBSNewbie" wrote: Hi Everyone, Does anyone have any experience trying to retreive data from the web into Excel without using web query? I've been trying for a few weeks and can only get as far as Excel Data into the Internet, I manually select the data and copy then resume the macro.I'm not sure how to get the info from the Internet into Excel. I'm not using any add-ins. I hope this clarifies my earlier question, but I'm crunched for time and I'm hoping there is someone out there who can help me. Thanks!!! Const url As String = "https://www65.americanexpress.com/opm/en_US/ViewStatement" Dim ie As Object, sampleData(1 To 2) As String, cTables As Object, X As Integer Dim MyArr As Variant, cl As Range, y As Worksheet, Account As String '///Setting For Each cl In Range([a2], [a33].End(3)) Account = cl.Value Set ie = CreateObject("internetexplorer.application") With ie Dim ws As Worksheet Dim newSheetName As String .Visible = True .navigate url Do While .ReadyState < 4: Loop '///Send data to IE With .Document.all .acct.Value = Account .submit1.Click Do While ie.ReadyState < 4: Loop End With End With Sheets.Add Type:="Worksheet" With ActiveSheet .Move After:=Worksheets(Worksheets.Count) .Name = cl.Value End With X = 1 For X = 1 To 10 Answer = MsgBox("Would You Like To Paste?", vbYesNo) If Answer = vbYes Then ActiveSheet.Paste ActiveCell.SpecialCells(xlLastCell).Select Else: Cells.Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit Range("A1").Select Exit For End If Next X Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Internet Table Transfer
There's a thread on here at
http://msdn.microsoft.com/newsgroups...127&sloc=en-us that does something similar. It also uses the Windows API. -- www.alignment-systems.com "MBSNewbie" wrote: Hi Everyone, Does anyone have any experience trying to retreive data from the web into Excel without using web query? I've been trying for a few weeks and can only get as far as Excel Data into the Internet, I manually select the data and copy then resume the macro.I'm not sure how to get the info from the Internet into Excel. I'm not using any add-ins. I hope this clarifies my earlier question, but I'm crunched for time and I'm hoping there is someone out there who can help me. Thanks!!! Const url As String = "https://www65.americanexpress.com/opm/en_US/ViewStatement" Dim ie As Object, sampleData(1 To 2) As String, cTables As Object, X As Integer Dim MyArr As Variant, cl As Range, y As Worksheet, Account As String '///Setting For Each cl In Range([a2], [a33].End(3)) Account = cl.Value Set ie = CreateObject("internetexplorer.application") With ie Dim ws As Worksheet Dim newSheetName As String .Visible = True .navigate url Do While .ReadyState < 4: Loop '///Send data to IE With .Document.all .acct.Value = Account .submit1.Click Do While ie.ReadyState < 4: Loop End With End With Sheets.Add Type:="Worksheet" With ActiveSheet .Move After:=Worksheets(Worksheets.Count) .Name = cl.Value End With X = 1 For X = 1 To 10 Answer = MsgBox("Would You Like To Paste?", vbYesNo) If Answer = vbYes Then ActiveSheet.Paste ActiveCell.SpecialCells(xlLastCell).Select Else: Cells.Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit Range("A1").Select Exit For End If Next X Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Internet transfer controls | Excel Discussion (Misc queries) | |||
microsoft internet transfer control | Excel Programming | |||
Microsoft Internet Transfer control with excel 2003 | Excel Programming | |||
Send Files To FTP - Can't use Internet Transfer Control ActiveX | Excel Programming | |||
internet data transfer library | Excel Programming |