I want to link my Acces Database to Excel (1 Viewer)

[Arcadia1]

I want to link my Acces database to Excel through VBA so that it will be done automatically..

So if something is edited it will be seen in excel directly without doing anything.

Can someone please help me :banghead:

 

[Uncle Gizmo]

DON'T DO MULTIPLE POSTS

The problem with Multiple posts is that it wastes a lot of people's time.

You litter the same post all over the forum under different headings, then someone kindly provides an answer. Now someone else answers the same question again, under a different heading. Not a problem for you, you have gamed the system and probably got your answer quicker than you would have normally.

However we all have limited time here which we provide for free no one gets paid as far as I know and if you discover that you have answered a question that has already been answered by someone else, then besides it being annoying, you may well have wasted half an hour and sometimes even an hour answering a question unnecessarily when you could have been answering someone else's question.

So multiple posting is very serious and it is necessary to encourage people to follow the rules of the forum. After all the rules are for the benefit of everyone.

 

[Arcadia1]

DON'T DO MULTIPLE POSTS

The problem with Multiple posts is that it wastes a lot of people's time.

You litter the same post all over the forum under different headings, then someone kindly provides an answer. Now someone else answers the same question again, under a different heading. Not a problem for you, you have gamed the system and probably got your answer quicker than you would have normally.

However we all have limited time here which we provide for free no one gets paid as far as I know and if you discover that you have answered a question that has already been answered by someone else, then besides it being annoying, you may well have wasted half an hour and sometimes even an hour answering a question unnecessarily when you could have been answering someone else's question.

So multiple posting is very serious and it is necessary to encourage people to follow the rules of the forum. After all the rules are for the benefit of everyone.

I only posted it twice, but if that is a problem you can let this one and delete that other post.


But can you help me with this issue?

 

[Arcadia1]

Someone help me pleaseee

 

[JHB]

The below code should do what you want, paste it into a module.

Dim dbs As DAO.Database, tbl As TableDef
Dim stSource As String, stConnect As String

Set dbs = CurrentDb()
stSource = "TheNameOfyourSheet$"
stConnect = "Excel 8.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=ThePathToYourExcelFileAndTheExcelFileName"
'Like "C:\Access programmer\Client.xls"
Set tbl = dbs.CreateTableDef("mySheet")
tbl.Connect = stConnect
tbl.SourceTableName = stSource

dbs.TableDefs.Append tbl

Set tbl = Nothing
Set dbs = Nothing
End Sub

 

[Arcadia1]

The below code should do what you want, paste it into a module.

Dim dbs As DAO.Database, tbl As TableDef
Dim stSource As String, stConnect As String
 
Set dbs = CurrentDb()
stSource = "TheNameOfyourSheet$"
stConnect = "Excel 8.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=ThePathToYourExcelFileAndTheExcelFileName"
'Like "C:\Access programmer\Client.xls"
Set tbl = dbs.CreateTableDef("mySheet")
tbl.Connect = stConnect
tbl.SourceTableName = stSource
 
dbs.TableDefs.Append tbl
 
Set tbl = Nothing
Set dbs = Nothing
End Sub

Thanks!

But when i put the code in a module like this:

Sub testen()
Dim dbs As DAO.Database, tbl As TableDef
Dim stSource As String, stConnect As String
Set dbs = CurrentDb()
stSource = "Blad1"
stConnect = "Excel 8.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\Lamyae\Desktop\Access thuis\test1.xlsx"
'Like "C:\Access programmer\Client.xls"
Set tbl = dbs.CreateTableDef("mySheet")
tbl.Connect = stConnect
tbl.SourceTableName = stSource
dbs.TableDefs.Append tbl
Set tbl = Nothing
Set dbs = Nothing
End Sub

When i want to run the code it tells me something is wrong with the following line:

dbs.TableDefs.Append tbl


Maybe there is something that i did wrong, but i can't find it please help me.

 

[JHB]

I think you are missing the "$" in the sheet name.

stSource = "Blad1[COLOR=Red][B]$[/B][/COLOR]"

Else show the error code and message you get because the below is to weak a description:

When i want to run the code it tells me something is wrong with the following line:

dbs.TableDefs.Append tbl