ADODB Connection over HTTP

[LDB]

Hi,

I've currently got an ADODB connection to a CSV file on a fileserver
working over TCP-IP. My VBA looks something like this:

fname = "\\fileserver\directory\file.csv"

With resultLocation.Worksheet.QueryTables.Add(Connection:="TEXT;" &
fname, Destination:=resultLocation)
.attributes = blah
End With

However, the production of our CSV files is now automated as a cron
job on an AIX box and the only feasible way to access them (between
platforms) is accross HTTP. Can I get excel to easily connect over
this protocol, i.e. making my string filename string more like:

fname = "http://internal.server/directory/file.csv"

My first attempts have failed miserably - is this idea going anywhere?

Thanks,

Louis

 

[joeu2004]

I've currently got an ADODB connection to a CSV file on a fileserver
working over TCP-IP. My VBA looks something like this:

What book are you using?

I cannot find a book that has a good explanation of network access
using VBA.

fname = "\\fileserver\directory\file.csv"
With resultLocation.Worksheet.QueryTables.Add(Connection:="TEXT;" &
fname, Destination:=resultLocation)
.attributes = blah
End With
[....]
However, the production of our CSV files is now automated as a cron
job on an AIX box and the only feasible way to access them (between
platforms) is accross HTTP. Can I get excel to easily connect over
this protocol, i.e. making my string filename string more like:
fname = "http://internal.server/directory/file.csv"

When I get stuck, I turn on macro record mode and try a similar
operation -- in this case, web query. Here is what I got:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://bigcharts.marketwatch.com/historical",
Destination:=Range("A1"))

Hope that helps.

 

[LDB]

I've currently got an ADODB connection to a CSV file on a fileserver
working over TCP-IP. My VBA looks something like this:

What book are you using?

I cannot find a book that has a good explanation of network access
using VBA.

fname = "\\fileserver\directory\file.csv"
With resultLocation.Worksheet.QueryTables.Add(Connection:="TEXT;" &
fname, Destination:=resultLocation)
.attributes = blah
End With
[....]
However, the production of our CSV files is now automated as a cron
job on an AIX box and the only feasible way to access them (between
platforms) is accross HTTP. Can I get excel to easily connect over
this protocol, i.e. making my string filename string more like:
fname = "http://internal.server/directory/file.csv"

When I get stuck, I turn on macro record mode and try a similar
operation -- in this case, web query. Here is what I got:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://bigcharts.marketwatch.com/historical",
Destination:=Range("A1"))

Hope that helps.

Joe,

Thanks for your relpy.

Since posting I realised I can specify a 'URL' connection - this
works, however I cannot delimit by comma when I import! I don't really
want the whole line dumping in one cell.

Any other idea?

Thanks,
Louis

 

[joeu2004]

When I get stuck, I turn on macro record mode and try a similar
operation -- in this case, web query. Here is what I got:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://bigcharts.marketwatch.com/historical",
Destination:=Range("A1"))

[....]
Since posting I realised I can specify a 'URL' connection - this
works, however I cannot delimit by comma when I import! I don't really
want the whole line dumping in one cell.
Any other idea?

I am a relative "newbie" when it comes to network programming using
VBA. I'm afraid I don't know much about it.

But I can tell that you when I use Web Query on a table from a web
page, the data goes into separate cells.

So again, I would suggest that you do a Web Query with macro recording
mode enabled, then experiment with the many "extraneous" lines of VBA
code to see which ones, if any, are needed to distribute the data
among cells.

Then again, perhaps the problem is with the design of the web page,
not so much the VBA programming. I'm afraid I know nothing about HTML
programming either.

(Sigh, I'm a programming dinosaur. Ask me anything about Fortran ;-

. Actually, I've forgotten all that, too. But C .... ;->.)

I know there a lot of knowledgable VBA programmers in these forums who
can help. Hopefully they will chime in and give you dispositive
answers.

 

[LDB]

When I get stuck, I turn on macro record mode and try a similar
operation -- in this case, web query. Here is what I got:
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://bigcharts.marketwatch.com/historical",
Destination:=Range("A1"))

[....]
Since posting I realised I can specify a 'URL' connection - this
works, however I cannot delimit by comma when I import! I don't really
want the whole line dumping in one cell.
Any other idea?

I am a relative "newbie" when it comes to network programming using
VBA. I'm afraid I don't know much about it.

But I can tell that you when I use Web Query on a table from a web
page, the data goes into separate cells.

So again, I would suggest that you do a Web Query with macro recording
mode enabled, then experiment with the many "extraneous" lines of VBA
code to see which ones, if any, are needed to distribute the data
among cells.

Then again, perhaps the problem is with the design of the web page,
not so much the VBA programming. I'm afraid I know nothing about HTML
programming either.

(Sigh, I'm a programming dinosaur. Ask me anything about Fortran ;-

. Actually, I've forgotten all that, too. But C .... ;->.)

I know there a lot of knowledgable VBA programmers in these forums who
can help. Hopefully they will chime in and give you dispositive
answers.

Joe,

Thanks for your help so far.

Unfortunately, I can't (or don't want to) edit the source of the data
to be imported - they are CSV files created by a monthly cron job
which are being hosted by Apache.

I'm currently doing the VBA-translation of a 'Web Query' (i.e.
Connection:="URL;...") but that only wants to dump my data into one
column, I can't distribute it along the cells horizontally - maybe I
need to go through and seperate by comma afterwards? Although that
sounds like an annoying way considering the number of lines my CSV
could span varies...

Louis