UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Link Into A Specific Excel Sheet, Access 2016    
 
   
Javier
post Feb 18 2020, 03:10 PM
Post#1



Posts: 53
Joined: 13-June 06



How to link into a specific Excel Sheet ?


I want to link into a specific Excel Sheet by creating a linked table in Access

Path is = CurrentProject.Path
Excel names is = Test.xlsx
Sheet name is = Sheet1
Access Table is = tblLinkedExcel

I tried like this:

CurrentDb.TableDefs("tblLinkedExcel").Connect = CurrentProject.Path & "\Test.xlsx\Sheet1" & "$"
CurrentDb.TableDefs("tblLinkedExcel").RefreshLink
Access.Application.RefreshDatabaseWindow


Any help please ?

 

Go to the top of the page
 
theDBguy
post Feb 18 2020, 03:28 PM
Post#2


UA Moderator
Posts: 77,703
Joined: 19-June 07
From: SunnySandyEggo


Hi. Your topic title asks how to "link" to an Excel sheet, but your code is about "relinking" an existing table. Which one did you really want to do?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Feb 18 2020, 03:40 PM
Post#3


UtterAccess VIP
Posts: 10,321
Joined: 25-October 10
From: Gulf South USA


Hi: If you are trying to link (not re-link) the table, you might try something like this ...

DoCmd.TransferSpreadsheet acLink, , "tblLinkedExcel", CurrentProject.Path & "\Test.xlsx", True

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Javier
post Feb 19 2020, 05:25 AM
Post#4



Posts: 53
Joined: 13-June 06



Yes I want to LInk (Not to re-link) .....

But following code will Link to the first Excel Sheet........ right ?
CODE
DoCmd.TransferSpreadsheet acLink, , "tblLinkedExcel", CurrentProject.Path & "\Test.xlsx", True


But....... I would like to Link to a specific Excel Sheet (No the first one) is this possible ?

Go to the top of the page
 
RJD
post Feb 19 2020, 06:12 AM
Post#5


UtterAccess VIP
Posts: 10,321
Joined: 25-October 10
From: Gulf South USA


Hi: Try this ...

DoCmd.TransferSpreadsheet acLink, , "tblLinkedExcel", CurrentProject.Path & "\Test.xlsx", True, "Sheet2$"

...making the sheet name the one you want with $ after ... or you can choose a range, "Sheet2!a1:a3" for example, or use a named range in the sheet.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Javier
post Feb 19 2020, 07:49 AM
Post#6



Posts: 53
Joined: 13-June 06



Thank you RJD but "Sheet1!a1:a3" didn´t work, but you put me on the right path ....


' Works fine for default (first sheet) Excel Sheet
CODE
DoCmd.TransferSpreadsheet acLink, , "tblLinkedExcel", CurrentProject.Path & "\Test.xlsx", True



' Works fine for specific Excel Sheet
CODE
DoCmd.TransferSpreadsheet acLink, , "tblLinkedExcel", CurrentProject.Path & "\Test.xlsx", True, "Sheet1" & "$"



' Works fine for specific Excel Sheet and Range
CODE
DoCmd.TransferSpreadsheet acLink, , "tblLinkedExcel", CurrentProject.Path & "\Test.xlsx", True, "Sheet1" & "$" & "A1:D20"

This post has been edited by Javier: Feb 19 2020, 07:52 AM
Go to the top of the page
 
RJD
post Feb 19 2020, 07:57 AM
Post#7


UtterAccess VIP
Posts: 10,321
Joined: 25-October 10
From: Gulf South USA


Hmmm ... okay ... "Sheet1!a1:a3" worked for me in my tests, without a problem. But the main thing is that you now have a solution that works. thumbup.gif

Continued success with your project ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th March 2020 - 09:17 AM