Lotus workbook values

[greywalk]

I need to pull the values from a lotus spreadsheet. For instance I need the values from:

A:C4
A12
A:C25

I would like to do this from within VFP. The name of the workbook file will change but the location of the information in the cells will not change. I would just import but there is mucho text in the spreadsheet.

Any suggestions? Dancing is easy. Now dancing on a floating raft in the middle of an October storm in the middle of the North Atlantic, that is hard.

 

[german12]

Hi greywalk,

I know this is not what you want exactly, however it could help:

Within Lotus record a macro which copies and saves only the information of the cells
A:C4 A12 A:C25
into a separate Lotus-file which i call mynewfile.123 here.
The button created with the code included however must always be transferred to your next Lotus-file, because you
said it would have another name every time.

But all you have to do then is in VFP to call the contents of mynewfile.123 by this:

Import from mynewfile type wk... (See import-command of vfp)

This is a quick shot sorry- next days I will try to find a better solution - but do not know, whether I would have
success.....your question is very interesting for me too.

regards from germany

Klaus

 

[baltman]

Can't you import of the Lotus Worksheet into a temporary file using the "import" command and then scan through the file and pick out what you want?

I'm looking at VFP 6 and there are options to import WK1, WK3 and WKS files.

I don't have Lotus so I can't try anything to see if it'd work, but I've done similar things with Excel.

I can provide a sample prg that'd work for Excel if you'd like...

Brian

 

[german12]

Hi Brian,

if you would like to send me your sample I could also transfer it into and test it with Lotus
(I am really interested.)

My adress:
KBriesemeister@t-online.de

Thanks
Klaus

 

[greywalk]

Klaus thank you for your suggestion. I will have to look at the possibility of the macro. The problem is the workbook will come from an external source which the company has no control over. However there is a possibility in your answer, a macro that can readily be transferred from one sheet to the next.

Brian, thank you but I tried your response initially. The problem lies with mixed values. The import routine only handles one type of value per column. In the workbook the value of a column may alternate between text, dates, and numeric values. Thus if the date is down the column from the text it does not readily transfer over. There were other problems using your methodolgy as well. But thanks anyways.

Klaus if I can help test or provide more information for you let me know. I am still working on this.
Dancing is easy. Now dancing on a floating raft in the middle of an October storm in the middle of the North Atlantic, that is hard.

 

[baltman]

It's pretty simple... but (at least in VFP 5) I'm limited Excel 5.0 or earlier... anyway:

IMPORT FROM c:\data\test\TestFile.xls;
TYPE XL5 SHEET "SheetName"

&&Now I have an imported file with field names of A, B etc.
&&If you know where you want to get your data from:

go 5 && go to row 5
VarGetData=c && now whatever was in column "C" of row "5" is not this variable

&&If you want to use this as a number somewhere use:
val(VarGetData)

I assume you get the idea. You can loop stuff pretty easily too... a good trick is that "chr(65)" is an "A" and in your loop you can add a counter to it to go to "chr(65+x)" i.e. "B" etc. where x is in a "for x=1 to 25" loop.

Brian

 

[german12]

*!*!* Lotustest.prg

*!**!* Try the following:

*!** at first save your lotus-file as a comma-delimited file (CSV)
*!** which is in my german-edition the first possibility in saving.
*!** In my program below I called it testfile.csv

*!**!* 1.CREATE a VFP-TABLE WITH HAS many COLUMNS AS your SHEET IN Lotus
*!**!* has (ONLY the COLUMNS which are filled IN Lotus - NOT FROM a1
*!**!* TO IV255!!! - normally no worksheet IS soo big....

*!* 2. Give that VFP-sheet field-names like field1, field2.....etc.
*!* and give them the type CHARACTER

*!* 3. Try the following program in VFP

*!* 4. Look at the browse-Window - perhaps you can do it with that
*!* new create VFP-file - although all contents are of character.
*!*
*!* 5. If it does not help you - send me a sample of your lotus
*!* file (no matter what is in - you can change the numbers as
*!* you like to stay confidential...) - I can test better then...


*!* Regards Klaus

*!* My adress: KBriesemeister@t-online.de

*VFP-Programm begins:

USE mydbf && that is your vfp-file to catch the data from Lotus
ZAP && nothing should be in at the beginning
GO TOP
APPEND FROM testfile.csv DELIMITED &&that's your lotus-file
BROWSE

*eof-program


Hope that helps

Regards from Germany

Klaus

 

[baltman]

Here's a complete Excel to DBF prg I wrote which can switch between method "1" and "2"...

"1" Uses COM and "2" uses a "save as" CSV method. Both make some basic assumptions about the target data that you might have to tweak. I do assume that everything is text to start out so that data isn't dropped.

The prg goes on to assume that there is only one data type (see the error coding used in the COM method to see how to deal with mixed data types).

I don't know if Lotus is a COM server or not so I don't know if this will be adapable...

CLOSE DATA ALL
SET SAFE OFF
SET TALK OFF
timer1=time()
&&start form variables
methodtouse=2 && 1=via com which is robust but slow or 2 which is saving as a comma delimfile which is fast
excelfile="c:\data.xls"
sheetnumber=1 && val from form default=1
startrow=2 && val from form default=2
headrow=startrow-1 && val from form
headersexists=1
linesofdata=3 && val from form, if null assume 100 (number to include headers)
firstcolumn=1 && val from form, if null assume A (1)
firstcolumnletter="A"
lastcolumn=10 && val from form, if null assume Z (26)
lastcolumnletter="O"
mapusing=1 &&val from form (1=headers in row before start row; 2= map by column order)
&&end form variables

&&Start Excel Import
#DEFINE False .F.
#DEFINE True .T.

*-- First, Create a Layout fro the data
for x=firstcolumn to lastcolumn
if x=firstcolumn
maketable="create table xlimport ("
endif

maketable=maketable+"col"+allt(str(x))+" c(32)"

if x<lastcolumn
maketable=maketable+&quot;,&quot;
endif

if x=lastcolumn
maketable=maketable+&quot&quot;
endif
endfor
&maketable

if methodtouse=1
for counter=headrow to headrow+linesofdata
append blank
endfor
endif

WAIT WINDOW NOWAIT &quot;Opening Excel Connection...&quot;

LOCAL loExcel, lcOldError, lcRange, lnSheets, lnCounter

lcOldError = ON(&quot;ERROR&quot
ON ERROR loExcel = .NULL.
loExcel = GetObject(, &quot;Excel.Application&quot
ON ERROR &lcOldError

IF ISNULL(loExcel)
loExcel = CreateObject( &quot;Excel.Application&quot; )
ENDIF

if methodtouse=1
loExcel.Workbooks.Open(excelfile) && excelfile is form variable
*loexcel.visible=.t.
WAIT WINDOW NOWAIT &quot;Starting Excel Import...&quot;

if methodtouse=1
*-- Now, scan through the spreadsheet and put all
*-- the information into the table

lnRow = headrow
go top
scan
WAIT WINDOW NOWAIT &quot;Populating cells: Record &quot; + ALLTRIM(STR(RECNO()))+&quot; of &quot; + ALLTRIM(STR(linesofdata))
*-- Write the record into the table

for x=firstcolumn to lastcolumn
strcmd=&quot;repl col&quot;+allt(str(x))+&quot; with allt(loExcel.Activesheet.Cells(lnRow,&quot;+allt(str(x))+&quot.Value)&quot;
numcmd=&quot;repl col&quot;+allt(str(x))+&quot; with allt(str(loExcel.Activesheet.Cells(lnRow,&quot;+allt(str(x))+&quot.Value,20,5))&quot;
datecmd=&quot;repl col&quot;+allt(str(x))+&quot; with chrtran(allt(str(year(loExcel.Activesheet.Cells(lnRow,8).Value)))+(str(month(loExcel.Activesheet.Cells(lnRow,8).Value),2))+(str(day(loExcel.Activesheet.Cells(lnRow,8).Value),2)),' ','0')&quot;

try=1 && assumes numbers work most often

if try=1
on error try=try+1
&numcmd
endif

if try=2 && assumes character is second most often
on error try=try+1
&strcmd
endif

if try=3 && assumes character is second most often
on error
&datecmd
endif

endfor
lnRow = lnRow + 1
endscan

Release loExcel
endif

set talk on
timer2=time()

?&quot;this run took&quot;
?timer1
?timer2
endif

if methodtouse=2
on error WAIT WINDOW NOWAIT &quot;file in use...&quot;
on error
wait window &quot;Formatting and exporting data....&quot; nowait
copy file &excelfile to c:\temp\temp4import.xls
excelfile=&quot;c:\temp\temp4import.xls&quot;
loExcel.Workbooks.Open(excelfile) && excelfile is form variable
loExcel.DisplayAlerts = False
*loExcel.Visible = .T.
XLRange=firstcolumnletter+alltr(str(headrow))+&quot;:&quot;+lastcolumnletter+alltr(str(linesofdata))
loExcel.Activesheet.Range(xlrange).NumberFormat=&quot;0.00000&quot;
loExcel.Activesheet.Range(xlrange).Replace(Chr(34), &quot;&quot
loExcel.Activesheet.Range(xlrange).Replace(&quot;,&quot;, &quot;&quot
loExcel.Activesheet.Range(xlrange).Replace(Chr(39), &quot;&quot
loExcel.Activesheet.Cells.Select
loExcel.Activesheet.Range(xlrange).ColumnWidth = 200

erase c:\temp\bobscsv.csv

loExcel.ActiveSheet.SaveAs(&quot;c:\temp\bobscsv.csv&quot;,6) &&6=XLcsv

loExcel.Workbooks.Close()
Release loExcel

use xlimport
append from c:\temp\bobscsv.csv type delim
delete for recno()>linesofdata
pack
endif

if headersexists=1
create table headers (fieldnum n(3), headername c(20),mapped c(3))

for tmpx=firstcolumn to lastcolumn
select xlimport
go 1
tmpvar=&quot;col&quot;+alltr(str(tmpx))
tmpans=&tmpvar
select headers
append blank
repl headername with alltrim(tmpans)
endfor
repl all fieldnum with recno()
endif

WAIT WINDOW NOWAIT &quot;Finished&quot; timeout 1