B
Billy B
The following code inserts a query, adds a new column that formats column C's
time as h:ss, moves that column to the right of column C then hides Column C.
When I step through the code it works perfect but when I take all the break
points off and run it, column Column C is hidden and the results of the
CreateTimeFormula procedure does not seem to run (or there is some other
problem. I am really stuck. Thank you.
Sub CreateQueryTables()
Dim strCnn As String, strCmdTxt As String
' Set up connection string.
strCnn = "ODBC;DBQ=H:\EducationPro\EducationPro-New.mdb;" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;PageTimeout=15"
'If I password this then enable below
'strCnn = strCnn & ";Password=<pwd>;User ID=<userID>"
strCmdTxt = Empty
strCmdTxt = "SELECT DISTINCT [Unit] & [Tier] & [Room] & [Bed] AS House,
" & _
"tblStudentHistory.DOCNumber AS [DOC#], tblStudentHistory.Time,
[LastName] & ', ' " & _
"& [FirstName] AS NAME, 'MSC Education' AS DESTINATION FROM
tblStudentHistory " & _
"INNER JOIN tblStudents ON tblStudentHistory.DOCNumber =
tblStudents.DOCNumber " & _
"WHERE (((tblStudentHistory.Quarter) = 'Summer 2007')) ORDER BY
tblStudentHistory.Time, " & _
"[LastName] & ', ' & [FirstName];"
'Clear contents of columns A through E to input to refresh query info
Columns("A:G").Select
Selection.Delete
' Create the QueryTable on the ActiveSheet at the range stated.
With ActiveSheet.QueryTables.Add(Connection:=strCnn,
Destination:=Range("A3"))
If .QueryType = xlOLEDBQuery Then .CommandType = xlCmdDefault
.CommandText = strCmdTxt
.RefreshStyle = xlOverwriteCells
.HasAutoFormat = False
.RefreshOnFileOpen = False
.Refresh
End With
'Replaces unit names with initials
ReplaceUnit
'Fill column F with time and formula for 12 hour clock
CreateTimeFormula
'Hide original times column and display formatted data after moving the
column
Columns("C:C").Select
Worksheets("Sheet1").Columns("C").Hidden = True
End Sub
Sub CreateTimeFormula()
'
' CreateTimeFormula Macro
' Created to fill column F with time and formula for 12 hour clock
'
Columns("D").Select
Selection.Insert
Range("D3").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",MOD(RC[-1],0.5))"
Range("D4").Select
Selection.AutoFill Destination:=Range("D4856"), Type:=xlFillDefault
Worksheets("Sheet1").Range("D4856").NumberFormat = "h:mm"
Range("A4").Select
End Sub
time as h:ss, moves that column to the right of column C then hides Column C.
When I step through the code it works perfect but when I take all the break
points off and run it, column Column C is hidden and the results of the
CreateTimeFormula procedure does not seem to run (or there is some other
problem. I am really stuck. Thank you.
Sub CreateQueryTables()
Dim strCnn As String, strCmdTxt As String
' Set up connection string.
strCnn = "ODBC;DBQ=H:\EducationPro\EducationPro-New.mdb;" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;PageTimeout=15"
'If I password this then enable below
'strCnn = strCnn & ";Password=<pwd>;User ID=<userID>"
strCmdTxt = Empty
strCmdTxt = "SELECT DISTINCT [Unit] & [Tier] & [Room] & [Bed] AS House,
" & _
"tblStudentHistory.DOCNumber AS [DOC#], tblStudentHistory.Time,
[LastName] & ', ' " & _
"& [FirstName] AS NAME, 'MSC Education' AS DESTINATION FROM
tblStudentHistory " & _
"INNER JOIN tblStudents ON tblStudentHistory.DOCNumber =
tblStudents.DOCNumber " & _
"WHERE (((tblStudentHistory.Quarter) = 'Summer 2007')) ORDER BY
tblStudentHistory.Time, " & _
"[LastName] & ', ' & [FirstName];"
'Clear contents of columns A through E to input to refresh query info
Columns("A:G").Select
Selection.Delete
' Create the QueryTable on the ActiveSheet at the range stated.
With ActiveSheet.QueryTables.Add(Connection:=strCnn,
Destination:=Range("A3"))
If .QueryType = xlOLEDBQuery Then .CommandType = xlCmdDefault
.CommandText = strCmdTxt
.RefreshStyle = xlOverwriteCells
.HasAutoFormat = False
.RefreshOnFileOpen = False
.Refresh
End With
'Replaces unit names with initials
ReplaceUnit
'Fill column F with time and formula for 12 hour clock
CreateTimeFormula
'Hide original times column and display formatted data after moving the
column
Columns("C:C").Select
Worksheets("Sheet1").Columns("C").Hidden = True
End Sub
Sub CreateTimeFormula()
'
' CreateTimeFormula Macro
' Created to fill column F with time and formula for 12 hour clock
'
Columns("D").Select
Selection.Insert
Range("D3").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",MOD(RC[-1],0.5))"
Range("D4").Select
Selection.AutoFill Destination:=Range("D4856"), Type:=xlFillDefault
Worksheets("Sheet1").Range("D4856").NumberFormat = "h:mm"
Range("A4").Select
End Sub