Changing OLE DB connection

[Jam22171]

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.

[[email protected]]

Jam22171 wrote:
> 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"

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"

[Jam22171]

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



"[email protected]" wrote:

>
>
> Jam22171 wrote:
> > 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"
>
> 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"
>
>

[[email protected]]

Jam22171 wrote:
> 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.

Try putting the Excel 8.0 in single quotes

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

[Shades799]

Hi Jam, Peregenem, All

I have the below connection specified in the Data->Import External Data->Edit Query but I wish to replace the static SHADES799 with a variable Username that will pick up the Windows username of the person running the sheet. How and where can I make this change to achieve desired results.

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=d:\Documents and Settings\SHADES799\My Documents\Employee_RankingGrid.xls;Mode=ReadWrite;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

Many Thanks for any help,
Colm