Excel Connections

[tg_et]

I have an Excel file that pulls data frommultiple text files via the "Connections" menu. The problem I'mrunning into is I need to be able to change the path to the text files fromwithin VBA.

 

[ranman256]

you could put the Data paths into a cell. I have a sheet, Config, with a cell to the db.

In VBE , insert MODULE and paste the code below into it.
the connections are done via code

public goConn as ADODB.Connection
public gDb

'-----------------
sub ConnectDB()
'-----------------
'BE SURE ADO is put in VBE References:  alt-F11, tools, references.

gDB = sheets("Config").range("A2").value

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & gDB
OLEDB:EngineType=4"
Set goConn = New ADODB.Connection
goConn.Open sConnect
end sub



'-----------------
sub GetLateRecords()
'-----------------
dim rst
dim sSql as string
 
sSql = "select * from table where [late]=true"
Set rst = getRst(sSql)

range("A1").select
ActiveCell.CopyFromRecordset rst
set rst = nothing
end subs


'-----------------
Public Function getRst(ByVal pvQry) As ADODB.recordset
'-----------------
Dim rst As ADODB.recordset

On Error GoTo errGetRst

If goConn Is Nothing Then ConnectDB

Set rst = CreateObject("ADODB.Recordset")
With rst
    Set .ActiveConnection = goConn
    .CursorLocation = adUseClient
    .Open pvQry
End With
Set getRst = rst

Exit Function
errGetRst:
MsgBox Err.Description, , "getRst():" & Err
End Function



'-----------------
Public Function RunActionQry(pvQry, Optional ByVal pbIsSql As Boolean)
'-----------------
On Error GoTo errRun

  ' Assign to ADO Command object
Set goCmd = New ADODB.Command
With goCmd
  .ActiveConnection = goConn
  .CommandText = pvQry
  
  If pbIsSql Then
    .CommandType = adCmdText
  Else
    .CommandType = adCmdStoredProc
  End If
  
  .Execute
End With
Exit Function

errRun:
RunActionQry = Err
End Function