Solved

Quickbooks Estimates Excel VBA

Posted on 2013-11-06

1 solution

Medium Priority

721 Views

Last Modified: 2014-02-13

I am trying to bring all of the estimate into excel from quickbooks. We recently added some group items and they are not pulling through into the report.

It appears to me that since the group items only have a "Total" column and not an "Amount" value, they are not being pulled across correctly.

I have search all over the internet at all of the on screen references that i could find online and have found nothing to help me. If you have any advice or solutions, please help.

Thank you for your time.

Comment

Question by:AccountantsTech

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

9 Comments

LVL 97

Expert Comment

by:Experienced Member

ID: 396291532013-11-06

Did you try the Intuit Marketplace?

Go here: http://marketplace.intuit.com/search.aspx?search=import%20from%20excel

and there are a number of Excel import tools available. Transaction Pro Importer is for Excel files (and others), and Dynamic Venture JE is for creating journal entries from Excel.

... Thinkpads_User

Author Comment

by:AccountantsTech

ID: 396292212013-11-06

This isn't quite what i am looking for. I have the Vba with the QB SDK already pulling in records it just isn't pulling in the group items on the estimate sheet.

LVL 97

Expert Comment

by:Experienced Member

ID: 396299722013-11-07

Have you looked (on the same link as above) at the QOBDC tools?

Are the group items part of a QB "List" and should you be using details? That is, can you pull both group and details?

.... Thinkpads_User

Author Comment

by:AccountantsTech

ID: 396304002013-11-07

Do you mean the Transaction Pro Importer and Dynamic Venture JE? I'm not looking for an app but rather a tweak to my code that will bring in all of the estimates.

I am using the IEstimateQuery to pull an IEstimateRet which has the elements of the IEstimateLine and IEstimateGroupLine. The problem is that it is not pulling in all of the items.

I don't have access to the code right now but i will try to get it so that i can post a sample it.

Author Comment

by:AccountantsTech

ID: 396306012013-11-07

Sub EstimatesQuery()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error Resume Next

Application.Sheets("Estimates Detail").Activate

Rows("2:100000").Select
Selection.Delete Shift:=xlUp

Dim sessionManager As QBSessionManager
Set sessionManager = New QBSessionManager
sessionManager.OpenConnection "", "Gehman Job Program"
sessionManager.BeginSession qbFile, omDontCare

Dim requestSet As IMsgSetRequest
Set requestSet = sessionManager.CreateMsgSetRequest("US", 6, 0)

Dim EstimateQueryRq As IEstimateQuery
Set EstimateQueryRq = requestSet.AppendEstimateQueryRq()

EstimateQueryRq.IncludeLineItems.SetValue (True)

Dim responseMsgSet As IMsgSetResponse
Set responseMsgSet = sessionManager.DoRequests(requestSet)

If (responseMsgSet Is Nothing) Then
Exit Sub
End If

Dim responseList As IResponseList
Set responseList = responseMsgSet.responseList
If (responseList Is Nothing) Then
Exit Sub
End If

Dim response As IResponse
Set response = responseList.GetAt(0)

Dim EstimateRet As IEstimateRetList
Set EstimateRet = response.Detail

Dim CurrEst As IEstimateRet

Dim ctr As Integer
ctr = 2

For i = 0 To EstimateRet.Count - 1

    If i = 1619 Then
        MsgBox ("hey")
    End If
        
    Set CurrEst = EstimateRet.GetAt(i)

    If (Not CurrEst.CustomerRef.FullName Is Nothing) Then
        Dim FullName As String
        FullName = CurrEst.CustomerRef.FullName.GetValue()
    End If

    If (Not CurrEst.TxnDate Is Nothing) Then
        Dim TxnDate As String
        TxnDate = CurrEst.TxnDate.GetValue()
    End If

    If (Not CurrEst.RefNumber Is Nothing) Then
        Dim RefNumber As String
        RefNumber = CurrEst.RefNumber.GetValue()
    End If

    If (Not CurrEst.OREstimateLineRetList Is Nothing) Then
        Dim i1501 As Integer

        For i1501 = 0 To CurrEst.OREstimateLineRetList.Count - 1
            Dim OREstimateLineRet1502 As IOREstimateLineRet
            Set OREstimateLineRet1502 = CurrEst.OREstimateLineRetList.GetAt(i1501)
  
            If (Not OREstimateLineRet1502.EstimateLineRet Is Nothing) Then

                If (Not OREstimateLineRet1502.EstimateLineRet.ItemRef Is Nothing) Then
                    'Get value of FullName
                    If (Not OREstimateLineRet1502.EstimateLineRet.ItemRef.FullName Is Nothing) Then
                        Dim FullNameLI As String
                        FullNameLI = OREstimateLineRet1502.EstimateLineRet.ItemRef.FullName.GetValue()
                    End If
                
                    If (Not OREstimateLineRet1502.EstimateLineRet.Amount Is Nothing) Then
                        Dim Amount As Double
                        Amount = OREstimateLineRet1502.EstimateLineRet.Amount.GetValue()
                    End If

                    Sheets("Estimate Detail").Cells(ctr, 1) = FullName
                    Sheets("Estimate Detail").Cells(ctr, 2) = FullNameLI
                    Sheets("Estimate Detail").Cells(ctr, 3) = Amount
                    Sheets("Estimate Detail").Cells(ctr, 4) = TxnDate
                    Sheets("Estimate Detail").Cells(ctr, 5) = RefNumber
  
                    FullNameLI = ""
                    Amount = 0
    
                    ctr = ctr + 1
    
                End If
   
            End If

        Next i1501
 
    End If

    If (Not OREstimateLineRet1502.EstimateLineGroupRet Is Nothing) Then

        If (Not OREstimateLineRet1502.EstimateLineGroupRet.EstimateLineRetList Is Nothing) Then
            Dim i1541 As Integer
    
        For i1541 = 0 To OREstimateLineRet1502.EstimateLineGroupRet.EstimateLineRetList.Count - 1
            Dim EstimateLineRet As IEstimateLineRet
            Set EstimateLineRet = OREstimateLineRet1502.EstimateLineGroupRet.EstimateLineRetList.GetAt(i1541)
            If (Not EstimateLineRet.ItemRef Is Nothing) Then
                
                If (Not EstimateLineRet.ItemRef.FullName Is Nothing) Then
                    Dim FullNameLI2 As String
                    FullNameLI2 = EstimateLineRet.ItemRef.FullName.GetValue()
                End If
                
                If (Not EstimateLineRet.Amount Is Nothing) Then
                    Dim Amount2 As Double
                    Amount2 = EstimateLineRet.Amount.GetValue()
                End If

                Sheets("Estimate Detail").Cells(ctr, 1) = FullName
                Sheets("Estimate Detail").Cells(ctr, 2) = FullNameLI2
                Sheets("Estimate Detail").Cells(ctr, 3) = Amount2
                Sheets("Estimate Detail").Cells(ctr, 4) = TxnDate
                Sheets("Estimate Detail").Cells(ctr, 5) = RefNumber
      
                FullNameLI2 = ""
                Amount2 = 0
      
                ctr = ctr + 1
  
            End If
  
        Next i1541

    End If

End If

err1:

FullName = ""
TxnDate = ""
RefNumber = ""

Next

Application.Calculation = xlCalculationAutomatic

End Sub

Open in new window

LVL 97

Expert Comment

by:Experienced Member

ID: 396310302013-11-07

I cannot see from the code (and I am NOT a code expert at all) why some detail lines would come but not all. Are the lines not coming zero? Or do they have a different "reference" field (what you are looking up)?

In my post above, QOBDC mean the product farther down the list, not the first two I pointed you to which do not do what you want.

.... Thinkpads_User

Author Comment

by:AccountantsTech

ID: 396428172013-11-12

There lines are there but all of the information is blank. As if i am requesting the wrong thing. All i am doing it pulling an Estimate List and then for each of the estimates i am getting out all of the Line Items and the Group Line Items.

Accepted Solution

by:AccountantsTech

AccountantsTech earned 0 total points

ID: 398444372014-02-08

ended up using this to fix it....

http://synergration.com/software/coreobjx/

Author Closing Comment

by:AccountantsTech

ID: 398557042014-02-13

This was a solution that solved it!

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Automating Microsoft Excel from Access (Part II)

Article by: Dale Fye

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.

Dynamic Data Vaildation

Article by: Rob

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.

Building Probability Models in Excel Part 5: Modeling an Investment Using Discrete Random Variables

Video by: Toby

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…