[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

PivotTable .Refresh from remote Unix database (Postgres)

Hello experts.

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

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.
0
juwdoks
Asked:
juwdoks
  • 3
  • 2
1 Solution
 
leonstrykerCommented:
Hmm, how are you planing for your code to be kicked off?  Is it going to be of a button click or something else?
0
 
juwdoksAuthor Commented:
I planned it like unix script using specific library to generate .xls which run every ~30 minutes (cron).
0
 
leonstrykerCommented:
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?

Leon
0
 
juwdoksAuthor Commented:
Yes, exactly.

juwdoks

PS: database is Firebird in fact, just correction (not important).
0
 
leonstrykerCommented:
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 (http://www.mayukhbose.com/python/ado/ado-connection.php) 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.

Leon
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now