Use Linked Table Path To Open Source File

[Gdub]

I have a linked Excel table named ToolList in my Access
2000 database. Is there a VB string where I
may "piggyback" the Linked Manager pathway to open the
source Excel file? The Excel source locations can change
from user to user

 

[Graham Mandeno]

Hi Gdub

You can link an Excel spreadsheet "on the fly" with the TransferSpreadsheet
method:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, _
"ToolList", strExcelFile, True, strWorksheetName & "$"

You can use the common file dialog (see
http://www.mvps.org/access/api/api0001.htm) to allow the user to select the
file. If the name of the worksheet to be linked is variable, then it would
be elegant to provide the user with a combo box containing the available
worksheet names. The following code snippet will give you an idea of how to
do that:

========== start code ============
Private Sub txtExcelFile_AfterUpdate()
Dim oXL As Excel.Application
Dim oWkb As Excel.Workbook
Dim oSht As Excel.Worksheet
Dim sSheets As String
On Error GoTo ProcErr
cboWorkSheet.RowSource = ""
Set oXL = CreateObject("Excel.Application")
Set oWkb = oXL.Workbooks.Open(txtExcelFile, , True)
For Each oSht In oWkb.Worksheets
sSheets = sSheets & oSht.Name & ";"
Next
cboWorkSheet.RowSource = Left(sSheets, Len(sSheets) - 1)
ProcEnd:
On Error Resume Next
oWkb.Close False
oXL.Quit
Set oWkb = Nothing
Set oXL = Nothing
With cboWorkSheet
If .ListCount > 0 Then
.Value = .ItemData(0)
.SetFocus
If .ListCount > 1 Then .Dropdown
Else
.Value = Null
End If
End With
Exit Sub
ProcErr:
MsgBox Err.Description, vbOKOnly, "Error " & Err.Number
Resume ProcEnd

 

[Dirk Goldgar]

I have a linked Excel table named ToolList in my Access
2000 database. Is there a VB string where I
may "piggyback" the Linked Manager pathway to open the
source Excel file? The Excel source locations can change
from user to user

On the off chance that Graham has misunderstood what you're after, is it
that you want to extract from the linked table the path to the Excel
file to which is is linked? You can get that with the following code:

Dim strSourceFile As String

' First get the linked table's Connect string.
strSourceFile = CurrentDb.TableDefs("ToolList")

' Now parse out the path to the source file.
strSourceFile = _
Mid$(strSourceFile , InStr(strSourceFile , ";DATABASE=") + 10)

 

[Graham Mandeno]

Yes, you might be right Dirk... I didn't look at it that way

But, I don't think the Connect string is the default property for a
TableDef, so you need to be more specific:

strSourceFile = CurrentDb.TableDefs("ToolList").Connect

Also, there's a chance that there are more connection string parameters
after the ;DATABASE section, so it would be a good idea to check just in
case:

If InStr(strSourceFile, ";") > 0 Then
strSourceFile = Left(strSourceFile, InStr(strSourceFile, ";") - 1)
End If

 

[Dirk Goldgar]

Yes, you might be right Dirk... I didn't look at it that way

But, I don't think the Connect string is the default property for a
TableDef, so you need to be more specific:

strSourceFile = CurrentDb.TableDefs("ToolList").Connect

Quite right. That was a typo, or more accurately a "paste-o", on my
part -- I definitely had the .Connect property referenced in my test
code, and then managed to leave it out as I was copying and pasting.
Oops!

Also, there's a chance that there are more connection string
parameters after the ;DATABASE section, so it would be a good idea to
check just in case:

If InStr(strSourceFile, ";") > 0 Then
strSourceFile = Left(strSourceFile, InStr(strSourceFile, ";")
- 1) End If

It's possible. I did check with one linked Excel file, and the
;DATABASE section was at the end, but I agree that it would be safest to
check for that. Or one could first Split() the connect string on the
';' character, and then loop through the array of elements looking for
one that begins "DATABASE=".