Disconnected Recordset -- http://www.server.com/test.xls
Howdy,
I need to write a macro that pulls data from a closed excel spreadsheet on the internet into an open spreadsheet on my hard-drive. Is this possible using ADO or RDS? I have tried using ADODB.Recordset with Provider=MS Remote, but I could not get it to work. Can anyone give me a sample connection string for an online excel spreadsheet? Also, which object library should I be using? Any suggestions?
Thanks,
Paul
You can try this (Example is working)
Run it on a empty sheet
Sub a()
ActiveWorkbook.Names.Add Name:="web", RefersToR1C1:= _
"='http://www.rondebruin.nl/files/[test.xls]Sheet1'!R1C1:R10C1"
Range("a1:a10").Formula = "=web"
Range("a1:a10").Value = Range("a1:a10").Value
ActiveWorkbook.Names("web").Delete
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"paulharvey" <[email protected]> wrote in message
news:[email protected]...
>
> Howdy,
>
> I need to write a macro that pulls data from a closed excel spreadsheet
> on the internet into an open spreadsheet on my hard-drive. Is this
> possible using ADO or RDS? I have tried using ADODB.Recordset with
> Provider=MS Remote, but I could not get it to work. Can anyone give me
> a sample connection string for an online excel spreadsheet? Also, which
> object library should I be using? Any suggestions?
>
> Thanks,
> Paul
>
>
> --
> paulharvey
> ------------------------------------------------------------------------
> paulharvey's Profile: http://www.excelforum.com/member.php...o&userid=25227
> View this thread: http://www.excelforum.com/showthread...hreadid=393977
>
Thank you very much, Ron -- this was exactly what I needed.
Mmmm
The first time you run the sub it it is not working
If you run it twice it is working OK
Will look at today or tomorrow
Maybe others have better examples
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> You can try this (Example is working)
> Run it on a empty sheet
>
> Sub a()
> ActiveWorkbook.Names.Add Name:="web", RefersToR1C1:= _
> "='http://www.rondebruin.nl/files/[test.xls]Sheet1'!R1C1:R10C1"
> Range("a1:a10").Formula = "=web"
> Range("a1:a10").Value = Range("a1:a10").Value
> ActiveWorkbook.Names("web").Delete
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "paulharvey" <[email protected]> wrote in message
> news:[email protected]...
>>
>> Howdy,
>>
>> I need to write a macro that pulls data from a closed excel spreadsheet
>> on the internet into an open spreadsheet on my hard-drive. Is this
>> possible using ADO or RDS? I have tried using ADODB.Recordset with
>> Provider=MS Remote, but I could not get it to work. Can anyone give me
>> a sample connection string for an online excel spreadsheet? Also, which
>> object library should I be using? Any suggestions?
>>
>> Thanks,
>> Paul
>>
>>
>> --
>> paulharvey
>> ------------------------------------------------------------------------
>> paulharvey's Profile: http://www.excelforum.com/member.php...o&userid=25227
>> View this thread: http://www.excelforum.com/showthread...hreadid=393977
>>
>
>
This should do it Paul
Public Sub GetExcelData()
Const sFilename As String = "C:\myTest\Volker.xls"
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""
sSQL = "SELECT * FROM [Sheet1$]"
Set oRS = New ADODB.Recordset
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
If Not oRS.EOF Then
ActiveSheet.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If
oRS.Close
Set oRS = Nothing
End Sub
You will need to set areference to the Microsoft ACtiveX Data Objects
Library, and change the filename to suit.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"paulharvey" <[email protected]> wrote
in message news:[email protected]...
>
> Howdy,
>
> I need to write a macro that pulls data from a closed excel spreadsheet
> on the internet into an open spreadsheet on my hard-drive. Is this
> possible using ADO or RDS? I have tried using ADODB.Recordset with
> Provider=MS Remote, but I could not get it to work. Can anyone give me
> a sample connection string for an online excel spreadsheet? Also, which
> object library should I be using? Any suggestions?
>
> Thanks,
> Paul
>
>
> --
> paulharvey
> ------------------------------------------------------------------------
> paulharvey's Profile:
http://www.excelforum.com/member.php...o&userid=25227
> View this thread: http://www.excelforum.com/showthread...hreadid=393977
>
Oops, sorry I missed the bit about the internet, so it may not work, haven't
tried it.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"paulharvey" <[email protected]> wrote
in message news:[email protected]...
>
> Howdy,
>
> I need to write a macro that pulls data from a closed excel spreadsheet
> on the internet into an open spreadsheet on my hard-drive. Is this
> possible using ADO or RDS? I have tried using ADODB.Recordset with
> Provider=MS Remote, but I could not get it to work. Can anyone give me
> a sample connection string for an online excel spreadsheet? Also, which
> object library should I be using? Any suggestions?
>
> Thanks,
> Paul
>
>
> --
> paulharvey
> ------------------------------------------------------------------------
> paulharvey's Profile:
http://www.excelforum.com/member.php...o&userid=25227
> View this thread: http://www.excelforum.com/showthread...hreadid=393977
>
Do you have a idea Bob why the first time you run my tester it give errors in the cells and de second time not.
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
> Oops, sorry I missed the bit about the internet, so it may not work, haven't
> tried it.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "paulharvey" <[email protected]> wrote
> in message news:[email protected]...
>>
>> Howdy,
>>
>> I need to write a macro that pulls data from a closed excel spreadsheet
>> on the internet into an open spreadsheet on my hard-drive. Is this
>> possible using ADO or RDS? I have tried using ADODB.Recordset with
>> Provider=MS Remote, but I could not get it to work. Can anyone give me
>> a sample connection string for an online excel spreadsheet? Also, which
>> object library should I be using? Any suggestions?
>>
>> Thanks,
>> Paul
>>
>>
>> --
>> paulharvey
>> ------------------------------------------------------------------------
>> paulharvey's Profile:
> http://www.excelforum.com/member.php...o&userid=25227
>> View this thread: http://www.excelforum.com/showthread...hreadid=393977
>>
>
>
Hi Ron,
No possible idea as I don't get errors. First time and every time I got the
1-10 series.
Regards
Bob
"Ron de Bruin" <[email protected]> wrote in message
news:%[email protected]...
> Do you have a idea Bob why the first time you run my tester it give errors
in the cells and de second time not.
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> > Oops, sorry I missed the bit about the internet, so it may not work,
haven't
> > tried it.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "paulharvey" <[email protected]>
wrote
> > in message
news:[email protected]...
> >>
> >> Howdy,
> >>
> >> I need to write a macro that pulls data from a closed excel spreadsheet
> >> on the internet into an open spreadsheet on my hard-drive. Is this
> >> possible using ADO or RDS? I have tried using ADODB.Recordset with
> >> Provider=MS Remote, but I could not get it to work. Can anyone give me
> >> a sample connection string for an online excel spreadsheet? Also,
which
> >> object library should I be using? Any suggestions?
> >>
> >> Thanks,
> >> Paul
> >>
> >>
> >> --
> >> paulharvey
>
>> ------------------------------------------------------------------------
> >> paulharvey's Profile:
> > http://www.excelforum.com/member.php...o&userid=25227
> >> View this thread:
http://www.excelforum.com/showthread...hreadid=393977
> >>
> >
> >
>
>
Strange
If I close the file and reopen the first time I run the macro I have Ref errors in the cells.
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
> Hi Ron,
>
> No possible idea as I don't get errors. First time and every time I got the
> 1-10 series.
>
> Regards
>
> Bob
>
>
> "Ron de Bruin" <[email protected]> wrote in message
> news:%[email protected]...
>> Do you have a idea Bob why the first time you run my tester it give errors
> in the cells and de second time not.
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
>> > Oops, sorry I missed the bit about the internet, so it may not work,
> haven't
>> > tried it.
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "paulharvey" <[email protected]>
> wrote
>> > in message
> news:[email protected]...
>> >>
>> >> Howdy,
>> >>
>> >> I need to write a macro that pulls data from a closed excel spreadsheet
>> >> on the internet into an open spreadsheet on my hard-drive. Is this
>> >> possible using ADO or RDS? I have tried using ADODB.Recordset with
>> >> Provider=MS Remote, but I could not get it to work. Can anyone give me
>> >> a sample connection string for an online excel spreadsheet? Also,
> which
>> >> object library should I be using? Any suggestions?
>> >>
>> >> Thanks,
>> >> Paul
>> >>
>> >>
>> >> --
>> >> paulharvey
>>
>>> ------------------------------------------------------------------------
>> >> paulharvey's Profile:
>> > http://www.excelforum.com/member.php...o&userid=25227
>> >> View this thread:
> http://www.excelforum.com/showthread...hreadid=393977
>> >>
>> >
>> >
>>
>>
>
>
Bob, when I assign sFilename a spreadsheet on the internet http://www.rondebruin.nl/files/test.xls I receive the error "Invalid internet address". Do I need to change the provider? I tried using "Provider=MS Remote", but I receive an RDS security error.
Originally Posted by Bob Phillips
Paul,
As I mentioned in a follow-up, I missed the internet bit. I am afraid this
just won't work. Ron's does though.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"paulharvey" <[email protected]> wrote
in message news:[email protected]...
>
> Bob, when I assign sFilename a spreadsheet on the internet
> http://www.rondebruin.nl/files/test.xls I receive the error "Invalid
> internet address". Do I need to change the provider? I tried using
> "Provider=MS Remote", but I receive an RDS security error.
>
> Bob Phillips Wrote:
> > This should do it Paul
> >
> > Public Sub GetExcelData()
> > Const sFilename As String = "C:\myTest\Volker.xls"
> > Dim oConn As ADODB.Connection
> > Dim oRS As ADODB.Recordset
> > Dim sFilename As String
> > Dim sConnect As String
> > Dim sSQL As String
> >
> > sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=" & sFilename & ";" & _
> > "Extended Properties=""Excel 8.0;HDR=Yes"""
> >
> > sSQL = "SELECT * FROM [Sheet1$]"
> >
> > Set oRS = New ADODB.Recordset
> > oRS.Open sSQL, sConnect, adOpenForwardOnly, _
> > adLockReadOnly, adCmdText
> >
> > If Not oRS.EOF Then
> > ActiveSheet.Range("A1").CopyFromRecordset oRS
> > Else
> > MsgBox "No records returned.", vbCritical
> > End If
> >
> > oRS.Close
> > Set oRS = Nothing
> >
> > End Sub
> >
> > You will need to set areference to the Microsoft ACtiveX Data Objects
> > Library, and change the filename to suit.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "paulharvey" <[email protected]>
> > wrote
> > in message
> > news:[email protected]...
> > >
> > > Howdy,
> > >
> > > I need to write a macro that pulls data from a closed excel
> > spreadsheet
> > > on the internet into an open spreadsheet on my hard-drive. Is this
> > > possible using ADO or RDS? I have tried using ADODB.Recordset with
> > > Provider=MS Remote, but I could not get it to work. Can anyone give
> > me
> > > a sample connection string for an online excel spreadsheet? Also,
> > which
> > > object library should I be using? Any suggestions?
> > >
> > > Thanks,
> > > Paul
> > >
> > >
> > > --
> > > paulharvey
> > >
> > ------------------------------------------------------------------------
> > > paulharvey's Profile:
> > http://www.excelforum.com/member.php...o&userid=25227
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=393977
> > >
>
>
> --
> paulharvey
> ------------------------------------------------------------------------
> paulharvey's Profile:
http://www.excelforum.com/member.php...o&userid=25227
> View this thread: http://www.excelforum.com/showthread...hreadid=393977
>
Ron,
Do you know what modifications to your code are necessary to offset the values by a certain number of rows? For example, I want the list pulled from "A1:A10" on the web and placed into "A11:A20" of the active sheet.
Thanks,
Paul
Hi Paul
No, Use another sheet to dump your cells in first ( I use Sheet2 in my example)
There is no problem anymore when you run the macro the first time, see MyWait 1 in the code
that call the MyWait macro.
Sub Test2()
ActiveWorkbook.Names.Add Name:="web", RefersToR1C1:= _
"='http://www.rondebruin.nl/files/[test.xls]Sheet1'!R1C1:R10C1"
Sheets("Sheet2").Range("a1:a10").Formula = "=web"
MyWait 1
Sheets("Sheet1").Range("a11:a20").Value = Sheets("Sheet2").Range("a1:a10").Value
Sheets("Sheet2").Range("a1:a10").Clear
ActiveWorkbook.Names("web").Delete
End Sub
Sub MyWait(PauseSeg As Double)
' Orlando Magalhães Filho
Dim Start
Start = Timer
Do While Timer < Start + PauseSeg
DoEvents
Loop
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"paulharvey" <[email protected]> wrote in message
news:[email protected]...
>
> Ron,
>
> Do you know what modifications to your code are necessary to offset the
> values by a certain number of rows? For example, I want the list pulled
> from "A1:A10" on the web and placed into "A11:A20" of the active sheet.
>
> Thanks,
> Paul
>
>
> --
> paulharvey
> ------------------------------------------------------------------------
> paulharvey's Profile: http://www.excelforum.com/member.php...o&userid=25227
> View this thread: http://www.excelforum.com/showthread...hreadid=393977
>
Thank you, Ron... you've been a great help. I have everything I need now. I like your website too... lots of neat little add-ins.
Thanks,
Paul
Hi Paul
Thanks for the feedback
--
Regards Ron de Bruin
http://www.rondebruin.nl
"paulharvey" <[email protected]> wrote in message
news:[email protected]...
>
> Thank you, Ron... you've been a great help. I have everything I need
> now. I like your website too... lots of neat little add-ins.
>
> Thanks,
> Paul
>
>
> --
> paulharvey
> ------------------------------------------------------------------------
> paulharvey's Profile: http://www.excelforum.com/member.php...o&userid=25227
> View this thread: http://www.excelforum.com/showthread...hreadid=393977
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks