G
Guest
Has anyone ever seen this error while programmatically trying to import a
spreadsheet?
I know the spreadsheet itself is readable and nobody ever gets into it but
me. I made sure that no pid's of Excel were opened... but I still get this
error every once in a blue moon when running some code that essentially does
this:
....
docmd.DeleteObject acTable, "ExcelTest"
currentdb.TableDefs.Refresh
Sleep 5
Docmd.TransferSpreadSheet acImport, xlBlahFormat, "Blahblah.xls", "ExcelTest"
' ^^^ The above has the code running right now so I can't see all the
parameters to tell you, but it's the standard TransferSpreadSheet command
....
public function Sleep(byval Interval as double)
dim sdate, edate as date
sdate = now
edate = dateadd("s",interval,sdate)
while datediff(edate,now) > 0
doevents
wend
end function
....
As you can see, the function deletes the object, refreshes the tables, waits
5 seconds while allowing the system to poll for events, and then tries to
transfer in the spreadsheet. But this problem STILL happens sometimes (it
just happened just now, hence my frantic post).
The reason that I delete the table first is b/c when I don't, for whatever
reason I get primary key violations a lot of the time even after doing a
"delete * from ExcelTest". The reason that I import instead of link to the
Excel file is that the Excel file has some unformatted data that I have to
clean up (i.e. "000-35" as -35), and then change the file types on the fly to
numeric or dates.
spreadsheet?
I know the spreadsheet itself is readable and nobody ever gets into it but
me. I made sure that no pid's of Excel were opened... but I still get this
error every once in a blue moon when running some code that essentially does
this:
....
docmd.DeleteObject acTable, "ExcelTest"
currentdb.TableDefs.Refresh
Sleep 5
Docmd.TransferSpreadSheet acImport, xlBlahFormat, "Blahblah.xls", "ExcelTest"
' ^^^ The above has the code running right now so I can't see all the
parameters to tell you, but it's the standard TransferSpreadSheet command
....
public function Sleep(byval Interval as double)
dim sdate, edate as date
sdate = now
edate = dateadd("s",interval,sdate)
while datediff(edate,now) > 0
doevents
wend
end function
....
As you can see, the function deletes the object, refreshes the tables, waits
5 seconds while allowing the system to poll for events, and then tries to
transfer in the spreadsheet. But this problem STILL happens sometimes (it
just happened just now, hence my frantic post).
The reason that I delete the table first is b/c when I don't, for whatever
reason I get primary key violations a lot of the time even after doing a
"delete * from ExcelTest". The reason that I import instead of link to the
Excel file is that the Excel file has some unformatted data that I have to
clean up (i.e. "000-35" as -35), and then change the file types on the fly to
numeric or dates.