Changing OLE DB connection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a macro that will automatically Edit OLE DB Query each
month. The closest help I got was the following:
tSource="C:\Mar05\File05.xls"
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & tSource & ";" & _
"Extended Properties=Excel 8.0;"
.Open
End With

Not sure of the Open but what I'd like to see the new source file when I
check on the Edit OLE DB Query screen the next time.

Thanks in advance.
 
Jam22171 said:
I'm trying to create a macro that will automatically Edit OLE DB Query each
month. The closest help I got was the following:
tSource="C:\Mar05\File05.xls"
Click to expand...

Maybe one of

tSource="C:\" & Format$(Date, "mmmyy") & "\File05.xls"
tSource="C:\" & Format$(Date, "mmmyy") & "\File" & Format$(Date, "yy")
& ".xls"
tSource="C:\" & Format$(Date, "mmmyy") & "\File" & Format$(Date, "mm")
& ".xls"
 
Thanks, peregenem. What I actually need is how to implement the change of
connection. My macro coded as follows:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
tSource="C:\Mar05\File05.xls" 'new
connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & tSource & ";" & _
"Extended Properties=Excel 8.0;" 'to replace old source
file with new
.Open
End With

I'm not sure if this is the right approach because I can't make it work.
Thanks
 
Jam22171 said:
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & tSource & ";" & _
"Extended Properties=Excel 8.0;" 'to replace old source

I'm not sure if this is the right approach because I can't make it work.
Click to expand...

Try putting the Excel 8.0 in single quotes

..Provider = "Microsoft.Jet.OLEDB.4.0"
..ConnectionString = "Data Source=" & tSource & ";" & _
"Extended Properties='Excel 8.0'"
 
Back
Top