Import from WORD into a .dbf-file 4

[german12]

I have a very long list of purchased items in a Word document (Office 2013) in a tabular form or matrix as shown below.
4 columns: supplier, item, date of purchase, price

What is the easiest way to import this list into a VFP 9 file?

Thanks
Klaus


Peace worldwide - it starts here...

 

[Chris Miller]

Hm. copy& paste into Excel and then go from there into VFP?

There is _vfp.DataToClip, but you need _vfp.ClipToData, which doesn't exist.
But you could oerhaps also use _cliptext to get the data only.
Whether dircetly using ALINES() on _cliptext or by pasting into notepad and saving as tab delimited values file.




Chriss

 

[Mike Lewis]

Actually, I don't think you need to go through Excel. If you copy the table directly from Word, you get tab-delimited text. So you could do something like this:

STRTOFILE(_cliptext, "junk.txt")
CREATE CURSOR Purchases (Supplier (C(12), Item  C(15), Date D, Price I )
APPEND FROM junk.txt DELIMITED WITH TAB

Obviously you will adjust the field names, data types and widths to suit your needs.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Yes, that was also one of my thoughts, but you know I also showed how easy you get an array from an excel range.

Yes, you can use STRTOFILE() instead of manually pasting into notepad, but even this low tech editor notepad is capable to store UTF-8, while VFP only extracts ANSI text.
Not a big problem, as ANSI also includes umlauts and more, but just a thought to maybe consider. In the end UTF-8 will also be a VFP problem if you don't use controls able to display UTF-8, like thw webbrowser control as perhaps simplest example. So the advantage is just a very slim margin when you need UTF8 capability you also need something more than VFP native controls.



Chriss

 

[german12]

Thank you Mike and Chris.
The easiest way to solve the problem for me is with STRTOFILE (),
but I am also grateful for the reference to UTF-8, and eventual ones
related problems.

Since the WORD file should also be maintained, I only have one additional question about _cliptext:

If the VFP program is to run 100% automatically,
then it would be nice if copying the word file as well
into the memory without manual intervention.

The Word file is called "Invoices.doc"

Is that possible with a VFP command - that would be a command that occurs before STRTOFILE (_cliptext, "junk.txt") can be executed?

Klaus


Peace worldwide - it starts here...

 

[Chris Miller]

I would automate Word. If you only have one table object in it, it will be oWord.ActiveDocument.Tables[1], no matter where exactly.

Local loWord
loWord = CreateObject("Word.Application")
loWord.Documents.Load('...')
loWord.Activedocument.Tables[1].Range.Select()
loWord.Selection.Copy()

Chriss

PS: even shorter without a selection:

Local loWord
loWord = CreateObject("Word.Application")
loWord.Documents.Load('...')
loWord.Activedocument.Tables[1].Range.Copy()

 

[german12]

Hi Chriss,
thanks for your code - however I got the following OLE error code 0x 80020006 when I try

loWord.Documents.Load('RECHNUNGEN.doc') *This is my word-file in the same folder as the prg is.
See photo below.

What does that mean, or - what did I do wrong?

Thanks
Klaus

Peace worldwide - it starts here...

 

[Chris Miller]

Pass in the whole fie name. Word is not in VFPs default directory. It's not the same process and even if it would be, it's not VFP.

Chriss

 

[german12]

I passed in the whole file name:
loWord.Documents.Load("c:\entw\produkt\wordholen\rechnungen.doc")

but that leads to the same error.


Peace worldwide - it starts here...

 

[Chris Miller]

Okay, that's because the method is Open, not Load, sorry.

Chriss

 

[german12]

Hi Chriss,
The transfer of a WORD file to a VFP file now works fine here.
It would never have occurred to me that "Open" and "Load" are a difference when accessing a WORD file.
There is no difference between the two terms in German usage.
If someone says: "I have loaded or opened a program" then it has the same meaning here.

Where can I learn more about WORD/Excel automation?

Best regards
Klaus

Peace worldwide - it starts here...

 

[Chris Miller]

Here, for example:

https://www.hentzenwerke.com/catalog/autofox.htm

And Microsoft has the whole office object model reference online and in the help of VBA.

Chriss

 

[Mike Lewis]

Where can I learn more about WORD/Excel automation?

Klaus, I usually recommend Tamar's conference papers as a good place to start. Go here and look for titles relating to "Automation", "Office", "Word", "Excel", etc.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ggreen61]

If the file is a docx file type, then it is really a collection of XML files contained in zip file (the zip extension is renamed to docx). You could then directly access the xml file to retrieve the table entries. This might take a bit of work, but you could create an example file and then determine the segments that are needed to be extracted. You can use an xml parser or use STREXTRACT function to extract the segments. Then you are not dependent on having Word installed if some are using alternative office applications such as OpenOffice.

 

[Chris Miller]

Also, declaring a variable as a type has usually no big effect, a LOCAL var still initializes to .F. no matter if you define it as Strin, as Integer or whatever.

But when you declare a variable as Word.Application, for example, you get intellisense showing you the object PEMs. You still have to set the variable to CREATEOBJECT(OLEclassname), but VFP assumes you'll do:

So once you initialize loWord as you stated in the LOCAL declaration you an use what Intellisense offers:

There I could have seen my mistake.

Chriss