turn123
asked on
Extended information on the newsletter article
The archived copy of the newsletter article this thread is for can be found at http://www.ee-stuff.com/Newsletter/061405newsletter.htm
Getting started
-The first time you access QuickBooks http:#14117661
Real examples (Perl scripts)
-How to use http:#14117719
-Sending the first XML request and reciving the response to make sure everything is working http:#14117765
-The code http:#14117768
-The XML http:#14117814
-The response http:#14117819
XML examples
-Method 1 http:#14120687
-Method 2 http:#14120693
-Notes on the Perl code for generating the XML http:#14120705
-The Perl code for generating the XML http:#14120730
-Method 3 http:#14120742
Getting started
-The first time you access QuickBooks http:#14117661
Real examples (Perl scripts)
-How to use http:#14117719
-Sending the first XML request and reciving the response to make sure everything is working http:#14117765
-The code http:#14117768
-The XML http:#14117814
-The response http:#14117819
XML examples
-Method 1 http:#14120687
-Method 2 http:#14120693
-Notes on the Perl code for generating the XML http:#14120705
-The Perl code for generating the XML http:#14120730
-Method 3 http:#14120742
ASKER
All example code is going to be Perl scripts and if you want to use them I would highly recommend that you download and install the FREE Active Perl Standard Distribution (http://www.activestate.com/Perl.plex?hdr=1).
I would suggest that you create a new folder on your root drive for the samples. I use c:\testing and wherever you see that you should substitute your path.
All Perl scripts should be created in this folder by right clicking -> new -> text document -> copy and past the entire comment (for the script) -> save -> rename with a unique filename ending in .pl.
All XML documents should be saved following the above procedure except the extension should be .xml
I would highly recommend that you run all Perl scripts from the command line while testing so you can see what is happing. To get to the command line go to start -> run -> cmd -> hit enter -> cd\testing (or whatever you named your directory).
Once there you should type filename.pl and hit enter. You should then see text explaining if you succeeded or failed in your attempt and other helpful information.
I would suggest that you create a new folder on your root drive for the samples. I use c:\testing and wherever you see that you should substitute your path.
All Perl scripts should be created in this folder by right clicking -> new -> text document -> copy and past the entire comment (for the script) -> save -> rename with a unique filename ending in .pl.
All XML documents should be saved following the above procedure except the extension should be .xml
I would highly recommend that you run all Perl scripts from the command line while testing so you can see what is happing. To get to the command line go to start -> run -> cmd -> hit enter -> cd\testing (or whatever you named your directory).
Once there you should type filename.pl and hit enter. You should then see text explaining if you succeeded or failed in your attempt and other helpful information.
ASKER
So now that your in the right folder type filename.pl filename.xml and hit enter.
You should see a stream of XML looking something like http:#14117819
You should see a stream of XML looking something like http:#14117819
ASKER
use Win32::OLE::Const 'QBXMLRP2';
Win32::OLE->Option(Warn=>3 );
my $lib=Win32::OLE::Const->Lo ad('QBXMLR P2');
#
# Read the Request file
#
open(IN,$ARGV[0]) or die "Could not open request file: $ARGV[0]: $!\n";
my @req = <IN>;
my $req = join("\n", @req);
close IN;
#
# Choose a company file
#
my $qbfile = ""; # Whatever company file is open
#
# Uncomment the next line and fill in the path to your company file to call a company file by name
#
#$qbfile = 'C:\myCompanyFile.qbw';
#
# Connect to QuickBooks, etc.
#
open OUT, "> output.xml";
our $qbxmlrp = Win32::OLE->new("QBXMLRP2. RequestPro cessor");
$qbxmlrp->OpenConnection(" ","LetMeIn -"); # Change this to something origonal
our $qbticket = $qbxmlrp->BeginSession("$q bfile", 2);
$actualFile = $qbxmlrp->GetCurrentCompan yFileName( $qbticket) ;
unless ($actualFile !~ /\w/) {
print "Companyfile is $actualFile\n";
} else {
print "Couldn't open the company file :-(\n";
exit 0 unless ($actualFile);
}
#
# Send the file to QuickBooks
#
$xmlresponse = $qbxmlrp->ProcessRequest($ qbticket,$ req);
print $xmlresponse;
print OUT $xmlresponse;
#
# Close QuickBooks connection, etc.
#
close OUT;
$qbxmlrp->EndSession($qbti cket);
$qbxmlrp->CloseConnection( );
undef $qbxmlrp;
Win32::OLE->Option(Warn=>3
my $lib=Win32::OLE::Const->Lo
#
# Read the Request file
#
open(IN,$ARGV[0]) or die "Could not open request file: $ARGV[0]: $!\n";
my @req = <IN>;
my $req = join("\n", @req);
close IN;
#
# Choose a company file
#
my $qbfile = ""; # Whatever company file is open
#
# Uncomment the next line and fill in the path to your company file to call a company file by name
#
#$qbfile = 'C:\myCompanyFile.qbw';
#
# Connect to QuickBooks, etc.
#
open OUT, "> output.xml";
our $qbxmlrp = Win32::OLE->new("QBXMLRP2.
$qbxmlrp->OpenConnection("
our $qbticket = $qbxmlrp->BeginSession("$q
$actualFile = $qbxmlrp->GetCurrentCompan
unless ($actualFile !~ /\w/) {
print "Companyfile is $actualFile\n";
} else {
print "Couldn't open the company file :-(\n";
exit 0 unless ($actualFile);
}
#
# Send the file to QuickBooks
#
$xmlresponse = $qbxmlrp->ProcessRequest($
print $xmlresponse;
print OUT $xmlresponse;
#
# Close QuickBooks connection, etc.
#
close OUT;
$qbxmlrp->EndSession($qbti
$qbxmlrp->CloseConnection(
undef $qbxmlrp;
ASKER
<?xml version="1.0"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceQueryRq>
<MaxReturned>1</MaxReturne d>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceQueryRq>
<MaxReturned>1</MaxReturne
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>
ASKER
Companyfile is C:\testing\test.QBW
<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<InvoiceQueryRs statusCode="0" statusSeverity="Info" statusMessage="Status OK">
<InvoiceRet>
<TxnID>4628-1117479112</Tx nID>
<TimeCreated>2005-05-30T14 :51:52-05: 00</TimeCr eated>
<TimeModified>2005-05-30T1 4:51:52-05 :00</TimeM odified>
<EditSequence>1117479112</ EditSequen ce>
<TxnNumber>18</TxnNumber>
<CustomerRef>
<ListID>10000-1056025402</ ListID>
<FullName>EuroTrech</FullN ame>
</CustomerRef>
<ARAccountRef>
<ListID>280000-1057674527< /ListID>
<FullName>Accounts Receivable</FullName>
</ARAccountRef>
<TemplateRef>
<ListID>10000-1056025328</ ListID>
<FullName>Intuit Product Invoice</FullName>
</TemplateRef>
<TxnDate>2005-05-30</TxnDa te>
<RefNumber>1</RefNumber>
<BillAddress>
<Addr1>9 Cambridge Terr.</Addr1>
<Addr2>Suite 3</Addr2>
<City>Cambridge</City>
<State>MA</State>
<PostalCode>02163</PostalC ode>
<Country>USA</Country>
</BillAddress>
<ShipAddress>
<Addr1>9 Cambridge Terr.</Addr1>
<Addr2>Suite 3</Addr2>
<City>Cambridge</City>
<State>MA</State>
<PostalCode>02163</PostalC ode>
<Country>USA</Country>
</ShipAddress>
<IsPending>false</IsPendin g>
<IsFinanceCharge>false</Is FinanceCha rge>
<PONumber>123</PONumber>
<TermsRef>
<ListID>50000-1056025336</ ListID>
<FullName>1% 10 Net 30</FullName>
</TermsRef>
<DueDate>2005-06-29</DueDa te>
<SalesRepRef>
<ListID>10000-1117479070</ ListID>
<FullName>j</FullName>
</SalesRepRef>
<ShipDate>2005-05-30</Ship Date>
<ShipMethodRef>
<ListID>20000-1056025336</ ListID>
<FullName>DHL</FullName>
</ShipMethodRef>
<Subtotal>2.00</Subtotal>
<SalesTaxPercentage>0.00</ SalesTaxPe rcentage>
<SalesTaxTotal>0.00</Sales TaxTotal>
<AppliedAmount>0.00</Appli edAmount>
<BalanceRemaining>2.00</Ba lanceRemai ning>
<IsPaid>false</IsPaid>
<IsToBePrinted>true</IsToB ePrinted>
<SuggestedDiscountAmount>0 .02</Sugge stedDiscou ntAmount>
<SuggestedDiscountDate>200 5-06-09</S uggestedDi scountDate >
</InvoiceRet>
</InvoiceQueryRs>
</QBXMLMsgsRs>
</QBXML>
<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<InvoiceQueryRs statusCode="0" statusSeverity="Info" statusMessage="Status OK">
<InvoiceRet>
<TxnID>4628-1117479112</Tx
<TimeCreated>2005-05-30T14
<TimeModified>2005-05-30T1
<EditSequence>1117479112</
<TxnNumber>18</TxnNumber>
<CustomerRef>
<ListID>10000-1056025402</
<FullName>EuroTrech</FullN
</CustomerRef>
<ARAccountRef>
<ListID>280000-1057674527<
<FullName>Accounts Receivable</FullName>
</ARAccountRef>
<TemplateRef>
<ListID>10000-1056025328</
<FullName>Intuit Product Invoice</FullName>
</TemplateRef>
<TxnDate>2005-05-30</TxnDa
<RefNumber>1</RefNumber>
<BillAddress>
<Addr1>9 Cambridge Terr.</Addr1>
<Addr2>Suite 3</Addr2>
<City>Cambridge</City>
<State>MA</State>
<PostalCode>02163</PostalC
<Country>USA</Country>
</BillAddress>
<ShipAddress>
<Addr1>9 Cambridge Terr.</Addr1>
<Addr2>Suite 3</Addr2>
<City>Cambridge</City>
<State>MA</State>
<PostalCode>02163</PostalC
<Country>USA</Country>
</ShipAddress>
<IsPending>false</IsPendin
<IsFinanceCharge>false</Is
<PONumber>123</PONumber>
<TermsRef>
<ListID>50000-1056025336</
<FullName>1% 10 Net 30</FullName>
</TermsRef>
<DueDate>2005-06-29</DueDa
<SalesRepRef>
<ListID>10000-1117479070</
<FullName>j</FullName>
</SalesRepRef>
<ShipDate>2005-05-30</Ship
<ShipMethodRef>
<ListID>20000-1056025336</
<FullName>DHL</FullName>
</ShipMethodRef>
<Subtotal>2.00</Subtotal>
<SalesTaxPercentage>0.00</
<SalesTaxTotal>0.00</Sales
<AppliedAmount>0.00</Appli
<BalanceRemaining>2.00</Ba
<IsPaid>false</IsPaid>
<IsToBePrinted>true</IsToB
<SuggestedDiscountAmount>0
<SuggestedDiscountDate>200
</InvoiceRet>
</InvoiceQueryRs>
</QBXMLMsgsRs>
</QBXML>
ASKER
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceQueryRq>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceQueryRq>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>
ASKER
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceQueryRq>
<TxnDateRangeFilter>
<FromTxnDate>2005-05-15</F romTxnDate >
<ToTxnDate>2005-05-30</ToT xnDate>
</TxnDateRangeFilter>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceQueryRq>
<TxnDateRangeFilter>
<FromTxnDate>2005-05-15</F
<ToTxnDate>2005-05-30</ToT
</TxnDateRangeFilter>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>
ASKER
This Perl code requires the date calc module wich can be installed by
going to start -> run -> ppm -> enter -> search date-calc -> install 1
It will generate an XML file to be fed into Quickbooks. The number of days is adjustable but the second number should always be greater then the first.
going to start -> run -> ppm -> enter -> search date-calc -> install 1
It will generate an XML file to be fed into Quickbooks. The number of days is adjustable but the second number should always be greater then the first.
ASKER
unlink "querylog.txt";
unlink "thismonth.xml";
open LOG, "> querylog.txt";
use strict;
use Date::Calc qw(Add_Delta_Days Today);
my($DateStr, @Date, $Delta, $date);
$Delta = -15;
@Date = Add_Delta_Days(Today(), $Delta);
$DateStr = sprintf("%d-%0.2d-%0.2d", @Date);
$Delta = 0;
@Date = Add_Delta_Days(Today(), $Delta);
$date = sprintf("%d-%0.2d-%0.2d", @Date);
use Win32::OLE::Const 'QBXMLRP2' || die;
my $qbfile = "//server/d/QuickBooksDATA /a-osborn. qbw";
my $qbxmlrp = Win32::OLE->new("QBXMLRP2. RequestPro cessor");
die unless $qbxmlrp;
$qbxmlrp->OpenConnection(" ","Jonatha nLogin");
my $qbticket = $qbxmlrp->BeginSession("$q bfile", 2);
my $actualFile = $qbxmlrp->GetCurrentCompan yFileName( $qbticket) ;
print "Companyfile is $actualFile\n";
die unless ($actualFile);
my $req = '<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceQueryRq>
<MaxReturned>100000</MaxRe turned>
<TxnDateRangeFilter>
<FromTxnDate>'.$DateStr.'< /FromTxnDa te>
<ToTxnDate>'.$date.'</ToTx nDate>
</TxnDateRangeFilter>
<IncludeLineItems>1</Inclu deLineItem s>
<IncludeLinkedTxns>0</Incl udeLinkedT xns>
<OwnerID>0</OwnerID>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>';
open OUT, ">req.txt";
print OUT $req;
close OUT;
my $xmlresponse = $qbxmlrp->ProcessRequest($ qbticket,$ req);
open TM, "> ThisMonth.xml";
print TM $xmlresponse;
close TM;
$qbxmlrp->EndSession($qbti cket);
$qbxmlrp->CloseConnection( );
print LOG "Success!!!!\n";
unlink "thismonth.xml";
open LOG, "> querylog.txt";
use strict;
use Date::Calc qw(Add_Delta_Days Today);
my($DateStr, @Date, $Delta, $date);
$Delta = -15;
@Date = Add_Delta_Days(Today(), $Delta);
$DateStr = sprintf("%d-%0.2d-%0.2d", @Date);
$Delta = 0;
@Date = Add_Delta_Days(Today(), $Delta);
$date = sprintf("%d-%0.2d-%0.2d", @Date);
use Win32::OLE::Const 'QBXMLRP2' || die;
my $qbfile = "//server/d/QuickBooksDATA
my $qbxmlrp = Win32::OLE->new("QBXMLRP2.
die unless $qbxmlrp;
$qbxmlrp->OpenConnection("
my $qbticket = $qbxmlrp->BeginSession("$q
my $actualFile = $qbxmlrp->GetCurrentCompan
print "Companyfile is $actualFile\n";
die unless ($actualFile);
my $req = '<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceQueryRq>
<MaxReturned>100000</MaxRe
<TxnDateRangeFilter>
<FromTxnDate>'.$DateStr.'<
<ToTxnDate>'.$date.'</ToTx
</TxnDateRangeFilter>
<IncludeLineItems>1</Inclu
<IncludeLinkedTxns>0</Incl
<OwnerID>0</OwnerID>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>';
open OUT, ">req.txt";
print OUT $req;
close OUT;
my $xmlresponse = $qbxmlrp->ProcessRequest($
open TM, "> ThisMonth.xml";
print TM $xmlresponse;
close TM;
$qbxmlrp->EndSession($qbti
$qbxmlrp->CloseConnection(
print LOG "Success!!!!\n";
ASKER
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceQueryRq>
<RefNumber>123</RefNumber>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>
<?qbxml version="2.0"?>
<QBXML>
<QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
<InvoiceQueryRq>
<RefNumber>123</RefNumber>
</InvoiceQueryRq>
</QBXMLMsgsRq>
</QBXML>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If a user is going to be running your program with QuickBooks open I would suggest either “Yes, whenever this QuickBooks company file is open”
Otherwise I would suggest “Yes, always; allow access even if QuickBooks is not running” if your going to be running your program with QuickBooks closed and having it log on by itself.
If your going to have it log on by itself I would suggest you set up a user for it. Company -> users -> Set up users and roles (or whatever your version of QuickBooks has). You might want to set the user at full access and give it a very strong password. Or you can play with the permissions and figure out what the minimum you need to give is. Just remember to log out of the admin account before testing if it works with your permissions ;-).