Solved

Quickbooks Estimates Excel VBA

Posted on 2013-11-06
9
660 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.
0
Comment
Question by:AccountantsTech
  • 6
  • 3
9 Comments
 
LVL 90

Expert Comment

by:John Hurst
ID: 39629153
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
0
 

Author Comment

by:AccountantsTech
ID: 39629221
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.
0
 
LVL 90

Expert Comment

by:John Hurst
ID: 39629972
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
0
 

Author Comment

by:AccountantsTech
ID: 39630400
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.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:AccountantsTech
ID: 39630601
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

0
 
LVL 90

Expert Comment

by:John Hurst
ID: 39631030
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
0
 

Author Comment

by:AccountantsTech
ID: 39642817
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.
0
 

Accepted Solution

by:
AccountantsTech earned 0 total points
ID: 39844437
ended up using this to fix it....

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

Author Closing Comment

by:AccountantsTech
ID: 39855704
This was a solution that solved it!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now