Excel vba using querytables

[godzi]

Hi,

I have a question, I am using the Get External data option in Excel to pull an Access database onto my excel spreasheet, though, everytime I want to use it the database has a different location. How can I change the code in vba for me to prompt me a location, in the code I attached below how can I prompt for location replacing: DBQ??, DefaultDir ?? and command text FROM 'path'?? ??

the code I have so far is this:
----------------------------------------------------------------
Sub AccessData()

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=Path.mdb;DefaultDir=Path??;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5" _
), Array(";UID=admin;")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT DISTINCT OWNERS.WELL, OWNERS.OPERATOR, OWNERS.SEARCHID" & Chr(13) & "" & Chr(10) & "FROM `PATH?????`.OWNERS OWNERS" & Chr(13) & "" & Chr(10) "HAVING (OWNERS.ID<>'1' And" _
, " OWNERS.ID Not Like '%U')" & Chr(13) & "" & Chr(10) & "ORDER BY OWNERS.ID")
.Name = "Query from Access Database_5"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
--------------------------------------------------------------------

However...

The Database name is a different name eveytime !!!! say: same drive E:\, same folder , different database names....


Thanks,

Godzi

 

[SkipVought]

I gave you some code.

Intead of parsing out the path from the GetOpeFileName method, just get the Right to get the filename

sDB = Right(sPath, Len(sPath)-instrrev("\",sPath))
sDB = Split(sDB, ".")(0)
...

You already know the path, so hard code.

Why would you have all these different databases to go to? Thats CRAZY!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[godzi]

It is giving me the "error 438; Object doesn't support this property or method.

Pointing at:
.Connection = sConn



Sorry I am new to the VBA environment....

 

[SkipVought]

Do a Debug.Print on sConn as post the results. Use HELP if need be.

And please answer, why all these different databases?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[godzi]

The dabatases are located in an external hard drive, I work with each single one of them, and then I place the results on a Main database.

How would the final code be?..

 

[SkipVought]

What are your results from sConn?

You still did not say WHY you have so many DB's. I am serious when I say that it is INSANE. Extremely poor system design! There IS a much better way!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[godzi]

I still get the error

 

[godzi]

NO results, Just the Error....

I ma using Excel just to filter and handle some records as filtered using SQL statement, then I use other methods to export and automate Access.

Which lines should I put the Debug.print???

?

 

[godzi]

I have this so far:
Sub AccessData()
Dim sConn As String, sSQL As String, sPath, sDB As String

sPath = Application.GetOpenFilename("Access Files (*.mdb), *.mdb")

If sPath <> False Then
sPath = Left(sPath, InStrRev(sPath, "\") - 1)
sDB = Right(sPath, Len(sPath) - InStrRev("\", sPath))
sDB = Split(sDB, ".")(0) '

Debug.Print sConn = "ODBC;DSN=MS Access Database;"
Debug.Print sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".mdb;"
Debug.Print sConn = sConn & "DefaultDir=" & sPath & ";"
Debug.Print sConn = sConn & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"

sSQL = "SELECT DISTINCT "
sSQL = sSQL & " OWNERS.WELL"
sSQL = sSQL & ", OWNERS.OPERATOR"
sSQL = sSQL & ", OWNERS.SEARCHID"
sSQL = sSQL & vbLf
sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.OWNERS OWNERS"
sSQL = sSQL & vbLf
sSQL = sSQL & "WHERE OWNERS.ID<>'1'"
sSQL = sSQL & " And OWNERS.ID Not Like '%U')"
sSQL = sSQL & vbLf
sSQL = sSQL & "ORDER BY OWNERS.ID"

With ActiveSheet.QueryTables()
.Connection = sConn
.CommandText = sSQL
.Refresh BackgroundQuery:=False
End With
End If

End Sub


Is that what I need... still getting "error 438 !!

 

[PHV]

Provided the sheet already have a QueryTable:

Sub AccessData()
Dim sConn As String, sSQL As String, sPath, sDB As String
sPath = Application.GetOpenFilename("Access Files (*.mdb), *.mdb")
If sPath <> False Then
  sPath = Left(sPath, InStrRev(sPath, "\") - 1)
  sDB = Split(Mid(sPath, InStrRev(sPath), "\") + 1), ".")(0)
  sConn = "ODBC;DSN=MS Access Database;"
  sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".mdb;"
  sConn = sConn & "DefaultDir=" & sPath & ";"
  sConn = sConn & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"
  Debug.Print sConn
  sSQL = "SELECT DISTINCT "
  sSQL = sSQL & "  OWNERS.WELL"
  sSQL = sSQL & ", OWNERS.OPERATOR"
  sSQL = sSQL & ", OWNERS.SEARCHID"
  sSQL = sSQL & vbLf
  sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.OWNERS OWNERS"
  sSQL = sSQL & vbLf
  sSQL = sSQL & "WHERE OWNERS.ID<>'1'"
  sSQL = sSQL & "  And OWNERS.ID Not Like '%U')"
  sSQL = sSQL & vbLf
  sSQL = sSQL & "ORDER BY OWNERS.ID"
  With ActiveSheet.QueryTables(1)
    .Connection = sConn
    .CommandText = sSQL
    .Refresh BackgroundQuery:=False
  End With
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[godzi]

Error: Run-Time '9' Subscript out of range.

I want to place the results.. say.. Sheet: DATA, "A1"




Thanks

 

[godzi]

The original . Recorded Macro for this code is:

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=E:\OK.mdb;DefaultDir=E:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5" _
), Array(";UID=admin;")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT DISTINCT OWNERS.WELL, OWNERS.OPERATOR, OWNERS.SEARCHID" & Chr(13) & "" & Chr(10) & "FROM `E:\OK`.OWNERS OWNERS" & Chr(13) & "" & Chr(10) & "GROUP BY OWNERS.WELL, OWNERS.OPERATOR, OWNERS.SEARCHID" & Chr(13) & "" & Chr(10) & "HAVING (OWNERS.SEARCHID<>'1' And" _
, " OWNERS.SEARCHID Not Like '%U')" & Chr(13) & "" & Chr(10) & "ORDER BY OWNERS.SEARCHID")
.Name = "Query from Access Database_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[PHV]

So, you create a new sheet each time ?
In my suggested code, replace this:
With ActiveSheet.QueryTables(1)
.Connection = sConn
.CommandText = sSQL
.Refresh BackgroundQuery:=False
End With
with this:
With ActiveSheet.QueryTables.Add Connection:=sConn
.CommandText = sSQL
.Refresh BackgroundQuery:=False
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo:
With ActiveSheet.QueryTables.Add(Connection:=sConn)
.CommandText = sSQL
.Refresh BackgroundQuery:=False
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[godzi]

I got: Run-time Error "450"

Wrong number of arguments or invalid property assignment

.....

I am losing my nerves...

 

[godzi]

The vba code was a macro recorded from the following SQL statement:

SELECT DISTINCT OWNERS.WELL, OWNERS.OPERATOR, OWNERS.SEARCHID
FROM `E:\X Folder\X database`.OWNERS OWNERS
GROUP BY OWNERS.WELL, OWNERS.OPERATOR, OWNERS.SEARCHID
HAVING (OWNERS.SEARCHID<>'1' And OWNERS.SEARCHID Not Like '%U')
ORDER BY OWNERS.SEARCHID

The way I am handling it is like this, I open the External data, then I select my saved query, and edit it in Microsoft Query, where I need to click on SQL, and change de portion: E:\ yadda\yadda... to database I want to open....

That said, I was hoping to automate that sql statement with VBA. !!!!

The Drive, and folder where the Access Databases come from are Network !!!. let's say a SERVER. as you cansee in the SQL statement...

Any suggestions??


Thanks. !!!

 

[PHV]

Replace this:
With ActiveSheet.QueryTables.Add(Connection:=sConn)
with this:
With ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("A1"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[godzi]

hey PHV,

I have included what you said, the code is this:

Sub AccessData()
Dim sConn As String, sSQL As String, sPath, sDB As String

sPath = Application.GetOpenFilename("Access Files (*.mdb), *.mdb")

If sPath <> False Then
sPath = Left(sPath, InStrRev(sPath, "\") - 1)
sDB = Right(sPath, Len(sPath) - InStrRev("\", sPath))
sDB = Split(sDB, ".")(0) '

sConn = "ODBC;DSN=MS Access Database;"
sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".mdb;"
sConn = sConn & "DefaultDir=" & sPath & ";"
sConn = sConn & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"

Debug.Print sConn

sSQL = "SELECT DISTINCT "
sSQL = sSQL & " OWNERS.WELL"
sSQL = sSQL & ", OWNERS.OPERATOR"
sSQL = sSQL & ", OWNERS.SEARCHID"
sSQL = sSQL & vbLf
sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.OWNERS OWNERS"
sSQL = sSQL & vbLf
sSQL = sSQL & "WHERE OWNERS.ID<>'1'"
sSQL = sSQL & " And OWNERS.ID Not Like '%U')"
sSQL = sSQL & vbLf
sSQL = sSQL & "ORDER BY OWNERS.ID"

With ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("A1"))
.CommandText = sSQL
.Refresh BackgroundQuery:=False
End With
End If
End Sub
---------------------------------------------
When I run it prompts a window that says:

ODBC Microsoft Access Driver Login Failed, Not a valid file name.

I press OK, and then it ask me to Login, Data Source, etc... . Well I choose database, then I select the correct database... and then the error jumps to VBA.

Run-time error '1004', SQL Syntax Error.

It points at: .Refresh Backgroundquery:=False

Any thoughts??

 

[SkipVought]

That's because the DB you selected in the GetOpenFileName method is not the same one that you're opening.

Consequently, the FROM statement has the path & name that you ORIGINALLY selected, and not the one that you opened.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[godzi]

Using the Debug method... I found this:

sConn in its line: DBQ is: DBQ=E:\DBfolder\E:\DBname.mdb...

I suspect the sSQL , FROM clause is also wrong...


What should I change?