Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Course Of The Month
1 day, 17 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
June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month1 day, 17 hours left to enroll
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.