Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Works when stepping thru only
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: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("D4:D856"), Type:=xlFillDefault Worksheets("Sheet1").Range("D4:D856").NumberFormat = "h:mm" Range("A4").Select End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why stepping thru works differently module vs. worksheet | Excel Programming | |||
Macros: Step Thru Works, Run Works, Keyboard Shortcut Locks up | Excel Programming | |||
BreakLinks method works when Stepping Through but not when Running! | Excel Programming | |||
Excel Addin works that works on a template workbook | Excel Programming | |||
VBA Code works by stepping through, not by running | Excel Programming |