Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Changing OLE DB connection
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. |
#2
|
|||
|
|||
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" |
#3
|
|||
|
|||
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 " 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" |
#4
|
|||
|
|||
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'" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I save an OLAP connection to distribute with an Excel sheet | Excel Discussion (Misc queries) | |||
Changing the format of an Excel output file made by Microsoft Access | Excel Discussion (Misc queries) | |||
How do I stop excel automatically changing my date to 2005? | Excel Discussion (Misc queries) | |||
changing page break in an excel file | New Users to Excel | |||
Changing Server on a Microsoft Query | Excel Discussion (Misc queries) |