Email:      Pass:    Pass?
Close Window   
Subscribe for Email Updates!
Excel tips, help, and more!


Free Excel Forum

Vba To Pull Data From Accpac Or Crystal Report

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi There excel experts

I would like if someone could help me and explain to me how i can run a VBA code that will pull data from a report i created in Crystal report which pulls data from Accpac.

What i do is i print this report and type in the data automaticaly in excel. i need a VBA code that will do this form me, it will minimize the chances of human error.

Please could anyone help me with this.


Similar Excel Video Tutorials

Helpful Excel Macros

Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Print Preview Display for Specific Worksheets in Excel
- This free Excel macro allows you to display the print preview mode or window in Excel for a specified Excel worksheet.
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
- This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web

Similar Topics

I have to create a report for my xls file .. how can i make it.? is there any Crystal Report ADD-IN for Excel..?
and how do i connect my Xls file to Crystal report.....
I have to prepare inspection report, Everytime i have to copy/paste the details and then print it by making most page settings, how do i avoid this..
i think Crystal Reports with Excel can do it.....?


I am hoping someone can give me some insight on this problem. I am using
Excel 2002 and ACCPAC 6.1.

When I generate a report based on data in an ACCPAC report the numbers are
sometimes out by a few pennies.

My spreadsheet formulas are to calculate tax =E9*1.15 and =SUM(G8:G36).

I have tried the prescision as displayed but it still wont work properly!!!!

I am sure the problem is in the differences in which ACCPAc and Excel
calculate formulas but I don't know where to go from there.

Any feedback is appreciated.

Many thanks!


I was hoping somebody may be able to post a simple example of how to open a Crystal Report from excel vba. I also need to pass some parameters to the report.

For further infomation, my scenario is that I have a report created in Crystal XI that reads from an Oracle db, I want use excel to store a list of orgainsations and their email address. I then wish to automate an email process that sends the appropriate report to the correct location.

many thanks in advance

I have had an exhaustive weekend trying to research this issue.

I have an Access 2003 DB that I actually posted on here about in January. I am currently merging the data to Word because of the Access Report Details limitaions. Anyway, I created the report in Crystal using CR XI Developer edition. I can preview the report, the data connection works good on the report side.

Now, here is where it gets sticky. I have a command button on the form that is set to open the Crystal viewer using the 'Call Shell' comand. This opens the viewer and is blank.

1. I need to open the viewer and have the report opened at the same time as the viewer.
2. I need to make sure that the report only shows the current record selected. Right now the report was created to view all records. I believe the SQL in the report will need to be modified to look at the current record ID.

The report is at this path c:\JobTrack\Subcont.rpt
The table is 'tblSubCont'

Can someone please help me

I have an excel spreadsheet that I've used to create a crystal report. The excel file has multiple employee id's on it. The crystal report has a parameter that uses the employee id to only show that employee's information. I'm trying to set up a macro to loop through the employee ID column, pass one employee ID to the crystal report, export the crystal report as a PDF and save it with the employee ID in the file name.

is it possible to use a macro for this purpose? In my test file, I only have 3 employee ID's but will have over 7000 if I'm able to figure it out.

I've search this board and many others to try and find some code to help me out but am at a loss. Thanks in advance to all who might be able to shed some light on this.

How Excel can be used for generating End user customisable reports. Data needs to be fetched from oracle / SQL server database based on some query and reports needs to be generated in Excel.

I want to design a solution for end user (who has knowledge of Excel & database query writing) for Report generation as per his changing requirement, so that he has no dependancy on development team for day today modification in report requirements.

I designed this type of application using C# & Crystal report earlier, which requires three things :
1. Crystal report template having database query
2. filter criteria (on which data will be read from database).
3. Dynamic User control (e.g. text box, drop down etc) generation component as per filter criteria Control required (defined in database table by end user).

If End user is acquainted with Crystal report designing then he can design his own crystal report template along with database query embedded in crystal report only. My software generates dynamic user controls as per defined in database for that report template. These filter criteria are passed to query defined in Crystal report so that desired report can be generated as per his filter criteria. This helped him in catering day today reporting related changing requirements.

Now I want to design similar solution using Excel i.e. replacing Crystal report template with Excel template.
My initial proposal is like this that I will replace crystal report with excel template having few fixed parameter & few variable report parameters (which will come from database as per query), rest I will use from my earlier solution.

I don't know
# how to define report parameters in Excel ?
# how to generate multi page report (when database returns dataset of more than one record) ?
# how to paginate report like crystal report does when results are to be displayed on multiple page ?
# how to define Report header & footer area in Excel to be displayed on multiple page report ?

I know that these things can be done using macro programing(defined in that Excel report template) but that will not be easy for end user, I want to have a simple generalised solution which I can implement once in my c# program so that end user (having little excel knowledge) can design / modify report as per his need without having much trouble of programming, like I did in my previous solution (c# & Crystal report).

Thanks in advance for sparing yours valuable time, support and guidence.

Gopal Parikh.

Hi All,

Every day I should get into our intranet application and select some values and generate a report. The report will be displayed in web using crystal reports, then I should export the crystal report to excel and save in a location.

Every day I should keep doing this process and I am feeling bored ... is there a way we can automate this process using XL macro.

Please help


I have a data file from a Crystal report and can not SORT; using EXCEL 2007; although the file opens fine the icon for Sorting is grayed out; please advise

I have a data file from a Crystal report and can not SORT; using EXCEL 2007; although the file opens fine in EXCEL the icon for Sorting is grayed out; please advise

Currently, from Excel, I can update a Showcase Query and have the data mapped to a defined range in Excel with an Add-In.
Is it possible to do this with Crystal Reports?
I know that I can export the Crystal Report into Excel, however, that is not what I need.
I have other tabs in the spreadsheet accessing the data that has been brought in.
I have been unable to locate an Add-In for Crystal Reports.
Any help would be Greatly Appreciated.
Thank You!

I know there might be some Crystal users here as thought I would post this question.

I would like an "IF,THEN" statement to come over from Crystal to excel when I export my report. Anyone know how to get the formula to come over and actually work.

I put the following text box on the line in the Crystal report.

But when it is exported to excel, the field in excel simply says the formula, it doesnt actually calculate the formula. Plus I would like it to change the field number each row. ie)

Any thoughts on how I can do this??

Hi everyone,

I'm running a report made by Crystal Report that I have to put on an Excel Spreedsheet. Unfortunately, my Crystal report is producing a lot of blank rows and column. I'm trying to fin a way to delete those line and columns. I've been looking on the site but couldn't find the proper way to do it.

Thanks for your help


I have a form with various customer data. The data in the form is autofilled based on a customer number entered. I am trying to make a report that will pull in some of the customer specifics but I'm getting a "#Name?" error.

In the control source of my report I'm using is:

Where [firstname] is the name of my text box in the form.

does anybody know how to pull into my report the first name of the customer from my form?? Please help!!

We have a formatted report with sums for data. Currently we run the data from Crystal, download it to excel and then cut and paste it into the formatted report. Is there a way to select a range of cells and delete hardcoded data only, leaving the formatting?

Thanks in advance for your help!

I'm using Crystal Report bundled in .Net 2003 to open up Access database. But there's a problem whereby whenever i closes the form that calls crystal reports. There database connection will not be closed. The .ldb file will still exists which will hinder me from doing other activities as it will prompt me that the database was opened exclusively by Xxxx.

Anyways to close .ldb file without closing the entire application?

Howdy to all..

I have a question regarding creating a master form that will pull data from other forms.
I created a report that we key daily production information into on a shared network drive. The report has to be copied over and the date changed along with tje starting numbers for each day

I would like to create a template or master form that will pull numbers from that report and keep a running total.

What would I need to do to achieve this ?

Thanks in advance


I've written a report in crystal XI which has been published into Business Objects and I would like to be able to run this dreport directly from Excel.

Is this possible? I'm quite new to VBA so I'm not sure where to start.

Many thanks


I am exporting a crystal clear report in excel format.
In the report for some of the cells,which have values with newline character in between,a square box is being displayed.
But this is happening for some clients,not for all.

I am using MS Office-2003.

Can anybody help me out on this ASAP?


I have a normal table of data A1 through M50.
Row 1 contains column headings for the data.
Column A contains report names.

I want the user to enter a report name someplace (a particular cell or a data request menu) and have the related data (say A15:M15 sheet1) copied to cells A1:M1 on another sheet (sheet2) in the same workbook.

For example, by entering "Report C" in a given cell, I want the report c data (i.e., Report C, 56, 127, 20, 44, 8) to be placed in A1:M1 of sheet2.

Name Area Vol Code Section Pg
Report A 54 125 18 42 6
Report B 55 126 19 43 7
Report C 56 127 20 44 8
Report D 57 128 21 45 9
Report E 58 129 22 46 10
Report F 59 130 23 47 11
Report G 60 131 24 48 12

Is there a relatively simple way to do this?


I've looked around here, and on other sites, and haven't seen this question yet, so I'm hoping someone knows the answer to this (if there even is an answer!).

I'm creating pricelists to be printed and handed our to our customer base using excel. Excel is reading the information from an internal worksheet linked to an ACCPAC SQL database. There are actually 3 worksheets, each linked to a different pricelist in the accpac database. The issue I'm having is that when I update the data on these worksheets (to pull in new prices I've entered into ACCPAC, or to add new items to the list in the pricelist) the fields on my first worksheet do not follow the data as it moves on the 3 linked worksheets. Is there any way to force Excel to follow the data's movement when I'm doing a database update? As it stands now, when I add an item, I have to re-visit my first worksheet and re-do all the formulas (or most of them at least) to point to where the data has now moved (usually one or two rows up or down) on the other worksheets.

Thanks in advance for any help you can give me!

Can we synchronize data in Accpac with Excel? The heading of the data is same in Accpac as well as in Excel Sheets.




I'm triying to pull data from another excel file based on what a user inputs.

For example:

I have montly excel files (workbooks) with the following names:
Jan Report.xls
Feb Report.xls
Mar Report.xls
Dec Report.xls

In another excel workbook, I want to pull data from a certain month depending what the user enters inputs. Take for example, if cell A1 is the input cell ("Jan", "Feb", etc.) and Cell A2 has the formula like "=[Jan Report]'!Sheet1$A$1"...

Obviously, Cell A2 will on pull data from Jan Report. But my question is how do I change the formula in A2 to pull any month depending on what is in cell A1? Someting like "=['A1' Report]'!Sheet1$A$1"...

Hope that makes sense... TIA!

#ERROR is what I keep getting when I try to pull the SubTotal field from the SubReport onto a text box of the main Report.

I use the ControlSource (...) to find the field from the SubReport. The Formatting is consistent from both the Report and SubReport. I've followed the exact directions the Help function offers for this (but it is only for Forms/SubForms).

What I'm trying to achieve (fields are very different for both queries):
Query 1: Actuals data -----> Report
Query 2: Forecast data -----> Sub Report
One page per Company with Company ID being the common field

1) Details of Actuals with the subtotal in the Report Header (done successfully)
2) Details of Forecast with the subtotal in the SubReport (done successfully)
3) The Actuals Subtotal in the Report Footer (done successfully)
4) The Forecast Subtotal in the Report Footer (#ERROR)
5) Forecast - Actual in the Report Footer (The next step)

How do I pull the data from the SubReport to the Report? Is there code involved? Is #ERROR displaying because it can not find the matching field (which exists for the report/subreport) for the Subreport in the main Report?

Any and all input would be appreciated.


I wanted to to create a small macro that would open a crystal reports file similar to clicking on the file in windows explorer. The report's path and name would be in a cell for example "C:\Reports\sales.rpt". You click a button and that file open's the report in Crystal, ready for me to work on.

I have data that was too large for one sheet so I have spread it over a number of workbooks. I need to pull the data from all workbooks into a report I have built in a sheet at the beginning. I can pull data from one book, but do not know how to pull from all books. See example:

Report/AL01/AL02/AL03/AL04 and so on. I want to put a number into my report and have it search all books to find it and pull the data into the report. Thanks for your help.