Solved

Quickbooks Estimates Excel VBA

Posted on 2013-11-06
9
700 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
[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
  • 6
  • 3
9 Comments
 
LVL 95

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 95

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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
 

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 95

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

734 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