Solved

Extended information on the newsletter article

Posted on 2004-10-29
1,301 Views
Last Modified: 2012-05-05
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
0
Question by:turn123
    12 Comments
     
    LVL 11

    Author Comment

    by:turn123
    The first time you try to access the QuickBooks Company file you will need to be logged in as the administrator.  You will be presented with a popup screen asking you to make a choice.

    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 ;-).
    0
     
    LVL 11

    Author Comment

    by:turn123
    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.
    0
     
    LVL 11

    Author Comment

    by:turn123
    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
    0
     
    LVL 11

    Author Comment

    by:turn123
    use Win32::OLE::Const 'QBXMLRP2';
    Win32::OLE->Option(Warn=>3);
    my $lib=Win32::OLE::Const->Load('QBXMLRP2');

    #
    # 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.RequestProcessor");
    $qbxmlrp->OpenConnection("","LetMeIn-"); # Change this to something origonal
    our $qbticket = $qbxmlrp->BeginSession("$qbfile", 2);
    $actualFile = $qbxmlrp->GetCurrentCompanyFileName($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($qbticket);
    $qbxmlrp->CloseConnection();
    undef $qbxmlrp;
    0
     
    LVL 11

    Author Comment

    by:turn123
    <?xml version="1.0"?>
     <?qbxml version="2.0"?>
      <QBXML>
       <QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
        <InvoiceQueryRq>
         <MaxReturned>1</MaxReturned>
        </InvoiceQueryRq>
       </QBXMLMsgsRq>
      </QBXML>
    0
     
    LVL 11

    Author Comment

    by:turn123
    Companyfile is C:\testing\test.QBW
    <?xml version="1.0" ?>
    <QBXML>
    <QBXMLMsgsRs>
    <InvoiceQueryRs statusCode="0" statusSeverity="Info" statusMessage="Status OK">
    <InvoiceRet>
    <TxnID>4628-1117479112</TxnID>
    <TimeCreated>2005-05-30T14:51:52-05:00</TimeCreated>
    <TimeModified>2005-05-30T14:51:52-05:00</TimeModified>
    <EditSequence>1117479112</EditSequence>
    <TxnNumber>18</TxnNumber>
    <CustomerRef>
    <ListID>10000-1056025402</ListID>
    <FullName>EuroTrech</FullName>
    </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</TxnDate>
    <RefNumber>1</RefNumber>
    <BillAddress>
    <Addr1>9 Cambridge Terr.</Addr1>
    <Addr2>Suite 3</Addr2>
    <City>Cambridge</City>
    <State>MA</State>
    <PostalCode>02163</PostalCode>
    <Country>USA</Country>
    </BillAddress>
    <ShipAddress>
    <Addr1>9 Cambridge Terr.</Addr1>
    <Addr2>Suite 3</Addr2>
    <City>Cambridge</City>
    <State>MA</State>
    <PostalCode>02163</PostalCode>
    <Country>USA</Country>
    </ShipAddress>
    <IsPending>false</IsPending>
    <IsFinanceCharge>false</IsFinanceCharge>
    <PONumber>123</PONumber>
    <TermsRef>
    <ListID>50000-1056025336</ListID>
    <FullName>1% 10 Net 30</FullName>
    </TermsRef>
    <DueDate>2005-06-29</DueDate>
    <SalesRepRef>
    <ListID>10000-1117479070</ListID>
    <FullName>j</FullName>
    </SalesRepRef>
    <ShipDate>2005-05-30</ShipDate>
    <ShipMethodRef>
    <ListID>20000-1056025336</ListID>
    <FullName>DHL</FullName>
    </ShipMethodRef>
    <Subtotal>2.00</Subtotal>
    <SalesTaxPercentage>0.00</SalesTaxPercentage>
    <SalesTaxTotal>0.00</SalesTaxTotal>
    <AppliedAmount>0.00</AppliedAmount>
    <BalanceRemaining>2.00</BalanceRemaining>
    <IsPaid>false</IsPaid>
    <IsToBePrinted>true</IsToBePrinted>
    <SuggestedDiscountAmount>0.02</SuggestedDiscountAmount>
    <SuggestedDiscountDate>2005-06-09</SuggestedDiscountDate>
    </InvoiceRet>
    </InvoiceQueryRs>
    </QBXMLMsgsRs>
    </QBXML>
    0
     
    LVL 11

    Author Comment

    by:turn123
    <?xml version="1.0" encoding="ISO-8859-1"?>
          <?qbxml version="2.0"?>
          <QBXML>
          <QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
                <InvoiceQueryRq>
                </InvoiceQueryRq>
          </QBXMLMsgsRq>
    </QBXML>
    0
     
    LVL 11

    Author Comment

    by:turn123
    <?xml version="1.0" encoding="ISO-8859-1"?>
      <?qbxml version="2.0"?>
        <QBXML>
          <QBXMLMsgsRq responseData="includeAll" onError="continueOnError">
            <InvoiceQueryRq>
              <TxnDateRangeFilter>
                <FromTxnDate>2005-05-15</FromTxnDate>
                <ToTxnDate>2005-05-30</ToTxnDate>
              </TxnDateRangeFilter>
            </InvoiceQueryRq>
          </QBXMLMsgsRq>
        </QBXML>
    0
     
    LVL 11

    Author Comment

    by:turn123
    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.
    0
     
    LVL 11

    Author Comment

    by:turn123
    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.RequestProcessor");
    die unless $qbxmlrp;
    $qbxmlrp->OpenConnection("","JonathanLogin");
    my $qbticket = $qbxmlrp->BeginSession("$qbfile", 2);
    my $actualFile = $qbxmlrp->GetCurrentCompanyFileName($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</MaxReturned>
    <TxnDateRangeFilter>
    <FromTxnDate>'.$DateStr.'</FromTxnDate>
    <ToTxnDate>'.$date.'</ToTxnDate>
    </TxnDateRangeFilter>
    <IncludeLineItems>1</IncludeLineItems>
    <IncludeLinkedTxns>0</IncludeLinkedTxns>
    <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($qbticket);
    $qbxmlrp->CloseConnection();
    print LOG "Success!!!!\n";
    0
     
    LVL 11

    Author Comment

    by:turn123
    <?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>
    0
     
    LVL 11

    Accepted Solution

    by:
    Moved from Test to Quickbooks and closed.

    turn123,
    Page Editor -- Productivity Applications
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    The Complete Ruby on Rails Developer Course

    Ruby on Rails is one of the most popular web development frameworks, and a useful tool used by both startups and more established companies to build strong graphic user interfaces, and responsive websites and apps.

    Suggested Solutions

    Title # Comments Views Activity
    Migrate from 12.0 to 15.0 2 234
    QuickBooks Premier 2015 Errors 4 124
    Quickbooks Unit of Measure Question 4 52
    Quickbooks Issue 5 22
    By Linda Saltz Customized item descriptions from bills DO import into a customer invoice!   This is a wonderful tip for companies that want to use generic items like Hardware, Software, Mileage, Travel in their item list which helps keep the i…
    You may need to view past transactions from previous QuickBooks files or other QuickBooks company files when:       1. you are working in another QuickBooks file       2. QuickBooks is closed QuickBooks users who would benefit from this artic…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    877 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now