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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Works when stepping thru only
Check your query is not executing in the background.
If it is, it may not be completed in time for the rest of your code. Try adding ..BackgroundQuery = False Tim "Billy B" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Works when stepping thru only
Put this at the top of your code module:
Public Function HalfSecDly() s = Timer + 0.5 Do While Timer < s DoEvents Loop End Function and then add this line: HalfSecDly after your call for: CreateTimeFormula Sometimes the End Sub executes before code outside the main sub can execute. It shouldn't, but it does. With the delay, it might allow your time format code to execute. "Billy B" wrote: 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 |
Reply |
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 |