I have an Excel workbook. I also have a macro that runs a saved query (.dqy)
on the network drive to bring data from the database into Excel worksheet.

My question is can I use a relative path for use in the macro and the query
file. ie ..\..\

Here is the code for the macro:

Sub UpdateReport12()
'
' UpdateReport12 Macro
' Macro recorded 21/06/2007 by Social Services
'

'
Sheets("Data").Select
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;O:\Adult Community & Housing Services\Policy Performance &
Resources\Technology & Business Support\Support Services\Accident
Reporting\Accident & Incidents - Database\Reports\Report_1.2 - HSE.dqy" _
, Destination:=Range("A1"))
.Name = "Report_1.2 - HSE_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("report").Select
End Sub

Here is the code for the query file:

XLODBC
1
DSN=MS Access Database;DBQ=O:\Adult Community & Housing Services\Policy
Performance & Resources\Technology & Business Support\Support
Services\Accident Reporting\Accident & Incidents - Database\Reports\accident
reporting.mdb;DefaultDir=O:\Adult Community & Housing Services\Policy
Performance & Resources\Technology & Business Support\Support
Services\Accident Reporting\Accident & Incidents -
Database\Reports;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
SELECT Report_12_HSE_Reportable_diseases.MainDate,
Report_12_HSE_Reportable_diseases.Division,
Report_12_HSE_Reportable_diseases.`HSE Inc`,
Report_12_HSE_Reportable_diseases.incident_type,
Report_12_HSE_Reportable_diseases.type_of_person,
Report_12_HSE_Reportable_diseases.DivSplit,
Report_12_HSE_Reportable_diseases.DMBC FROM
Report_12_HSE_Reportable_diseases Report_12_HSE_Reportable_diseases


MainDate Division HSE Inc incident_type type_of_person DivSplit

Many thanks in advance.
--
Richard

Does this help?

Sub UpdateReport12()
'
' UpdateReport12 Macro
' Macro recorded 21/06/2007 by Social Services
'

'

Set objShell = CreateObject("Shell.Application")
On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&)
If Not objFolder Is Nothing Then
Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path
End If

Sheets("Data").Select
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
Range("A1").Select
With ActiveSheet.QueryTables.Add( _
Connection:="FINDER;" & _
Folder & "\Report_1.2 - HSE.dqy", _
Destination:=Range("A1"))
.Name = "Report_1.2 - HSE_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("report").Select
End Sub


"Richard" wrote:

I have an Excel workbook. I also have a macro that runs a saved query (.dqy)
on the network drive to bring data from the database into Excel worksheet.

My question is can I use a relative path for use in the macro and the query
file. ie ..\..\

Here is the code for the macro:

Sub UpdateReport12()
'
' UpdateReport12 Macro
' Macro recorded 21/06/2007 by Social Services
'

'
Sheets("Data").Select
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;O:\Adult Community & Housing Services\Policy Performance &
Resources\Technology & Business Support\Support Services\Accident
Reporting\Accident & Incidents - Database\Reports\Report_1.2 - HSE.dqy" _
, Destination:=Range("A1"))
.Name = "Report_1.2 - HSE_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("report").Select
End Sub

Here is the code for the query file:

XLODBC
1
DSN=MS Access Database;DBQ=O:\Adult Community & Housing Services\Policy
Performance & Resources\Technology & Business Support\Support
Services\Accident Reporting\Accident & Incidents - Database\Reports\accident
reporting.mdb;DefaultDir=O:\Adult Community & Housing Services\Policy
Performance & Resources\Technology & Business Support\Support
Services\Accident Reporting\Accident & Incidents -
Database\Reports;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
SELECT Report_12_HSE_Reportable_diseases.MainDate,
Report_12_HSE_Reportable_diseases.Division,
Report_12_HSE_Reportable_diseases.`HSE Inc`,
Report_12_HSE_Reportable_diseases.incident_type,
Report_12_HSE_Reportable_diseases.type_of_person,
Report_12_HSE_Reportable_diseases.DivSplit,
Report_12_HSE_Reportable_diseases.DMBC FROM
Report_12_HSE_Reportable_diseases Report_12_HSE_Reportable_diseases


MainDate Division HSE Inc incident_type type_of_person DivSplit

Many thanks in advance.
--
Richard

Thanks Joel

Selecting the folder isn't really what I am looking to do.

The reason I want to have the path as relative is because the database,
spreadsheets and query files will need to be moved to a different location on
the server in the near future (3 times at least), so I don't want an absolute
path (the complete path of the server). I just want to state "..\Report_1.2 -
HSE.dqy" for it to look up one level on the server.

Hope this makes sense.

--
Richard


"Joel" wrote:

Does this help?

Sub UpdateReport12()
'
' UpdateReport12 Macro
' Macro recorded 21/06/2007 by Social Services
'

'

Set objShell = CreateObject("Shell.Application")
On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H1&)
If Not objFolder Is Nothing Then
Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path
End If

Sheets("Data").Select
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
Range("A1").Select
With ActiveSheet.QueryTables.Add( _
Connection:="FINDER;" & _
Folder & "\Report_1.2 - HSE.dqy", _
Destination:=Range("A1"))
.Name = "Report_1.2 - HSE_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("report").Select
End Sub


"Richard" wrote:

I have an Excel workbook. I also have a macro that runs a saved query (.dqy)
on the network drive to bring data from the database into Excel worksheet.

My question is can I use a relative path for use in the macro and the query
file. ie ..\..\

Here is the code for the macro:

Sub UpdateReport12()
'
' UpdateReport12 Macro
' Macro recorded 21/06/2007 by Social Services
'

'
Sheets("Data").Select
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;O:\Adult Community & Housing Services\Policy Performance &
Resources\Technology & Business Support\Support Services\Accident
Reporting\Accident & Incidents - Database\Reports\Report_1.2 - HSE.dqy" _
, Destination:=Range("A1"))
.Name = "Report_1.2 - HSE_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("report").Select
End Sub

Here is the code for the query file:

XLODBC
1
DSN=MS Access Database;DBQ=O:\Adult Community & Housing Services\Policy
Performance & Resources\Technology & Business Support\Support
Services\Accident Reporting\Accident & Incidents - Database\Reports\accident
reporting.mdb;DefaultDir=O:\Adult Community & Housing Services\Policy
Performance & Resources\Technology & Business Support\Support
Services\Accident Reporting\Accident & Incidents -
Database\Reports;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
SELECT Report_12_HSE_Reportable_diseases.MainDate,
Report_12_HSE_Reportable_diseases.Division,
Report_12_HSE_Reportable_diseases.`HSE Inc`,
Report_12_HSE_Reportable_diseases.incident_type,
Report_12_HSE_Reportable_diseases.type_of_person,
Report_12_HSE_Reportable_diseases.DivSplit,
Report_12_HSE_Reportable_diseases.DMBC FROM
Report_12_HSE_Reportable_diseases Report_12_HSE_Reportable_diseases


MainDate Division HSE Inc incident_type type_of_person DivSplit

Many thanks in advance.
--
Richard