I think I am doing something like what this is described as, but the get value part does not seem to work right.
' 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
I know nothing about that sort of stuff so I can't help you.
LarryMc
I couldn't even get it to compile on my system, it didn't like the lib/obj for some reason...
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
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
Tried doing:
OleApp.Cells(row,1).Value = row
did not work.
Just an FYI.
Thanks,
Bill