I have the following lines of code:
This line deletes the link to an excel workbook. I presumed that any
vestige
of it's tabledef.connect string would be gone as well.
DropTable lclName
This line recreates the same local name but links it (successfully: I
checked
the data) to a workbook with a different name and in a different
directory:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, lclName, fName,
True, rngName
but, at this point, in trying to edit the connect string to IMEX 1, the
connect string returns the old workbook name and path and the function
craps
out (3044, Not a valid path):
SetIMEX2 1, lclName
Here's the function:
Function SetIMEX2(nVal As Integer, strTbl As String)
'Changing the IMEX value from 2 to 1 will allow clean records from a
linked
Excel file,
'provided the registry key (Hkey_Local_Machine/Software/Microsoft/Jet/4.
0/Engines/Excel/)
'TypeGuessRows is set to 0.
Dim tdf As TableDef
Set tdf = DBEngine(0)(0).TableDefs(strTbl)
tdf.Connect = Replace(tdf.Connect, "IMEX=2", "IMEX=" & nVal)
-------------here's where it craps out
tdf.RefreshLink
End Function
So I tried to edit the connect string with the following line, but it has
no
effect. The tdf.connect string still reflects the path and filename of the
dropped table.
dbLocal.TableDefs(lclName).Connect = "Excel 8.0;HDR=YES;IMEX=1;DATABASE="
&
strXLDir & .Fields("WkbkName")
How is this even possible? And how do I get around it?
--
Message posted via http://www.accessmonster.com
|
