ODBC Links 1

[JamieArvato]

I have 15 files a week all a copy of each other.

I am pulling the data through into one spreadsheet to run summary reports.

When doing Data>Import External Data>New Database Query> sometimes it is opening the source files and sometimes it won't !?!!? I don't understand.

Also is there a quick way to change what file, eg to the next weeks file, quicker than setting up a whole new file?

Thanks.

 

[SkipVought]

Hi,

If you turn on the macro recorder and then edit your query, you can view the code that is generated.

Look in the value assigend to the connect proberty and also the value assigned to the command text property. These are the places where you can point your query to another database/table(s), change query on the fly etc.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[JamieArvato]

This is the macro that was recorded, the part in the middle I have seperated by spaces though is in red in the code and this code will not run when I try, giving the error "Compile error" and highlighting the section I have spaced out .commandtext

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 01/11/2004 by Jamie Riden
'

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=X:\Agent Hours\Miele\Timesheet - Miele - WE 2004-11-07.xls;DefaultDir=X:\Agent Hours\Miele;DriverId=790;Max" _
), Array("BufferSize=2048;PageTimeout=5;")), Destination:=Range("A31852"))



.CommandText = Array( _
"SELECT Total_Week_Data.Date, Total_Week_Data.Campaign, Total_Week_Data.Agent, Total_Week_Data.Unique_ID, Total_Week_Data.Agency, Total_Week_Data.Role, Total_Week_Data.Sort_Order, Total_Week_Data.`Sub-" _
, _
"Group`, Total_Week_Data.Shift_Code, Total_Week_Data.Shift_In, Total_Week_Data.Shift_Out, Total_Week_Data.Shift_Breaks, Total_Week_Data.Actual_In, Total_Week_Data.Actual_Out, Total_Week_Data.Actual_Bre" _
, _
"aks, Total_Week_Data.Hrs_Wkd, Total_Week_Data.Deci_Hrs, Total_Week_Data.Absence_Reason, Total_Week_Data.Late, Total_Week_Data.Log_Agent, Total_Week_Data.Log_Unique_ID, Total_Week_Data.Log_Agency, Tota" _
, _
"l_Week_Data.Log_Role, Total_Week_Data.Log_Sort_Order, Total_Week_Data.`Log_Sub-Group`, Total_Week_Data.Log_Shift_Code, Total_Week_Data.Log_Shift_In, Total_Week_Data.Log_Shift_Out, Total_Week_Data.Log_" _
, _
"Shift_Breaks, Total_Week_Data.Log_Actual_In, Total_Week_Data.Log_Actual_Out, Total_Week_Data.Log_Actual_Breaks, Total_Week_Data.Log_Hrs_Wkd, Total_Week_Data.Log_Deci_Hrs, Total_Week_Data.Log_RD, Total" _
,,)





.Name = "Query from Excel Files_9"
.FieldNames = False
.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
End Sub

 

[SkipVought]

Your SQL is INCOMPLETE! No FROM clause.

Sub Macro1()
   cnn = "ODBC;"
   cnn = cnn & "DSN=Excel Files;"
   cnn = cnn & "DBQ=X:\Agent Hours\Miele\Timesheet - Miele - WE 2004-11-07.xls;"
   cnn = cnn & "DefaultDir=X:\Agent Hours\Miele;"
   cnn = cnn & "DriverId=790;"
   cnn = cnn & "MaxBufferSize=2048;"
   cnn = cnn & "PageTimeout=5;"
   
'your SQL is INCOMPLETE -- no FROM clause
   
   Sql = "SELECT Total_Week_Data.Date, Total_Week_Data.Campaign, Total_Week_Data.Agent, "
   Sql = Sql & "Total_Week_Data.Unique_ID , Total_Week_Data.Agency, Total_Week_Data.Role, "
   Sql = Sql & "Total_Week_Data.Sort_Order, Total_Week_Data.`Sub-Group`, Total_Week_Data.Shift_Code, "
   Sql = Sql & "Total_Week_Data.Shift_In, Total_Week_Data.Shift_Out, Total_Week_Data.Shift_Breaks, "
   Sql = Sql & "Total_Week_Data.Actual_In, Total_Week_Data.Actual_Out, Total_Week_Data.Actual_Breaks, "
   Sql = Sql & "Total_Week_Data.Hrs_Wkd, Total_Week_Data.Deci_Hrs, Total_Week_Data.Absence_Reason, "
   Sql = Sql & "Total_Week_Data.Late, Total_Week_Data.Log_Agent, Total_Week_Data.Log_Unique_ID, "
   Sql = Sql & "Total_Week_Data.Log_Agency, Total_Week_Data.Log_Role, Total_Week_Data.Log_Sort_Order, "
   Sql = Sql & "Total_Week_Data.`Log_Sub-Group`, Total_Week_Data.Log_Shift_Code, "
   Sql = Sql & "Total_Week_Data.Log_Shift_In, Total_Week_Data.Log_Shift_Out, "
   Sql = Sql & "Total_Week_Data.Log_Shift_Breaks, "
      
   With ActiveSheet.QueryTables(1)
      .Connection = cnn
      .CommandText = Sql
      .Refresh BackgroundQuery:=False
   End With
End Sub

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[JamieArvato]

I understand what your saying and I have wrote simple SQL in the past and understand the from clause, but don't know how to put it into this code, the from is;

X:\Agent Hours\Miele\Timesheet - Miele - WE 2004-11-07.xls

Table Name: Total_Week_Data

The code I pasted was what Excel recorded for me.

 

[SkipVought]

turn on you recorder again.

This time just EDIT insted of insertina a NEW query.

When you EDIT, only select a few columns.

post back the results.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[JamieArvato]

Here's the code it recorded;

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 01/11/2004 by Jamie Riden
'

'
Range("B9").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=Excel Files;DBQ=X:\Agent Hours\Miele\Timesheet - Miele - WE 2004-11-07.xls;DefaultDir=X:\Agent Hours\Miele;DriverId=790;Max" _
), Array("BufferSize=2048;PageTimeout=5;"))
.CommandText = Array( _
"SELECT Total_Week_Data.Date, Total_Week_Data.Shift_Code, Total_Week_Data.Hrs_Wkd" & Chr(13) & "" & Chr(10) & "FROM `X:\Agent Hours\Miele\Timesheet - Miele - WE 2004-11-07`.Total_Week_Data Total_Week_Data" _
)
.Refresh BackgroundQuery:=False
End With
End Sub

 

[SkipVought]

[/code]
Sub Macro8()
'
' Macro1 Macro
' Macro recorded 01/11/2004 by Jamie Riden

'
Dim cnn As String, sql As String, sPath As String, sWbName As String

sPath = "X:\Agent Hours\Miele"
sWbName = "Timesheet - Miele - WE 2004-11-07" 'notice -- no extension in ths variable

cnn = "ODBC;"
cnn = cnn & "DSN=Excel Files;"
cnn = cnn & "DBQ=" & sPath & "\" & sWbName & ".xls;"
cnn = cnn & "DefaultDir=" & sPath & ";"
cnn = cnn & "DriverId=790;"
cnn = cnn & "MaxBufferSize=2048;"
cnn = cnn & "PageTimeout=5;"

sql = "SELECT TWD.Date, TWD.Shift_Code, TWD.Hrs_Wkd "
sql = sql & "FROM `" & sPath & "\" & sWbName & "`.Total_Week_Data TWD " 'notice -- you can use any abbreviation like TWD

With ActiveSheet.QueryTables(1)
.Connection = cnn
.CommandText = sql
.Refresh BackgroundQuery:=False
End With

End Sub
[/code]


Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[JamieArvato]

Nearly there....

Getting a subscript out of range error.....

With ActiveSheet.QueryTables(1)

 

[SkipVought]

If the active sheet is the one WITH YOUR QUERY, it should work.

You're on a sheet WITHOUT a query table in it.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[JamieArvato]

That's excellent, thanks so much.

What would I tweak to make a new table on a blank sheet starting in cell A1?

Thanks again for your help.

 

[SkipVought]

   Dim cnn As String, sql As String, sPath As String, sWbName As String, MyQueryTableName As String
   
   sPath = "X:\Agent Hours\Miele"
   sWbName = "Timesheet - Miele - WE 2004-11-07"      'notice -- no extension in ths variable

   cnn = "ODBC;"
   cnn = cnn & "DSN=Excel Files;"
   cnn = cnn & "DBQ=" & sPath & "\" & sWbName & ".xls;"
   cnn = cnn & "DefaultDir=" & sPath & ";"
   cnn = cnn & "DriverId=790;"
   cnn = cnn & "MaxBufferSize=2048;"
   cnn = cnn & "PageTimeout=5;"
   
   sql = "SELECT TWD.Date, TWD.Shift_Code, TWD.Hrs_Wkd "
   sql = sql & "FROM `" & sPath & "\" & sWbName & "`.Total_Week_Data TWD "    'notice -- you can use any abbreviation like TWD
       
   MyQueryTableName = "Total_Week_Data_1"
   
   With ActiveSheet.QueryTables.Add( _
       Connection:=cnn, Destination:=Range("A1"))
      .CommandText = sql
      .Name = MyQueryTableName
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .Refresh BackgroundQuery:=False
   End With

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]