ThrottleWorks
Excel Ninja
Hi,
I am using below code to import MS Access database in excel.
This code is written in Excel file. I have only one table in Access database.
Is it possible to give cell reference instead of full path in the code.
I am trying to change below line but not able to do so.
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\AA\AA\AA\AA\AA.accdb;Mode=ReadWrite", Destination:=Worksheets("DataBase").Range("A1")).QueryTable
I am trying to replace ‘=\\AA\AA\AA\AA\AA.accdb’ with ‘MyDataSource’.
Can anyone please help me in this.
I am using below code to import MS Access database in excel.
This code is written in Excel file. I have only one table in Access database.
Is it possible to give cell reference instead of full path in the code.
I am trying to change below line but not able to do so.
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\AA\AA\AA\AA\AA.accdb;Mode=ReadWrite", Destination:=Worksheets("DataBase").Range("A1")).QueryTable
I am trying to replace ‘=\\AA\AA\AA\AA\AA.accdb’ with ‘MyDataSource’.
Can anyone please help me in this.
Code:
Sub OPENCONNECTION()
Dim MyDataSource As String
MyDataSource = Worksheets("Sheet1").Range("B1")
Worksheets("DataBase").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\AA\AA\AA\AA\AA.accdb;Mode=ReadWrite", Destination:=Worksheets("DataBase").Range("A1")).QueryTable
.CommandType = xlCmdTable
'Name your table below
.CommandText = Array("MyDatabase")
'Refresh on open?
.RefreshOnFileOpen = False
'Refresh rate (in minutes)
.RefreshPeriod = 20
.Refresh BackgroundQuery:=False
End With
End Sub