Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
Subscribe for Free Excel tips & more!

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 ...
Highlight and Sort the Top and Bottom Performers in a List in Excel
How to highlight the rows of the top and bottom performers in a list of data. This allows you to quickly identify ...
Put Data into a Worksheet using a Macro in Excel
How to input data into cells in a worksheet from a macro. Once you have data in your macro and you do something wit ...
Get a Table Look with Only the Formatting in Excel
How to get the nice formatting of a table without turning your data into an actual table. Formatting data as a tab ...

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 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.

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'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!


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.

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

Hi All,

I need help with VB script to open crystal reports from excel and pass on the login parameters.
2. from this Crystal report i have to export the data as XL in to the same excel sheet

Is it possible guys. i searched many places without success. Any suggestions or a sample code on this.


I love your vids. they're so smart. I am having problems with separating duplicates. I know that there is a feature in excel where you can separte unique values but the problem is this:

I generate the list from crystal report.

these are taken from online registration of customers. so there are times when customer type in capital letters, commas, periods and so.

now, once I get the crystal report, i need to match that to another report and check only those who have duplicates.



by simply looking at the above, you can see they are duplicates right?

now my problem is if I try to use the function sort unique values, this is not included simply because their fonts dont match.

so, I have to do all 300 lists every day, manually. i check every word that comes out of the report that seems to have duplicates.

it is really a lot of work.

I hope you can help me on this. Is there a help I can get from Excel on my problem?

I would really appreciate your help.



I have recently figured out that creating queries with fields from different tables doesnt allow me to add new records to my queries and forms.

So my question is, I have a list of 715 (5) digit codes on another table and query, without having to create another query with an inner join (which eliminates the possibility to add new records), how can use a filter or expression or vba to have my query or form only show these records for these codes?

SQL Below:]
SELECT [HIM Report].Source, [HIM Report].Provider, [HIM Report].[Provider Name], [HIM Report].[Dept / Spec], [HIM Report].Department, [HIM Report].[Pt Acct], [HIM Report].[MR No], [HIM Report].[Pt Last Name], [HIM Report].DOB, [HIM Report].Started, [HIM Report].DOS, [HIM Report].[Dif Add 1], [HIM Report].[Dif Add 2], [HIM Report].CPT, [HIM Report].Mod1, [HIM Report].Mod2, [HIM Report].Dx, [HIM Report].[CPT Description], [HIM Report].FC, [HIM Report].AssistNo, [HIM Report].AssistName, [HIM Report].AttendNo, [HIM Report].AttendName, [HIM Report].Dx2, [HIM Report].Dx3, [HIM Report].Dx4, [HIM Report].CPT_Type
FROM [HIM Report]
WHERE ((([HIM Report].DOS)>#11/30/2010#) AND (([HIM Report].FC) In ("8","A","B","C","N","S")));

I have a worksheet (see attached). I need help on two items with it.
1. I need to print a report for each line of data (@ users discretion) meaning that if they want to see the data for customer ABC, they can click customer ABC and then print a report for that specific customer


2. The report needs to be rendered in to the following form:

Customer Name, Series Name and Formula Name as headers (as headers)

then lines D<cell number> through S<sell number>
aligned vertically in the report (printed vertical on one sheet)

The object is to print out a report for each customer and put it into a binder.

Can anyone help me with how to do this?

I have a report created in my customer database. It's basically a report with a sub report that looks like a letter that will have customer information in the report and their purchases in the sub report. It has always worked quite well, it looks just like a letter, putting a new page on the end of every record, etc.
However, the company brass want to have the report go out in the future with company letterhead for page one, and plain paper for all other pages for that particular customer. That would be easy enough if all the reports were the same number of pages, but they are not.
Is there a macro/vba, or something that I can use to tell it that every time the report goes to a new customer, pull from tray 1 for the first page and pull from tray 2 on the next page.

I have searched this and multiple forums for an answer to no avail, someone did mention something called neatcd sample from Microsoft Download suite, but I can not find anything on there, I don't even know if that is what I need.

Any suggestions are greatly appreciated!

hi all. i have to print more than 500 reports a day. and each report comes in 3-7 pages and the report comes out very hard to read. and lots of unneeded infromation. i could reduce all the infromation to only one page if the data was in a spread sheet. so is there a way that when i print a report insted of it going to the printer could it go to an excel template first then from there i could print out a final new report with ujested fonts and headers??
any help will be greatly appreciated.

We are having a problem converting an Excel file created from Crystal Reports into an HTML document. The file is exported from Crystal Reports to an Excel file and it looks fine in Excel, but when we save it in HTML from Excel it basically displays everything in one column. The Excel Sheet is a weekly calendar so there are columns for each day and then various numbers of rows. Any ideas? Thanks!!