Initiating and Executing Excel Code from Access


I am in the process of determining whether it is feasible to move upstream 
into Access, a process currently run in Excel. 

1. An Access program extracts data from a data warehouse, massages the data, 
and produces Excel spreadsheet(s) to be used as input to the Excel "report 
generation" spreadsheet in #2. 
2. An Excel "report generation" spreadsheet has been created (not by me, so 
I am inheriting this), which has a menu on it, from which, the user may 
select up to 8 different Pivot Table reports to generate. When the user 
selects a report to run, a subroutine is run, which 
a. presents the user with a dialog box to select which Excel spreadsheet 
created in #1 above to use as input. 
b. Reads the column headings on the input data to create a menu item for 
each field in the input sheet, such that, if the user selects that field, it 
becomes part of the pivot table. 
c. Prompts the user as to where to save the report, suggesting a file 
location and file name. 

What I would like to do is: 
1. Change the Access program to ask the user which report(s) to create, and 
the data source file name (Excel input file). 
2. Based on the user selection in #1, have Access call Excel to run the 
appropriate Excel VBA code to generate the report(s) selected by the user in 

Can anybody shed any light on whether this can be done, and point me in the 
right direction for how to accomplish this? 

My thought is to devise a method of passing the Excel "report generator" 
spreadsheet the parameters necessary to execute the approporiate section of 
code and where to store the completed report(s). 

Sample code follows, which is how one of the reports is generated currently 
through user selection: 

Sub DataFile_FC_DOM_Pending() 
Dim strInputFile, strOutputFile As String 

Title = "FC Dom - Pending Cases" 
ReportFileType = "FC_DOM_Pending_" 
ReportDateType = "snapshot" 
DateCell = "B12" 
PrimaryLabel = "Family Court Domestic: Pending Cases" 'this is the label 
above pivot table 
PivotLabelRange = "B6:B7" 
WebLabelRange = "B7" 

' Use local paths during testing 
LinkSheetsPath = "Y:\Reports\LinksSheet\DomCaseMgt_Links.xls" 

LinkSheetName = "Dom1" 'worksheet name in the links workbook 
LinkCellLocation = "A3" 

' Convert Text File To Excel 
strInputFile = "v:\rdm92ax.txt" 
strOutputFile = "Y:\PrepData\FC_DOM_Pending_.xls" 

Call ConvertToExcel(strInputFile, strOutputFile) 
If strOutputFile = "" Then 
Exit Sub 
End If 
' *** 

Call Open_Data_File(ReportFileType, Title, "y:\prepdata\") 

If DataFileName = False Then Exit Sub 
Call Create_Report(ReportFileType, Title) 
Call FieldReportShowAll("Rundate", xlRowField) 
Call FieldReportShowAll("Court", xlRowField) 
Call FieldReportShowAll("Type", xlRowField) 
Call FieldReportShowAll("Track", xlRowField) 
Call FieldReportShowAll("Over?", xlColumnField) 
ReportDate = Year(Range(DateCell).Value) & "-" & _ 
Month(Range(DateCell).Value) & "-" & _ 
ReportFileName = ReportFileType & ReportDate 
Call Save_Report(PrimaryLabel, ReportFileName, ReportDate, _ 
ReportDateType, LinkSheetName, LinkCellLocation) 

' Delete .xls file used as input which is called "output" but is really 
converted from the .txt file 
Kill strOutputFile 

End Sub 

3/12/2008 2:24:02 PM
access.formscoding 7493 articles. 0 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 20


Similar Artilces:

Having problems accessing external network SMTP servers on port 25
Hi Guys, Just installed Windows Small Business Server 2008 Standard, with Service Pack 2. Now trying to get Exchange Server 2007 to send external emails to my ISPs smart host. The SBS machine has IP:, I have a ADSL router at IP: and a test client running XP Pro at IP: I have temporarily disabled the Firewall in SBS 2008. So; if I do; telnet 25 from the XP machine I get this; 220 ESMTP [relay05] telnet 25 from the XP machine I get this; 220 FACTORYSRV.nsl.local Microsoft ESMTP MAIL Service re...

need to access outlook
i cant get in to my account when i open my outlook. Hi Steve, please tell us more about that error. Have you installed anything? Have you changed anything? -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. "Steve Bersch" <> schrieb im Newsbeitrag news:3b5a01c4727c$fdeceb10$a301280a@phx.gbl... > i cant get in to my account when i open my outlook. ...

Excel 2003 Problem
I had office 2003 installed in a system running XP Professional. I login to the domain with the domain user name and password and was able to work with my office. The other day i changed my domain machine but with the same domain name. But when i log in back with the domain user name i have problems with the the office suite. Outlook doesn't open at all. Office and excel asks the name when u open the document or excel sheet every time. I couldnt find a soultion. I even reinstalled the office suite, now i am able to open the outlook , word and excel, but each time a small window ope...

Getting initials from Given Names
I have loaded an Excel spreadsheet from a database using a query and have a column which lists Given Names. I have another column for initials and am wondering if these can be created automatically from the Names column. Any ideas? Thanks Hi Peter, It depends on how they are setup and how you want them displayed. John Smith: =LEFT(A1,1)&" "&MID(A1,FIND(" ",A1)+1,1) Returns J S Smith,John: =LEFT(A1,1)&" "&MID(A1,FIND(",",A1)+1,1) Returns S J Smith,John: =MID(A1,FIND(",",A1)+1,1)&LEFT(A1,1) Returns JS Biff ...

downloading excel and word
i would like to download microsoft excel and word to my pc, and i search download sites and i could not find any? i would like to download for free. anyone can help me on this thank you Sorry mate, You can't have them for free, they are commercial products sold at a price (or noone would have heard of Bill Gates!). Even if you managed to get them for free, it would be illegal!!! There's other stuff out there you can get for free, like Open Office, that will do most of what Excel and Word do, at least for the average user (and with file compatibility). Nikos "yaman" <...

Stock prices in Excel?
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Hi Folks, Can I set up excel so that it downloads and displays the current stock price for a certain stock every time I open the document or refresh? I want to use my own workbook, and I'm looking for a free solution, although I might consider an inexpensive add-on. Thanks!! Yes, Excel will do that. Pop over to the Excel forum and they will explain how: On 30/03/08 6:22 AM, in article ee969b4.-1@webcrossing.caR9absDaxw, "graybell@officeformac...

Change Needed
I have a neat VB code that I use in Excel. This code imports data from a text file into columns in an excel spreadsheet. The excel file has two worksheets, one is to input the .txt filename with a button for the VB code to do it's magic and convert everything I have in the text file, which usually has the same layout (in each txt file) in terms of what data I want columnized in excel. The code goes as follows.... Sub ConvertFile() Dim LCStart As Long Dim IQStart As Long Dim Lat1Start As Long Dim Lon1Start As Long Dim Lat2Start As Long Dim Lon2Start As Long Dim NbmesStart As Long Dim...

NDR Status Code 6.4.1
Hi...I'm new to this group, but we are running up against a problem with a front-end exchange server that has just been implemented. Since this server has been installed, the error log is filling up with NDR 6.4.1 error messages and those select email are NOT getting delivered properly even though other email comes through fine. Best we can tell in house, this error code is indicating that we might not have a setting correct on the front-end exchange server to allow 8-bit MIME types to get through. Are there any suggestions on what might be occurring. Are there default settings ...

Logarithmic graphs in Excel
Hello! I have some Excel data that lists the United States' Gross Domestic Product for each year from 1890 to 2000. The data looks like this: 1890 260.42 1891 271.87 1892 298.51 .... 1998 8495.65 1999 8848.225 2000 9156.6 I wanted to turn this data into a groovy looking line graph, so I tried using Excel's built-in Chart Wizard, but the resulting graph looks WAY too much like an exponential curve (not to mention the fact that it really doesn't all that good), so I want to turn it into a logarithmic graph. How can I accomplish this? Double click the Y axis, and on the Sca...

Help for Excel Chart problem
Hi I have a little problem. I have to prepare audience profile for some TV Channels using dimensions age, sex, monthly income and education. I want to combine metric - age and income in one chart and non metric - sex and education in other. So I dont know which chart to use. I think the most appropriate is the bubble chart (more I have the penetration as a bubble size). But I cannot think how to dispose the dimensions and data. Example: Channel 1 Age 12-18 30 % 19-34 20% 35-64 35% 64+ 15% Sex Male - 48% Female - 52% So I need to positioning this channel and compare with ot...

Printing Doubel-sided in Excel
Has anyone experienced issues attempting to print double-sided in Excel using a Canon IR C3220 (PS3 driver) or is this a feature not supported by Excel. Hi Anthony This is a printer setting. If your printer have this option it must work Another option is to do it with code -- Regards Ron de Bruin "Anthony Spartalis" <> wrote in message news:ORn5khavFHA.2556@TK2MSFTNGP15.phx.gbl... > Has anyone experienced issues attempting to print double-sided in Excel using a Canon IR ...

Big problem with Excel 97
Excel 97 on Windows NT 4.0 SP6 After I create a new file and save it, it takes a very long time (about 30 seconds) to close the file. I am working with lots of Excel files and it's intolerable. Please help me with this. Thanks. ...

Total in every Page in Print-out in Excel
Is there a possibility that automatic page total be included in th foote -- Message posted from Hi This is a builtin feature in Excel since...some time! See View, Header and Footer You should come up with &[Pages] in your footer HTH Cordially Pascal "elmergev >" <<> a �crit dans le message de > Is there a possibility that automatic page total be included in the > footer > > > --- > Message posted from > ...

Excel help #3
Hi, Two questions. First is, when performing a Pivot Table, is there a way of changing the source so that when you produce a pivot table, the months are in order. And, when you concatenate and join a surname and first name together, how do you then separate them ? Thanks "Gary Newman" <> wrote in message news:09f501c39882$44084930$a401280a@phx.gbl... > Hi, > > Two questions. > First is, when performing a Pivot Table, is there a way of > changing the source so that when you produce a pivot > table, the months are in order. > &g...

Is there a way to add more reason codes
Is there a way to add more reason codes? Yes, in Manager look under the Database menu and there is a Reason Codes function there. -- Jason Hunt Advanced Computer Systems ...

Launch jscript code when clicking on view to see all Cases
Hello I realise that this is totally going to be an unsupported customisation, but I was wondering whether anyone knew how to change the inbuilt grid form for Cases so that I could call some jscript that altered the look of the default grid used for viewing cases. Any advice or pointers appreciated. Cheers Saira Hi, Saira. Have not had a chance to try it myself, but in theory this should work. Here is the general idea: Create some *.HTM page and put it on CRM Web Server. On the page, put iframe 100% x 100%, which has it's source set to actual Cases grid. Now, you'll need t...

PowerPoint 2007 with EXCEL Chart
Good afternoon, Thanks in advance. I hope I am in the correct group since this is both an Excel and PPT question - I created a Spreadsheet with 10 tabs, on each tab there is one fairly simple chart with a secondary axis that show a % for one column. I have the chart with format looking correct in Excel, and copy / paste to a corresponding slide in ppt. Sometime, not all of the time the format changes especially on the secondary axis, it goes to a decimal value. A lot of the time the data labels will be missing from the columns as well. Not sure what I am missing, USUALLY ...

MYOB to Excel
Is there an Excel expert out there that also knows MYOB? I am trying to produce CATEGORY reports in the MYOB program that will show the previous year's details alongside the Current Year's Selected Period and Year To Date columns. ie The column headings should be: Account# Account Name Selected Period Year To Date Last Year's Selected Period Last Year's Year To Date. MYOB does export to Excel but MYOB can't produce such a report and I wonder if someone has already produced a workbook that would manipulate the exported data to produce a report in the manner descri...

MS Access and Flash
Hi, I know that Access does not like animation. I want to put some animation on my Access form using flash. Does access support flash object? SF "SF" <> wrote: >I know that Access does not like animation. I want to put some animation on >my Access form using flash. Does access support flash object? Why would you want to do this? Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www....

Benefits of Using Excel for Accounting
Hello � What are the benefits of using Excel for accounting purpose? What ar the remarkable features will help me in accounting? Thank you, Kat -- ilovetak ----------------------------------------------------------------------- ilovetako's Profile: View this thread: You may be better served by buying software specifically intended for accounting. On Tue, 27 Sep 2005 16:08:36 -0500, ilovetako <> wr...

Excel Postcode list to map
Hi, Sorry if you've already had this on the "misc" group but no reply:- I have a list of UK Postcodes in a Spread Sheet in Column A, and a List of Date/Times in Column B. I'd like to plot these on a map so that lines are drawn to show show the route in time order. Does anyone know of an excel addon, programme or website that will allow me to do this from a worksheet? -- Rich "Rich" <> wrote in message news:0HhHo.35263$sK1.32443@newsfe21.i...

accessing files in subfolders
Do I need to use AfxGetAppName() when files in different, or even the same, project subfolders are accessing each other? E.g., in myDirectory\ogg\ogg.h, #include <ogg/os_types.h> does not work; but #include <myDirectory/ogg/ogg/os_types.h> seems to work fine If you are doing complex includes like this, you need to rethink what you are doing. Complex directory structures like this break horribly when the project moves around. You should not be using <> for user-defined includes; use "". Mostly these problems stem from the horrible design of how #includ...

Excel 2002
When I open a one of my files it opens two files of the same name but with a different number on the end. E.G. the file name is "Past Players:1" and the other file name is Past Player:2, the original name is Past Players without the number. At the moment I have downloaded the trial of 2007 where the problem has been fixed, but at the end of the trial I don't want to pay for 2007 as I am a pensioner and can't afford the upgrade. You have somehow enable a New Window view. In XL 2003 this if done with Window | New Window In XL2007 it is done with New Window on the View t...

Reg: Method execution time
Hi All, I wanted to calculate the time take for execution of method based on CPU Instruction execution Speed. Is it possible to estimate total time to be taken for the following code, before executing the code? are there are APIs provided for this purpose? void Run() { int i = 100; while(i) { printf(" cnt %d\n",i); i--; } } thanks in advance, Chandrasekhar <> wrote in message > Hi All, > > I wanted to calculate the time take for execution of method based on > CPU ...

request failed with HTTP status 401: Unauthorized accessing report
Hello, We are trying to get reports working on CRM 3.0 and running into an error when clicking the reports : Server Error in '/' Application. The request failed with HTTP status 401: Unauthorized. The reports are working when we access crm on the crm server when using the servername, but if we access crm using the ip address then we get the above error message when we click Reports. CRM is on the separate server. SQL and Reporting Services are on the same server. even accessing the crm by entering localhost does not work on the crm server. Any ideas? thanks Have a look at thi...