sayantan1286
Programmer
Hi,
I am relatively new to VBA coding. I want to extract data (by subsetting) from an access file and get the results in excel. After doing some research I have seen that using "querytables" in Excel VBA can do the trick. I have the following code :-
Sub test()
Dim a As String
Dim b As String
a = "ODBC;DBQ=C:\codes\Database1.mdb;Driver={Microsoft Access Driver (*.mdb)}"
b = "select values from table1 where ID=1"
With ActiveSheet.QueryTables.Add(Connection:=a, Destination:=Range("E15"))
.CommandText = b
.Refresh BackgroundQuery:=False
End With
End Sub
The table in Access has two columns: values and ID. For simplicity lets assume that ID can take two distinct values : 1 and 2.
When I run the above code for the first time, it works fine. The set of "values" for ID=1 are extracted in excel starting from E15. But the problem arises when I change the the value of ID to 2 and run the query. The original column gets shifted to F, which I do not want. I want the query to overwrite the results in column E.
I know there is a thing called "ActiveSheet.QueryTables(1).Refresh" but I am not able to use it properly.
I am trying to solve this issue for quite sometime now but with no luck. I must admit here that I am not well versed with all the functionalities of excel VBA since I am in learning phase.
Any help will be highly appreciated.
Thanks,
Sayantan
I am relatively new to VBA coding. I want to extract data (by subsetting) from an access file and get the results in excel. After doing some research I have seen that using "querytables" in Excel VBA can do the trick. I have the following code :-
Sub test()
Dim a As String
Dim b As String
a = "ODBC;DBQ=C:\codes\Database1.mdb;Driver={Microsoft Access Driver (*.mdb)}"
b = "select values from table1 where ID=1"
With ActiveSheet.QueryTables.Add(Connection:=a, Destination:=Range("E15"))
.CommandText = b
.Refresh BackgroundQuery:=False
End With
End Sub
The table in Access has two columns: values and ID. For simplicity lets assume that ID can take two distinct values : 1 and 2.
When I run the above code for the first time, it works fine. The set of "values" for ID=1 are extracted in excel starting from E15. But the problem arises when I change the the value of ID to 2 and run the query. The original column gets shifted to F, which I do not want. I want the query to overwrite the results in column E.
I know there is a thing called "ActiveSheet.QueryTables(1).Refresh" but I am not able to use it properly.
I am trying to solve this issue for quite sometime now but with no luck. I must admit here that I am not well versed with all the functionalities of excel VBA since I am in learning phase.
Any help will be highly appreciated.
Thanks,
Sayantan