Internet Table Transfer

[MBSNewbie]

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

 

[Guest]

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

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

 

[Guest]

There's a thread on here at

http://msdn.microsoft.com/newsgroup...6f2c66-6841-4617-8c24-a6a382460127&sloc=en-us

that does something similar. It also uses the Windows API.