VB to import data from text file

[sharon]

Hi all,

Having this piece of code:

Sub ReadStraightTextFile()
Dim str As String
Dim lin As String
Dim bytArray() As Byte
Dim intcount As Integer
Dim col As Long

Open "C:\wesensors.xml" For Input As #1
col = 0
Line Input #1, strTest
lin = "sensor=" & Chr(34) & "sunset" & Chr(34) & " cat=" & Chr(34) &
"standard" & Chr(34) & " unit=" & Chr(34) & "local" & Chr(34) & ""
col = InStr(1, strTest, lin, vbTextCompare)

str = Mid(strTest, col + 44, 5)
Close #1
End Sub

How can I return str value to a text box named 'SUNSET" in active Publisher
doc?
perhaps I need to add a line similar to:
active.document.SUNSET.VALUE=str ??

Do I need to declare text box into the macro?

Any help would be appreciated

sharon

 

[Ed Bennett]

How can I return str value to a text box named 'SUNSET" in active
Publisher doc? perhaps I need to add a line similar to:
active.document.SUNSET.VALUE=str ??

First port of call would be the Publisher object model documentation.
Hit F1 in the VBA editor to get access to that.

You can create a text box with the following code:

Dim myNewTextBox as Shape
Set myNewTextBox =
ActiveDocument.ActiveView.ActivePage.Shapes.AddTextBox([insert parameters])

You can then add text with:

myNewTextBox.TextFrame.TextRange.Text = str

Alternatively, you can reference an existing text box with:

Set myNewTextBox = ActiveDocument.Pages(PAGENUMBER).Shapes(SHAPENUMBER)

If you're using the VBA editor, you should use "ThisDocument" instead of
"ActiveDocument" to refer to the document to which the VBA project is
attached.

 

[sharon]

Thanks Ed for you reply.

I almost succeeded but now I'm in trouble trying to open the file in the
web, so instead of:
Open "C:\wesensors.xml" For Input As #1
doing this in:
http://www.webname.com/data/wesensors.xml

How to do it?

sharon

How can I return str value to a text box named 'SUNSET" in active
Publisher doc? perhaps I need to add a line similar to:
active.document.SUNSET.VALUE=str ??

First port of call would be the Publisher object model documentation.
Hit F1 in the VBA editor to get access to that.

You can create a text box with the following code:

Dim myNewTextBox as Shape
Set myNewTextBox =
ActiveDocument.ActiveView.ActivePage.Shapes.AddTextBox([insert parameters])

You can then add text with:

myNewTextBox.TextFrame.TextRange.Text = str

Alternatively, you can reference an existing text box with:

Set myNewTextBox = ActiveDocument.Pages(PAGENUMBER).Shapes(SHAPENUMBER)

If you're using the VBA editor, you should use "ThisDocument" instead of
"ActiveDocument" to refer to the document to which the VBA project is
attached.

 

[Ed Bennett]

I almost succeeded but now I'm in trouble trying to open the file in the
web, so instead of:
Open "C:\wesensors.xml" For Input As #1
doing this in:
http://www.webname.com/data/wesensors.xml

How to do it?

Download the file to a temporary folder, and open it from there?

 

[sharon]

Well, I tried this already but it seems to me that OPEN sentence doesn't
accept an URL address...

I have a FTP which updates the file every 5 minutes so I want to read the
updated file. It says file not find.

It should work this way?
Open "http://www.webname.com/data/wesensors.xml" FOR INPUT AS #1

???

sharon

 

[Ed Bennett]

Well, I tried this already but it seems to me that OPEN sentence doesn't
accept an URL address...

I have a FTP which updates the file every 5 minutes so I want to read the
updated file. It says file not find.

It should work this way?
Open "http://www.webname.com/data/wesensors.xml" FOR INPUT AS #1

No, download the file using a download method to a temporary folder,
then supply the path to the file in the temporary folder to the method.

 

[sharon]

Thanks for your help.

Is there a download method in Publisher VBA? I couldn't find..

Can you give me some light on it?

TIA,
sharon

 

[Ed Bennett]

Is there a download method in Publisher VBA? I couldn't find..

Can you give me some light on it?

This page suggests you need to use API calls, but gives a complete
implementation:
http://www.devx.com/vb2themax/Tip/19203

 

[sharon]

But as far as I understand this doesn't solve my question.

' Download a file from Internet and save it to a local file
'
' it works with HTTP and FTP, but you must explicitly include
' the protocol name in the URL, as in
' CopyURLToFile "http://www.vb2themax.com/default.asp", "C:\vb2themax.htm"

This gives me a way to download a file to a local source... but I want to
open from Publisher local path (which I have in a URL location). Maybe the
problem is how I want to implement the solution.

What I try to do is having my Publisher file in
www.webname.com/data/MyPub.pub I publish it and in the same folder I have
already updated the file I want to Open from VBA and then extract parts of it
and show them in a text.frame. This works fine when I do this in my own PC
but fails when trying to do from my web folder.

Is my design possible to implement? (I'm just a novice )

sharon

 

[Ed Bennett]

This gives me a way to download a file to a local source... but I want to
open from Publisher local path (which I have in a URL location). Maybe the
problem is how I want to implement the solution.

You cannot open stuff directly from the web, you have to download it to
a temporary folder on your local disk and open the file saved on the disk.

 

[sharon]

I understand this is a just limitation for Publisher and opening a file in
VBA, because obviously one can open, with a link, any file in a internet
folder.

Any suggestions? It must be a way for doing this... I want any user can see
data from a file. Using Publisher is possible?

sharon

 

[Ed Bennett]

I understand this is a just limitation for Publisher and opening a file in
VBA, because obviously one can open, with a link, any file in a internet
folder.

If it is possible in Publisher to open a file direct from a URL, it's
highly inadvisable. And what is happening behind-the-scenes is that
Publisher downloads the file to a temporary location on disk and opens
it from there.

My suggestion, as before, is to download the file to the temporary
folder and open the data from there. This can be done as often and as
many times as you need. To the user it will appear as if the data came
straight from the internet.

 

[sharon]

Ok. My problem is I don't understand what you mean.

I already have the file updated to a folder, but when I try to identify this
folder it has an URL address.

I have also this file on my system, but I understand I need to have
mypub.pub published in a server's (external) folder as index.htm, the same
folder where I try to open from VBA this file.

Set pbShape = ThisDocument.Pages(1).Shapes(24)
tit = ThisDocument.Path & "wesens.xml"
Open tit For Input As #1

here crashes since tit="http://www.webname.com/data/wesens.xml"

So, shortly, how to make understand VBA the file is in the same folder
index.htm is. When you say file needs to be in a local folder, I guess LOCAL
could be same folder where index.htm is placed.

Otherwise if, as I understood before, my file needs to be in a local path,
then I understand nothing.

Sorry, I can't see it...

sharon

 

[Ed Bennett]

Otherwise if, as I understood before, my file needs to be in a local path,
then I understand nothing.

Sorry, I can't see it...

LOCAL means ON A DISK. One that has either a drive letter or a UNC path.
One that is attached to your computer. A path that has backslashes in
it. Not a URL. You can't give it a URL. Anything that starts with "http"
or "ftp", and/or has a "://" in it, cannot be passed.

If the file is at the http:// location, you have to DOWNLOAD IT to a
TEMPORARY FOLDER ON YOUR HARD DRIVE, and then pass the file in the
temporary folder to your function. You can get the path to the user's
temporary folder using this code:
http://vbadud.blogspot.com/2007/04/visual-basic-function-to-get-temporary.html

 

[sharon]

Hi Ed,
Thanks for your help.

As I understand everything you say here but not how to make my design, I
posted a new question in Web Design thread "Publishing with Publisher 2003".

I'll come back to this thread when I know if I my question has a real answer
for me ;-)

Thanks a lot,
sharon

 

[Fred]

You cannot open stuff directly from the web, you have to download it to a

temporary folder on your local disk and open the file saved on the disk.

Hi Ed,
That's not strictly true. Although I don't think it will help Sharon, it's
fairly simple to pull stuff straight in using the XMLHTTP object, thus:

Function GetWebContent()
Set objXmlHttp = CreateObject("Msxml2.XMLHttp")
URL = "http://www.webname.com/data/wesensors.xml"
objXmlHttp.Open "GET", URL, False
objXmlHttp.Send
Dim myNewTextBox As Shape
Set myNewTextBox =
ThisDocument.ActiveView.ActivePage.Shapes.AddTextbox(Orientation:=pbTextOrientationHorizontal,
_
Left:=10, Top:=10, Width:=500, Height:=600)
myNewTextBox.TextFrame.TextRange.Text = objXmlHttp.responseText
End Function


As it stands this will fill a box with the raw html from a web page, which
probably isn't that useful. One could also use the web browser control to
translate html into plain text.

 

[sharon]

Hi Fred,

Many, many thanks for this answer.

This is what in my (poor) understanding I thought it shoud be...

Great! It's just what I was looking for.... It works a treat !

sharon

 

[sharon]

Hi there,

Finally I catch the point...

What VBA makes is create a static html data, rather than dynamic as I
needed, and which I think I'd shoud be. But this is another war...

Now I don't really understand what's the real purpose having VBA in Publisher?
Or maybe I miss somethink again...

sharon

 

[Ed Bennett]

Now I don't really understand what's the real purpose having VBA in Publisher?
Or maybe I miss somethink again...

The purpose of having VBA is the same as the purpose of VBA in any other
Office product: to automate repetitive tasks, and to allow Publisher's
functionality to be extended.

Since the start of this thread, I've had real trouble trying to
understand what you're trying to do, and I'm still not certain, but I
don't think it's a good idea!

It almost sounds like you're trying to create a web application using
Publisher and VBA. That is such a bad idea on so many levels...

I highly recommend you avoid using Publisher for creating websites of
any kind.

 

[sharon]

Hi Ed,

Late, I know, but I understood the message... ;-)

It was my fault, trying to use Publisher as Web designer soft... I just
needed an static page, but later on, I tried to do things Publisher is not
intended for.

sharon