Dishelper Question

[billhsln]

I think I am doing something like what this is described as, but the get value part does not seem to work right.

Code Select Expand

' This program will take the PSG6090 spread sheets and strip Actual
' and convert it to a comma delimited file and create file PSV0190

' PSG6090_to_PSV0190-XLS.EBA

$MAIN
STRING Version$
Version$="1.2"

AUTODEFINE "off"

$include "shlwapi.inc"

DECLARE IMPORT,SHGetSpecialFolderLocation(hwnd as UINT,nFolder as INT,ppITEMIDLIST as POINTER),int
DECLARE IMPORT,SHGetPathFromIDList(pITEMIDLIST as POINTER,PATH as STRING),int

IDispatch oleApp

FILE OfileIE
STRING FileNameO, path, filename, rtn, flds[30]
STRING company, cmpny, t, q, c, qcq, cq, qc
STRING recout
INT trecs, wrecs, row, dir, attrib, result, value

HRESULT iHresult = E_FAIL

t = "\t"

c = ","
q = "\""

qc = q + c
qcq = q + c + q
cq = c + q

trecs = 0
wrecs = 0

OPENCONSOLE

path = GetFolderLocation(5) + "\\CSB\\Bakeshop\\Jerry's Actual Report\\"
FileNameO = path + "PSV0190U-XLS.CSV"
IF (OPENFILE(OfileIE,FileNameO,"W") <> 0)
PRINT "File: " + FileNameO + " Not able to Open Output"
GOTO eoj
ENDIF

dir = FINDOPEN(path + "*.xls")
IF (dir)
DO
filename = FINDNEXT(dir,attrib)
IF filename = ""
' Last Record
rtn = ""
ELSE
IF attrib & @FILE_DIRECTORY
'this is a directory
rtn = ""
ELSE
company = UCASE$(MID$(filename,9,3))
SELECT company
CASE "7TH"
cmpny = "1"
CASE "I45"
cmpny = "2"
CASE "I35"
cmpny = "3"
CASE "I20"
cmpny = "4"
DEFAULT
cmpny = "X"
ENDSELECT

PRINT "OPEN ", filename, " = ", cmpny

oleApp = CreateComObject("Excel.Application")
result = oleApp.Workbooks.Open(filename)
' PRINT result
result = OleApp.Worksheets("Reports")
' PRINT result
'===================
' Read the database.
'===================
FOR row = 7 TO 300
flds[0] = OleApp.Cells(row,1).Value
flds[1] = OleApp.Cells(row,2).Value
flds[2] = OleApp.Cells(row,13).Value
PRINT flds[0], " ", flds[1], " ", flds[2]
IF flds[0] = "ZZZZ"
row = 301
ELSE
recout = cmpny + cq + flds[1] + qc + flds[2]
wrecs++
WRITE OfileIE, recout
ENDIF
NEXT row
'====================
' Close the database.
'====================
oleApp->Release()
ENDIF
ENDIF
UNTIL filename = ""
FINDCLOSE dir
ENDIF
PRINT "Written Recs = ", USING("###,###",wrecs)
LABEL eoj
CLOSECONSOLE
END

SUB GetFolderLocation(nFolder as INT),STRING
DEF path[260]:ISTRING
DEF pidl:POINTER
DEF ppidl:POINTER
ppidl = &pidl
path = ""
SHGetSpecialFolderLocation(NULL,nFolder,ppidl)
SHGetPathFromIDList(pidl,path)
CoTaskMemFree(pidl)
RETURN path
ENDSUB

It only returns 'Microsoft Excel' in all 3 fields (flds[0] - flds[2]), lines 85 thru 88.

Not sure what I am doing wrong, but should come back with the first 2 fields being text and the value in column 13 should be number, but might be text.

Thanks,
Bill

[LarryMc]

I know nothing about that sort of stuff so I can't help you.

LarryMc

[ZeroDog]

I couldn't even get it to compile on my system, it didn't like the lib/obj for some reason...

[LarryMc]

Are you trying to do something you can't do with what you were doing here:

http://www.ionicwind.com/forums/index.php?topic=4125.msg32275#msg32275

or are you just trying to see if you can do it better with comobjects?

LarryMc

[billhsln]

I could not get it to work correctly 100% of the time the other way.  So, I found a program written in Foxpro, that does it this way.  Was just hoping I could convert it into IWB.  Most seem to work, but can't seem to read the data.  Will give writing a try next, after I backup the data.

Bill

[billhsln]

Tried doing:

OleApp.Cells(row,1).Value = row

did not work.

Just an FYI.

Thanks,
Bill