Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & 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.


View Answers     

Similar Excel Tutorials

Remove Gridlines in Excel 2007 and Later
In Excel 2007 and later you can quickly remove the gridlines that appear within the Excel worksheet. This allows yo ...
Sort Data Alphabetically or Numerically in Excel 2007 and Later
This Excel tip shows you how to Sort Data Alphabetically and Numerically in Excel 2007. This is a great feature to ...
Quickly Switch the Columns and Rows of a Data Set in Excel
How to switch a data set in Excel so that the columns become rows and the rows become columns. This will save you ...
Hide Data Within a Worksheet in Excel
In Excel you can actually hide data that is stored within a worksheet. This allows you to show data that is useful ...

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
Print Selected Worksheets in Excel
- This free excel macro will print all selected worksheets in Excel. If there are no other worksheets selected the active
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the

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.

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?

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.

Hi Everyone
I am not sure if there is where to ask, but here I go.
I am using software that uses Microsoft SQL for its data base, and it has a bunch of reports that use excel to display data. Although there are quite a few reports, I would like some custom reports to track specific data in the data base. The data is being tracked, and it does show up sporadically in other reports, but I want to consolidate it into one report for a specific task. At another company where they were using AccPac and SQL, when I asked for a custom report, it was made for me fairly quickly. My questions are these:
Could this be done.
What would it take.
How would i find someone to do this.
I have looked online quite a bit, but I am not really finding what I am looking for. I don't know if I am searching it properly, but I am not finding any info on what it would take, or who can do it. Any help in this would be greatly appreciated.

Hi, I'm new to this forum. Relatively good at Excel, but am always game to learn new formulas/VBA code.

I have a workbook where I have a button to automatically create a new worksheet. (ex. Report 1, Report 2, Report 3, etc...). I would like to have a Summary worksheet that automatically grabs the data from the existing and newly created worksheets (Report 1, Report 3, Report 3...etc) On the Summary worksheet, I would like to reference the exact same cell range from Report 1, Report 2, Report 3 etc...

However, I am unable to increment the values of the referenced worksheet on my Summary Worksheet. I'm not sure how to change the numerical value inside the single-quotes which refer to the worksheet Report 1, Report 2, etc. (ie 'Report 1'!A$89 ...) I would like to increment the 1 to a 2 and then to a 3 as each new worksheet is created and the Summary puts the data in the next available line, resulting in a Summary Report of Report 1 to Report N.

Hopefully that makes sense. If anyone has an idea, I would appreciate some guidance.

Thanks In Advance