Trouble With Linking To Excel

[ineedahelp]

Hi and HELP!,

I am using Excel and Access 2003. I am trying to create a link to an excel spreadsheet and append the data from a specific sheet called "PEJ". My error is runtime error 3125 "PEJ$" is not a valid name. When I perform these tasks manually it works...any help? Oh, I prefer to link because the spreadsheet data will change quite frequently and I will be performing this function on multiple sheets.

Dim db As Database
Dim tdfLinked As TableDef
Dim strSearchTable, strTableToFind, TempDate As String
Dim myPath As String, myFile As String
 
myPath = "c:\documents and settings\NAME\My Documents\NAME\Excel Analytics\ETFs\"
myFile = "DTC_032707_1"
strTableToFind = "tbl_LINK_" & Me!comboChooseETF
TempDate = Format(Date, "mmddyy")
Set db = CurrentDb
'strSearchTable = "SELECT COUNT(*) FROM MSYSObjects WHERE (((MSYSObjects.Name)='tbl_LINK_PEJ'));"
 
If TableExists("" & strTableToFind & "") Then
    CurrentDb.TableDefs.Delete ("" & strTableToFind & "")
End If
 
Set tdfLinked = db.CreateTableDef("" & strTableToFind & "")
tdfLinked.Connect = _
    "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & myPath & myFile & ".xls"
Debug.Print myPath & myFile & ".xls"
tdfLinked.SourceTableName = "PEJ$"
db.TableDefs.Append tdfLinked
DoCmd.RunSQL "SELECT " & strTableToFind & ".ETFSymbol, " & strTableToFind & ".CUSIP, " & strTableToFind & ".ETFName, " & strTableToFind & ".TotalQty, " & strTableToFind & ".LastPrice INTO " & Me!comboChooseETF & "_" & TempDate & _
                            " FROM " & strTableToFind
 
'    DoCmd.RunSQL "DELETE * FROM tbl_LINK_" & Me!comboChooseETF & ";"
db.Close
Set db = Nothing
Set tdfLinked = Nothing

[maxamis4]

I am not to familiar with the link part of excel. I usually just import my records. Try www.utteraccess.com, those guys are more into the module/coding part of different variations of code.

Hi and HELP!,

I am using Excel and Access 2003. I am trying to create a link to an excel spreadsheet and append the data from a specific sheet called "PEJ". My error is runtime error 3125 "PEJ$" is not a valid name. When I perform these tasks manually it works...any help? Oh, I prefer to link because the spreadsheet data will change quite frequently and I will be performing this function on multiple sheets.

Dim db As Database
Dim tdfLinked As TableDef
Dim strSearchTable, strTableToFind, TempDate As String
Dim myPath As String, myFile As String
 
myPath = "c:\documents and settings\NAME\My Documents\NAME\Excel Analytics\ETFs\"
myFile = "DTC_032707_1"
strTableToFind = "tbl_LINK_" & Me!comboChooseETF
TempDate = Format(Date, "mmddyy")
Set db = CurrentDb
'strSearchTable = "SELECT COUNT(*) FROM MSYSObjects WHERE (((MSYSObjects.Name)='tbl_LINK_PEJ'));"
 
If TableExists("" & strTableToFind & "") Then
    CurrentDb.TableDefs.Delete ("" & strTableToFind & "")
End If
 
Set tdfLinked = db.CreateTableDef("" & strTableToFind & "")
tdfLinked.Connect = _
    "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & myPath & myFile & ".xls"
Debug.Print myPath & myFile & ".xls"
tdfLinked.SourceTableName = "PEJ$"
db.TableDefs.Append tdfLinked
DoCmd.RunSQL "SELECT " & strTableToFind & ".ETFSymbol, " & strTableToFind & ".CUSIP, " & strTableToFind & ".ETFName, " & strTableToFind & ".TotalQty, " & strTableToFind & ".LastPrice INTO " & Me!comboChooseETF & "_" & TempDate & _
                            " FROM " & strTableToFind
 
'    DoCmd.RunSQL "DELETE * FROM tbl_LINK_" & Me!comboChooseETF & ";"
db.Close
Set db = Nothing
Set tdfLinked = Nothing

[Corster]

Hey, I've never tried any of this and I don't mean to patronise, but I would question the following:

strTableToFind = "tbl_LINK_" & Me!comboChooseETF

Could be that the item in the combo box is incorrect?

tdfLinked.SourceTableName = "PEJ$"

You said it could not find "PEJ$", try removing the "$" and including your file path and file extension:

tdfLinked.SourceTableName = myPath & Me!comboChooseETF.Text & ".xls"

But then again, I'm no expert... If this gets you anywhere though, let me know!

[ineedahelp]

No luck with these suggestions unfortunately...I will be grateful to anyone who can help me with the connecting and linking from Access 2003 to Excel 2003.
Thank you.