Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Course Of The Month
9 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Quickbooks Estimates Excel VBA
Posted on 2013-11-06
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.
[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
Active today
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
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
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.
Active today
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
Active today
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
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.
Featured Post
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
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| converting excel into labels | 6 | 34 | |
| Need help with pivot summary or report | 17 | 45 | |
| Why Won't My Excel Workbook Refresh The Data | 2 | 35 | |
| how can I determine if an Excel spreadsheet will fit on an 8.5 by 11 inch sheet? | 3 | 55 |
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…
Suggested Courses
Course of the Month9 days, 6 hours left to enroll
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.