M
MuSuLPhReAk
I am able to make my connection to the excel file and
enter the first sheet into the table with no problem. It
goes to the second excel sheet then the system locks up on
record 64560 which is about record 4345 on the second
sheet. Am I missing something?
This is the code I'm running:
PRIVATE SUB COMMAND0_CLICK()
DIM OBJEXCEL AS ADODB.CONNECTION
DIM STRSQL AS STRING
DIM OBJAPP AS EXCEL.APPLICATION
DIM OBJWB AS EXCEL.WORKBOOK
DIM OBJWS AS EXCEL.WORKSHEET
DIM RST AS ADODB.RECORDSET
DIM INTCOUNT AS INTEGER, INTROWS AS LONG
DIM STRTABLENAME AS STRING
DIM CNN2 AS NEW ADODB.CONNECTION
DIM RST2 AS ADODB.RECORDSET
SET CNN2 = CURRENTPROJECT.CONNECTION
SET RST2 = NEW ADODB.RECORDSET
RST2.CURSORTYPE = ADOPENSTATIC
RST2.LOCKTYPE = ADLOCKOPTIMISTIC
RST2.OPEN \"TBLLOGS\", CNN2
SET OBJAPP = NEW EXCEL.APPLICATION
SET RST = NEW ADODB.RECORDSET
WITH OBJAPP
DISPLAYALERTS = FALSE
VISIBLE = FALSE
END WITH
STRPATH = \"C:\WINDOWS\DESKTOP\TELEPHONE LOGS
PROJECT\TELEPHONE LOG.XLS\"
SET OBJWB = OBJAPP.WORKBOOKS.OPEN(STRPATH)
SET OBJWS = OBJWB.WORKSHEETS(1)
STRTABLENAME = OBJWS.NAME
SET OBJEXCEL = NEW ADODB.CONNECTION
OBJEXCEL.CONNECTIONSTRING
= \"PROVIDER=MICROSOFT.JET.OLEDB.4.0;\" & _
\"DATA SOURCE=\" & STRPATH & \";\" &
_
\"EXTENDED PROPERTIES=EXCEL 8.0;\"
STRSQL = \"SELECT * FROM [\" & STRTABLENAME & \"$]\"
RST.OPEN STRSQL, OBJEXCEL.CONNECTIONSTRING, ADOPENSTATIC,
ADLOCKOPTIMISTIC, -1
INTROWS = RST.RECORDCOUNT
INTSHEETS = OBJWB.SHEETS.COUNT
POS = 1
X = 1 'USED TO DISPLAY RECORD
NUMBER CURRENTLY WRITING ON FORM
WHILE (POS <= INTSHEETS)
DO WHILE NOT RST.EOF
RST2.ADDNEW
RST2(1) = RST(0) 'RST2(0) IS AN AUTONUMBER FIELD
SO DON’T SET IT
RST2(2) = RST(1)
RST2(3) = RST(2)
RST2(4) = RST(3)
RST2(5) = RST(4)
RST2(6) = RST(5)
RST2(7) = RST(6)
RST2(8) = RST(7)
RST2.UPDATE
RST.MOVENEXT
ME.TXTRECORD = X
ME.REPAINT
X = X + 1
LOOP
POS = POS + 1
RST.CLOSE
SET RST = NOTHING
SET OBJWS = OBJWB.WORKSHEETS(POS)
STRTABLENAME = OBJWS.NAME
STRSQL = \"SELECT * FROM [\" & STRTABLENAME & \"$]\"
SET RST = NEW ADODB.RECORDSET
RST.OPEN STRSQL, OBJEXCEL.CONNECTIONSTRING,
ADOPENSTATIC, ADLOCKOPTIMISTIC, -1
WEND
SET OBJWS = NOTHING
SET OBJWB = NOTHING
OBJAPP.QUIT
SET OBJAPP = NOTHING
RST.CLOSE
SET RST = NOTHING
'DELETE ROWS USED FOR TOTALS - FOUND IT FASTER THAN TO CHECK EACH
'RECORD BEFORE WRITING
DOCMD.RUNSQL \"DELETE TBLLOGS.DATE FROM TBLLOGS WHERE
TBLLOGS.DATE IS NULL;\"
END SUB
Another thing was that when I only tested it on one page,
it worked fine. After the code finished, if I pressed
alt+crtl+del, I would still see excel in the processes
running.
Any help on this would be GREATLY appreciated.
enter the first sheet into the table with no problem. It
goes to the second excel sheet then the system locks up on
record 64560 which is about record 4345 on the second
sheet. Am I missing something?
This is the code I'm running:
PRIVATE SUB COMMAND0_CLICK()
DIM OBJEXCEL AS ADODB.CONNECTION
DIM STRSQL AS STRING
DIM OBJAPP AS EXCEL.APPLICATION
DIM OBJWB AS EXCEL.WORKBOOK
DIM OBJWS AS EXCEL.WORKSHEET
DIM RST AS ADODB.RECORDSET
DIM INTCOUNT AS INTEGER, INTROWS AS LONG
DIM STRTABLENAME AS STRING
DIM CNN2 AS NEW ADODB.CONNECTION
DIM RST2 AS ADODB.RECORDSET
SET CNN2 = CURRENTPROJECT.CONNECTION
SET RST2 = NEW ADODB.RECORDSET
RST2.CURSORTYPE = ADOPENSTATIC
RST2.LOCKTYPE = ADLOCKOPTIMISTIC
RST2.OPEN \"TBLLOGS\", CNN2
SET OBJAPP = NEW EXCEL.APPLICATION
SET RST = NEW ADODB.RECORDSET
WITH OBJAPP
DISPLAYALERTS = FALSE
VISIBLE = FALSE
END WITH
STRPATH = \"C:\WINDOWS\DESKTOP\TELEPHONE LOGS
PROJECT\TELEPHONE LOG.XLS\"
SET OBJWB = OBJAPP.WORKBOOKS.OPEN(STRPATH)
SET OBJWS = OBJWB.WORKSHEETS(1)
STRTABLENAME = OBJWS.NAME
SET OBJEXCEL = NEW ADODB.CONNECTION
OBJEXCEL.CONNECTIONSTRING
= \"PROVIDER=MICROSOFT.JET.OLEDB.4.0;\" & _
\"DATA SOURCE=\" & STRPATH & \";\" &
_
\"EXTENDED PROPERTIES=EXCEL 8.0;\"
STRSQL = \"SELECT * FROM [\" & STRTABLENAME & \"$]\"
RST.OPEN STRSQL, OBJEXCEL.CONNECTIONSTRING, ADOPENSTATIC,
ADLOCKOPTIMISTIC, -1
INTROWS = RST.RECORDCOUNT
INTSHEETS = OBJWB.SHEETS.COUNT
POS = 1
X = 1 'USED TO DISPLAY RECORD
NUMBER CURRENTLY WRITING ON FORM
WHILE (POS <= INTSHEETS)
DO WHILE NOT RST.EOF
RST2.ADDNEW
RST2(1) = RST(0) 'RST2(0) IS AN AUTONUMBER FIELD
SO DON’T SET IT
RST2(2) = RST(1)
RST2(3) = RST(2)
RST2(4) = RST(3)
RST2(5) = RST(4)
RST2(6) = RST(5)
RST2(7) = RST(6)
RST2(8) = RST(7)
RST2.UPDATE
RST.MOVENEXT
ME.TXTRECORD = X
ME.REPAINT
X = X + 1
LOOP
POS = POS + 1
RST.CLOSE
SET RST = NOTHING
SET OBJWS = OBJWB.WORKSHEETS(POS)
STRTABLENAME = OBJWS.NAME
STRSQL = \"SELECT * FROM [\" & STRTABLENAME & \"$]\"
SET RST = NEW ADODB.RECORDSET
RST.OPEN STRSQL, OBJEXCEL.CONNECTIONSTRING,
ADOPENSTATIC, ADLOCKOPTIMISTIC, -1
WEND
SET OBJWS = NOTHING
SET OBJWB = NOTHING
OBJAPP.QUIT
SET OBJAPP = NOTHING
RST.CLOSE
SET RST = NOTHING
'DELETE ROWS USED FOR TOTALS - FOUND IT FASTER THAN TO CHECK EACH
'RECORD BEFORE WRITING
DOCMD.RUNSQL \"DELETE TBLLOGS.DATE FROM TBLLOGS WHERE
TBLLOGS.DATE IS NULL;\"
END SUB
Another thing was that when I only tested it on one page,
it worked fine. After the code finished, if I pressed
alt+crtl+del, I would still see excel in the processes
running.
Any help on this would be GREATLY appreciated.