Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Trouble With Linking To Excel

Member
  
Join Date: Sep 2006
Posts: 98
#1: Apr 17 '07
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.

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim tdfLinked As TableDef
  3. Dim strSearchTable, strTableToFind, TempDate As String
  4. Dim myPath As String, myFile As String
  5.  
  6. myPath = "c:\documents and settings\NAME\My Documents\NAME\Excel Analytics\ETFs\"
  7. myFile = "DTC_032707_1"
  8. strTableToFind = "tbl_LINK_" & Me!comboChooseETF
  9. TempDate = Format(Date, "mmddyy")
  10. Set db = CurrentDb
  11. 'strSearchTable = "SELECT COUNT(*) FROM MSYSObjects WHERE (((MSYSObjects.Name)='tbl_LINK_PEJ'));"
  12.  
  13. If TableExists("" & strTableToFind & "") Then
  14.     CurrentDb.TableDefs.Delete ("" & strTableToFind & "")
  15. End If
  16.  
  17. Set tdfLinked = db.CreateTableDef("" & strTableToFind & "")
  18. tdfLinked.Connect = _
  19.     "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & myPath & myFile & ".xls"
  20. Debug.Print myPath & myFile & ".xls"
  21. tdfLinked.SourceTableName = "PEJ$"
  22. db.TableDefs.Append tdfLinked
  23. DoCmd.RunSQL "SELECT " & strTableToFind & ".ETFSymbol, " & strTableToFind & ".CUSIP, " & strTableToFind & ".ETFName, " & strTableToFind & ".TotalQty, " & strTableToFind & ".LastPrice INTO " & Me!comboChooseETF & "_" & TempDate & _
  24.                             " FROM " & strTableToFind
  25.  
  26. '    DoCmd.RunSQL "DELETE * FROM tbl_LINK_" & Me!comboChooseETF & ";"
  27. db.Close
  28. Set db = Nothing
  29. Set tdfLinked = Nothing

maxamis4's Avatar
Expert
  
Join Date: Jan 2007
Location: Northern VA
Posts: 217
#2: Apr 17 '07

re: Trouble With Linking To Excel

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.

Quote:

Originally Posted by 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.

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim tdfLinked As TableDef
  3. Dim strSearchTable, strTableToFind, TempDate As String
  4. Dim myPath As String, myFile As String
  5.  
  6. myPath = "c:\documents and settings\NAME\My Documents\NAME\Excel Analytics\ETFs\"
  7. myFile = "DTC_032707_1"
  8. strTableToFind = "tbl_LINK_" & Me!comboChooseETF
  9. TempDate = Format(Date, "mmddyy")
  10. Set db = CurrentDb
  11. 'strSearchTable = "SELECT COUNT(*) FROM MSYSObjects WHERE (((MSYSObjects.Name)='tbl_LINK_PEJ'));"
  12.  
  13. If TableExists("" & strTableToFind & "") Then
  14.     CurrentDb.TableDefs.Delete ("" & strTableToFind & "")
  15. End If
  16.  
  17. Set tdfLinked = db.CreateTableDef("" & strTableToFind & "")
  18. tdfLinked.Connect = _
  19.     "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & myPath & myFile & ".xls"
  20. Debug.Print myPath & myFile & ".xls"
  21. tdfLinked.SourceTableName = "PEJ$"
  22. db.TableDefs.Append tdfLinked
  23. DoCmd.RunSQL "SELECT " & strTableToFind & ".ETFSymbol, " & strTableToFind & ".CUSIP, " & strTableToFind & ".ETFName, " & strTableToFind & ".TotalQty, " & strTableToFind & ".LastPrice INTO " & Me!comboChooseETF & "_" & TempDate & _
  24.                             " FROM " & strTableToFind
  25.  
  26. '    DoCmd.RunSQL "DELETE * FROM tbl_LINK_" & Me!comboChooseETF & ";"
  27. db.Close
  28. Set db = Nothing
  29. Set tdfLinked = Nothing
Corster's Avatar
Member
  
Join Date: Mar 2007
Location: Devon
Posts: 36
#3: Apr 17 '07

re: Trouble With Linking To Excel

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

Quote:
strTableToFind = "tbl_LINK_" & Me!comboChooseETF
Could be that the item in the combo box is incorrect?

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

Expand|Select|Wrap|Line Numbers
  1. tdfLinked.SourceTableName = myPath & Me!comboChooseETF.Text & ".xls"
But then again, I'm no expert... If this gets you anywhere though, let me know!
Member
  
Join Date: Sep 2006
Posts: 98
#4: Apr 17 '07

re: Trouble With Linking To Excel

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.
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,531 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved