PivotTable .Refresh from remote Unix database (Postgres)

Posted on 2006-05-25
Last Modified: 2013-12-25
Hello experts.

I have Excel file with Pivot table taking data from local Access database updating PivotCache with
.CommandType = xlCmdSql
.CommandText = sql_query 'stored on one of the Sheets

Now I need to receive data from remote Unix system with Postgres database.

Please advise best way to do that if
-traffic is valuable
-only ftp available (query will be executed on the server and data must be saved somewhere locally on unix system)

Regards, juwdoks.
Question by:juwdoks
    LVL 29

    Expert Comment

    Hmm, how are you planing for your code to be kicked off?  Is it going to be of a button click or something else?

    Author Comment

    I planned it like unix script using specific library to generate .xls which run every ~30 minutes (cron).
    LVL 29

    Expert Comment

    Ok, just to get this straight, you have:

    A UNIX script that kicks off an Excel? process, which will connect to a Postgres database and generate an Excel file from the result.

    Is this correct?


    Author Comment

    Yes, exactly.


    PS: database is Firebird in fact, just correction (not important).
    LVL 29

    Accepted Solution

    Ok, in that case I would suggest you write a VBA procedure which will use ADO to return your data to Excel. Your connection string shoul dlook something like this:

    "Provider='LCPI.IBProvider';Data Source='remotehost:C:\path\to\database.fdb';User ID='username';Password='pwd';Auto Commit=true;"

    the above from ( I have not tried this, since I have not worked with Firebird.

    If you have not worked with ADO before, let me know and I can can give you some sample code.


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    731 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

    15 Experts available now in Live!

    Get 1:1 Help Now