follwing issue,
workbook A with macro's
Jetreport in workbook B
Is there a way to run a marco in file workbook A that will open and run a jetreport in workbook B,and have the data copied to a new worksheet in workbook A
How can I make this working
thanks for any advise
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi Jasmon,
I do have this done, however, I'm not sure if my solution works for you; thought I would offer some assistance.
The approach I have is :
1) A dedicated Jet Reports "server" (a PC) that runs your 'Workbook B' - I have this running in the Windows Task Scheduler. It opens a source Excel file, saved in design mode, and then runs and saves to a separate output folder. The source and output file are on another server's shared drives. This Jet Report takes about 3 minutes to populate.
2) A macro (in your 'Workbook A') that runs VBA code every 10 seconds to "refresh" the data in it. This is handled on another PC.
Now I need to make a couple of points here:
Firstly, the way I have structured this is when Workbook B is open on the Jet Reports server, I do not allow Workbook A to read from it. This is done through VBA. Workbook A can only read from Workbook B if Workbook B is closed, i.e. when Workbook B has the latest saved data. this might seem very strange to you - copying data from a closed Excel file. But trust me, I have this automated and it does work.
Secondly, and it is the painstaking bit: I have VBA code in Workbook A reads back from the closed Workbook B on a cell-by-cell basis. So the VBA is set to look at the value in a cell in a sheet of Workbook B and copy into a cell in Workbook A. Lots of repetitious code and loop handling.
How much VBA do you know? I am willing to freely post my code but I don't know your system, so you will need to adapt your code to suit your environment.
So you know, the reason I developed all of this: I have Workbook A linked to a PowerPoint presentation (with an auto-refresh free add-in found online) on the same PC running Workbook A runs the presentation - this is fed to 2 x 32" LCD screens in the main office. I use a dual graphics card in this PC and send the presentation output to the first monitor port. This port is connected to a further DVI splitter, and each of these DVIs are converted to Cat 5e connections. then at the screens I convert the Cat 5e to DVI.
So in a way, it is a (near) Real Time Information system - something we call thr "RTI".
I also have other sources of Data - exchange rates from a web page on xe.com and our clocking-in system that saves its data into SQL Server but I use a link from that into an Access query and that resultset from the access query is run into an Exce4l sheet, which Workbook A's macros also run on.
It is complicated - I will say that, and some programming experience is required, but I am willing to help!
Stephen -
Jet Reports Historic Posts Thanks for your input,
I found following as wellApplication.Run "JetReports.xlam!JetMenu", "Refresh"
will try this first
Please sign in to leave a comment.