ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Quickbooks Estimates Excel VBA
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.
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.
Asked:
-
6
3
1 Solution
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.
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
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
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.
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.
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
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
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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Be seen. Boost your question’s priority for more expert views and faster solutions
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
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