#REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub
Go Back  Xtreme Visual Basic Talk > > > > #REF after ODBC query - cannot solve with paste Sub


Reply
 
Thread Tools Display Modes
  #1  
Old 04-22-2011, 02:06 AM
flavios flavios is offline
Newcomer
 
Join Date: Mar 2011
Location: Milan ITALY
Posts: 3
Lightbulb #REF after ODBC query - cannot solve with paste Sub


I have a form that makes the user enter two limit dates for a query.
Going out they are simply entered in two cells used by the query as parameters. The ODBC query is launched by the change.

On a different worksheet I have simply "copied" all data (such as =Data!A5) in a different column order interspersing with "calculations".

The problem is that the query process obviously DELETES/ADDS some rows and the corresponding formulas go to #REF error (or skip).

I have a simple COPY/PASTE Sub that refreshes all the formulas in the Table. But this apparently runs before the query changes are finished and does not solve the problem. If I run the same paste as a macro it's ok.

I appear to need a sort of "Wait for idle" on the data upload process (that I do not control in my code) or a change event on the "Data" worksheet that really works. Is there any? Keep it simple
Flavio

I tried with no success:
Private Sub Worksheet_PivotTableAfterValueChange(ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range)
copyFreshAfterForm
End Sub
Reply With Quote
  #2  
Old 04-22-2011, 09:00 AM
Bob Phillips's Avatar
Bob Phillips Bob Phillips is offline
Contributor
 
Join Date: Jul 2009
Posts: 507
Default

I have this problem with an XML import. What I did was to save all the formulae before the query, then re-instate them afterwards. Tedious, but works once you have written the code.

If of interest, this is my code that captures and re-applies the formulae

Code:
'-----------------------------------------------------------------
Public Function FormulaCapture()
'-----------------------------------------------------------------
Dim mpShFormula As Worksheet
Dim mpCell As Range
Dim mpNextrow As Long
Dim i As Long

    If Ask(Replace(Replace(MSG_ASK_FORMULA_APPLY, _
                    PLACEHOLDER_SHEET, ActiveSheet.Name), _
                PLACEHOLDER_BOOK, ActiveWorkbook.Name)) = vbYes Then
                
        Call SheetExists(sh:=SHEET_FORMULA, _
                         wb:=ActiveWorkbook, _
                         Create:=True, _
                         Clearout:=True, _
                         Visible:=True)
        Set mpShFormula = ActiveWorkbook.Worksheets(SHEET_FORMULA)
                
        With ActiveSheet
            
            mpNextrow = 1
            mpShFormula.Range("A1:B1").Value = Array("Cell", "Formula")
            mpShFormula.Rows(1).Font.Bold = True
            mpShFormula.Columns("B").NumberFormat = "Text"
            For Each mpCell In .UsedRange
            
                If mpCell.HasFormula Then
                
                    mpNextrow = mpNextrow + 1
                    mpShFormula.Cells(mpNextrow, "A").Value2 = "''" & .Name & "'!" & .Cells(mpCell.Row, mpCell.Column).Address
                    mpShFormula.Cells(mpNextrow, "B").Value2 = "'" & mpCell.Formula
                End If
            Next mpCell
            
            mpShFormula.Columns("B").AutoFit
        End With
    End If
End Function

'-----------------------------------------------------------------
Public Function FormulaApply() As Boolean
'-----------------------------------------------------------------

    If Ask(Replace(Replace(MSG_ASK_FORMULA_APPLY, _
                    PLACEHOLDER_SHEET, ActiveSheet.Name), _
                PLACEHOLDER_BOOK, ActiveWorkbook.Name)) = vbYes Then
                
        If SheetExists(sh:=SHEET_FORMULA, _
                       wb:=ActiveWorkbook) Then
            
            Call FormulaRestore
        End If
    End If
End Function
Reply With Quote
Reply

Tags
excel 2010 automation, idle, odbc


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
#REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub #REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub
 
#REF after ODBC query - cannot solve with paste Sub
#REF after ODBC query - cannot solve with paste Sub
 
-->