Run Excel Macro From Access 2003 Conversion Problem

My 2000 access application shelled to Excel, ran a macro that prepared Oracle 
output for importation to Access 2000.  I used the Microsoft "178116; ACC: 
How to Determine When a Shelled Process Ends" logic and it worked perfectly.  
When I upgraded to Access 2003, the Excell macros now runs twice more AFTER 
the shelled routine has finished.  I tried substituting the "129796 How To 
Use a 32-Bit Application to Determine When a Shelled Process Ends" and 
"209876 HOW TO: Determine When a Shelled Process Ends in Access 2000".  
Neither corrected the problem.  I inserted several debug print statements in 
both the Access function and the Excel macro and determine that yes, the 
module completed all statements correctly, but, before exiting the sub, the 
Excell macro runs twice more.   If I run just the Excel macro, it runs just 
one time, as it should.

I address this to the Access newsgroup since my base application is in Access.

Any thoughts will be appreciated.  

Kevin
0
Utf
7/7/2006 11:32:01 PM
access.conversion 3038 articles. 0 followers. Follow

2 Replies
617 Views

Similar Articles

[PageSpeed] 42

Without knowing the actual code you're using, it's difficult to say. 
However, I'd recommend using Automation, rather than shelling to Excel.


-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Kevin Kilpatrick" <kkilpat@crosslink.net(NOT)> wrote in message 
news:EC868CBA-35BD-4927-BA90-4328822BA918@microsoft.com...
> My 2000 access application shelled to Excel, ran a macro that prepared 
> Oracle
> output for importation to Access 2000.  I used the Microsoft "178116; ACC:
> How to Determine When a Shelled Process Ends" logic and it worked 
> perfectly.
> When I upgraded to Access 2003, the Excell macros now runs twice more 
> AFTER
> the shelled routine has finished.  I tried substituting the "129796 How To
> Use a 32-Bit Application to Determine When a Shelled Process Ends" and
> "209876 HOW TO: Determine When a Shelled Process Ends in Access 2000".
> Neither corrected the problem.  I inserted several debug print statements 
> in
> both the Access function and the Excel macro and determine that yes, the
> module completed all statements correctly, but, before exiting the sub, 
> the
> Excell macro runs twice more.   If I run just the Excel macro, it runs 
> just
> one time, as it should.
>
> I address this to the Access newsgroup since my base application is in 
> Access.
>
> Any thoughts will be appreciated.
>
> Kevin 


0
Douglas
7/9/2006 10:02:21 PM
Looking at automation, I don't think this is the solution for my headache. 
I need to run the Excel macro within a Access routine as the remaining code 
totally relies on the Excel macro's results.  Code segment follows:

    strexcel = "Excel.exe " & varxlspath
    ExecCmd strexcel

    'Clear Tables
    DoCmd.OpenQuery "q_Delinitiated", acViewNormal, acEdit         'Clear 
t_initiated Table
    DoCmd.OpenQuery "q_Delmaindata", acViewNormal, acEdit          'Clear 
t_maindata Table
    DoCmd.OpenQuery "q_Delhudm", acViewNormal, acEdit              'Clear 
t_hudm Table
    DoCmd.OpenQuery "q_Delicmdata", acViewNormal, acEdit           'Clear 
t_icmdata
    DoCmd.OpenQuery "q_Delcdsdata", acViewNormal, acEdit           'Clear 
t_cdstartend
    DoCmd.OpenQuery "q_Deldkrpt", acViewNormal, acEdit             'Clear 
t_dkrpt
    DoCmd.OpenQuery "q_Delstaffasgn", acViewNormal, acEdit         'Clear 
t_staffasgn
    DoCmd.OpenQuery "q_Delcasesbyhours", acViewNormal, acEdit      'Clear 
t_casesbyhours
    DoCmd.OpenQuery "q_Delcaserank", acViewNormal, acEdit          'Clear 
t_caserank

    DoCmd.TransferSpreadsheet acImport, 8, "t_initiated", varcmipath, True
    DoCmd.TransferSpreadsheet acImport, 8, "t_hudm", varhudpath, True
    DoCmd.TransferSpreadsheet acImport, 8, "t_icmdata", varicmpath, True
    DoCmd.TransferSpreadsheet acImport, 8, "t_cdstartend", varcdspath, True
    DoCmd.TransferSpreadsheet acImport, 8, "t_dkrpt", vardkrpath, True
    DoCmd.TransferSpreadsheet acImport, 8, "t_casesbyhours", varcbhpath, 
True

The routine continues to the Exit Sub (checked this using debug.print). 
ExecCmd function is pulled directly from KB article 178116 "ACC: How to 
Determine When a Shelled Process Ends"" with no code changes.  I tracked 
Excel in Task Manager.  After each run, the Excel process dropped from the 
task manager.  Which means something was starting Excel outside of the 
routine.  But what?

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
news:OhzraN6oGHA.3288@TK2MSFTNGP03.phx.gbl...
> Without knowing the actual code you're using, it's difficult to say. 
> However, I'd recommend using Automation, rather than shelling to Excel.
>
>
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Kevin Kilpatrick" <kkilpat@crosslink.net(NOT)> wrote in message 
> news:EC868CBA-35BD-4927-BA90-4328822BA918@microsoft.com...
>> My 2000 access application shelled to Excel, ran a macro that prepared 
>> Oracle
>> output for importation to Access 2000.  I used the Microsoft "178116; 
>> ACC:
>> How to Determine When a Shelled Process Ends" logic and it worked 
>> perfectly.
>> When I upgraded to Access 2003, the Excell macros now runs twice more 
>> AFTER
>> the shelled routine has finished.  I tried substituting the "129796 How 
>> To
>> Use a 32-Bit Application to Determine When a Shelled Process Ends" and
>> "209876 HOW TO: Determine When a Shelled Process Ends in Access 2000".
>> Neither corrected the problem.  I inserted several debug print statements 
>> in
>> both the Access function and the Excel macro and determine that yes, the
>> module completed all statements correctly, but, before exiting the sub, 
>> the
>> Excell macro runs twice more.   If I run just the Excel macro, it runs 
>> just
>> one time, as it should.
>>
>> I address this to the Access newsgroup since my base application is in 
>> Access.
>>
>> Any thoughts will be appreciated.
>>
>> Kevin
>
> 


0
Kevin
7/12/2006 12:46:05 AM
Reply:

Similar Artilces:

Need only one DLL instance to run...
Hi all, 1: if two apps load the same DLL - LoadLibrary(...) - system will create to different instance of the DLL... Now, in my DLL i've a CList and i need it to be visible to all instance and all apps!!!! Is there a way!? 2: I need the dll to remain loaded till machine reboot!!! Is it possible!? Thanks Ale >if two apps load the same DLL - LoadLibrary(...) - system will create to >different instance of the DLL... >Now, in my DLL i've a CList and i need it to be visible to all instance and >all apps!!!! > >Is there a way!? It'll be difficult to share a ...

Excel combo box
Is there an easy way to maintain a date format from the original cell through selection via a combo box? --- Message posted from http://www.ExcelForum.com/ If you add the items yourself--instead of using listfillrange, then the combobox from the control toolbox toolbar will keep the formatting: Option Explicit Private Sub Worksheet_Activate() Dim myCell As Range With Me.ComboBox1 .ListFillRange = "" .Clear For Each myCell In Me.Range("a1:a14").Cells .AddItem myCell.Text Next myCell End With End Sub or if you want...

credentials to run this report are not stored
Hi all, I'm getting this problem when I try and create a timed subscription. Error: ...credentials to run this report are not stored..... ok I created another folder under the same folder where I'm having this problem. loaded the report and I have no problem. I even moved the current folder with that report in it. Creted another folder with the same name and still the same problem with that named folder. With a different name for the folder I have no problem??? I'm stuck here.. almost all post just talk about storing the credentials, Already done and works...

How do you escape from Access Calendar.
Hi, I using Access' active x calendar control in Access within XP Office pro. The code is working fine, but how do I escape from the calendar if I accidently click on my date field? My code is setup to dispaly the calendar control when I click on the date field. When I click on a date on the calendar, I get the date, assign it to my field, make the calendar invisible, and set focus on my date field. My question is, how do I exist the calendar if I click on the date field by mistake. I tryed to check for the Escape key in the KeyDown event for the Calendar, the KeyDo...

Word Mail Merge with Excel as data file
Word 2003 Excel 2003 Windows XP Why would a mail merge show only every other Excel row when merging with a Microsoft Word form? I am also cross-referening this to the Word forum. Thanks in advance! Sounds like perhaps you have TWO <<next record>> fields in your main document for each, and you should only have one. ******************* ~Anne Troy www.OfficeArticles.com "HT" <asfafd@microsoft.com> wrote in message news:OER87r9hFHA.3260@TK2MSFTNGP10.phx.gbl... > Word 2003 > Excel 2003 > Windows XP > > Why would a mail merge show only every othe...

run time error 10-22-03
I am having a lot of trouble when I open up word I get run time error 52 in VB. I have tried uninstalling word and reinstalling it. WE have tried deleting the macro but still to no avail can someone help me please? ...

Excel Validation Drop Down Box
Is there any way to increase the size of the text in the validation drop down box? If the sheet is zoomed to say 60% the text in the box is not readable. No, but you may find this helpful: http://www.contextures.com/xlDataVal08.html#Larger In article <0a4c01c3999c$92878a10$a001280a@phx.gbl>, "Mike S." <anonymous@discussions.microsoft.com> wrote: > Is there any way to increase the size of the text in the > validation drop down box? If the sheet is zoomed to say > 60% the text in the box is not readable. ...

Repost: Error running Report in an Access 2003 db from Access 2007
Ok, clarification - ignore the code from my original post, some of the reports do work. The ones that don't are reports that I have being filtered. Here is the code from one of those buttons: Private Sub Ok_Click() On Error GoTo Ok_Click_Err 'using the customer sub form for customer state report to filter the report, clicking ok will open report for selected state Dim stDocName As String Dim stLinkCriteria As String If Not IsNull(Me.Search_Results) Then stLinkCriteria = "[StateOrProvince] = """ & Me![Search Results] & """"...

System Error when opening Excel 2007
A client is receiving this error "system Error &H80004005(-2147467259)" in excel 2007 when opening a new file or an exisiting file. This error came up after visio 2003 was uninstalled and visio 2007 got installed on his system. This error can be a lot, and I looked a bit around where they say it might be something with memory. But then I wonder why he never gave this error before untill visio 2007 got installed. Any help is welcome and I would be very thankfull to those who can help me in this issue. Greetz ...

Automatically run macro
My name is Mike and i have a question about microsoft excel macro's. Attached is a copy of the excel sheet im working on. Below the excel sheet is the macro I built. Some of the cells contain given values and some cells are calculated from formulas. Cell (G4) is my given value...it is related to cell (C32). The point is, I plug a value into cell (C10) and it runs through the rest of the calcs in the other cells and gives me a value to cell (C32). I built a macro that works as a goal seek pretty much. The macro makes cell (C32) equal to cell (G4) and gives me the value for cell (C10). I wan...

New at this Excel VBA game and very perplexed
I am trying to export data from a Word form to a Excel spread sheet. The Word form has three data fields. I want to export the form data to a new row directly under existing rows in the spreadsheet (i.e., as each new form is created I can run the macro and export the data to the growing spreadsheet). I have this code: Sub ExportToExcel() Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim myWB As String Dim oFF As FormField Dim i As Long myWB = "E:\My Documents\Word\Word Documents\Word Tips\Macros\Working With Access And Excel\myExportBook1.xls&quo...

Silly date/text question
Hi- is there an easy way to show dates/text with the day eg. July 17th similar to Word 2k format eg July 17th . Currently cut/paste from Word 2k - to get raised 'th' or 'rd' or 'nd' etc. TIA Dan In custom format use mmmm dt\h >-----Original Message----- >Hi- is there an easy way to show dates/text with the day eg. July 17th >similar to Word 2k format eg July 17th . Currently cut/paste from Word 2k - >to get raised 'th' or 'rd' or 'nd' etc. >TIA >Dan > > >. > Are you looking for the th, rd, nd stuff or are ...

Hidden Column
Hi Can anyone Help I have a spreadsheet, and on my 3rd worksheet, the first column (A) wa hidden, now I want to show that column, but it just isnt appearing. I have tried, Format > Column > unhide as well as Row > Unhide, Ive also tried Edit > Go To > A1 and it still doesnt appear. Any thoughts? ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements Format > C...

Mail Merge using Access then save as PDF
In my organization, we send out letters by the thousands, ususally about 500 at a time, due to the time constraints of the current method. Currently, we process the database, which places the letter contents in a table called Archives. Then we open the Merge Template Word document, and process the mail merge to print each individual letter. Next we have to save each individual letter as a PDF file (Acrobat 8.2 Installed) for record keeping and quality assurance. What we'd like to do is find a way that this can all be done from within Access programmatically. Referen...

IIC
HI., This is saravanan .,I have to send 16 bit address to CAMERA(ov3640) With the help of IIC... There is no 16bit address format In IIC... I Read only 7/10 bit addressing format(IIC) ...I have to develop the program in Visual studio for that 16 bit address to camera & I have to read the camera contents (Through 16 bit address format)...Can u help me ...How to send a 16 Bit address format to camera through IIC... For ex: In OV3640 The product id is:300A., The (16Bit) address I have to send through IIC to camera(for read operation),In IIC registers only 8 bit reg...

Excel Continuous Running Total
I posted a message earlier and have received a partial solution. I want to keep track of how much stock prices go up or down with a running total of how much they go up or down over several days until the direction changes. For example, if price go up 10 on Mon, 20 on Tues, 30 on Thurs and down 10 on Fri I want my running total column to show a positive number of 60 and then a red number of 10 and continue adding the amount of the total of the down days until the market shows an up day. The formula I am now using total the first and second day but does not do a running total count if t...

tables created by Linking an Excel File
Hi Mates I have a linked access table created from an excel spreadsheet. Then I used Table Analyze feature in access to split this and the split created 3 seperate tables. When the excel spreadsheet is changed the linked table gets updated but split tables remains unchanged. Is there a way these split tables to get updated automatically. Thanks mates in advance for your help. Sarah W Hi Sarah, The table analyzer is designed for splitting a non-normalised table into two or more new, normalised tables. This is a one-time process: the new tables are entirely separate from the old one. T...

New accounts cannot access their mailbox
Hi I am having a problem with new accounts in SBS 2003. When we create new users they are unable to log into there mailbox if you check there "email address" tab in ad the do not receive any email addresses. We have tried loading the latest service packs and patches and rebuilt/reload the RUS but without any success Thanks Kris In news:8A0CEDE8-B192-4278-82EA-85967166E324@microsoft.com, luckr01 <luckr01@discussions.microsoft.com> typed: > Hi I am having a problem with new accounts in SBS 2003. When we > create new users they are unable to log into there mailbox i...

display changing label caption on form as sub runs w/o screen flic
let's say i have this routine Sub Test label1.caption = "Starting ... " 'do events label1.caption = "Getting there ... " 'do events label1.caption = "Finished! ... " End Sub on my form, i have label1 right in the middle what happens is sometimes the message will change, and then sometimes it wont, or it will show the first one, skip the second and jump to the third etc etc etc so it is inconsistent. is there a way to make sure the label caption displays consistently, on time, wh...

Conditional Formatting in Excel
Hi, If D1 is empty then G1 should be white(clear) if D1 has a number in it then G1 should be red additionally; if a value, either a figure 0 or a currency value is entered in G1, which owing to the previous two conditions, would be red, then G1 should change to blue. If anyone can explain how to acheive this I would be extremely grateful. Thank You Brian Ok, you have a few conditions: Isblank(d1) means clear Formatted as Currency Means “Blue” Any value not formatted as Currency Means Red Step 1 I would insert a column to calculate this and format based on the resu...

Outlook 2003 rule for incoming mails
Hi, lets say I have a contact "John Miller" with the primary e-mail address john.miller@somewhere.com and secondary jmiller@elsewhere.com. I would like to set up a rule which only applies to some of John Millers addresses, but not to others, i.e. if I receive an e-mail addressed to jmiller I would like to forward it or move it to another folder or delete it, but that shouldn't apply to mails sends to john.miller. The problem is, if I setup a definition for that, Outlook overwrites the e-mail address with the "John Miller" contact information and that results in a rule ...

Create a custom footer for all Excel worksheets at one time...
I want to create a custom footer, just once, that could be applied to all worksheets in an Excel workbook. Microsoft Word lets you do this, so why not Microsoft Excel? BigCat Try the code below: Be sure to substitute the information for what you want. HTH Dennis ******************************************** Sub PageSetupAllSheets() Dim EachSheet As Worksheet For Each EachSheet In ActiveWorkbook.Worksheets With EachSheet.PageSetup ' &F = File ' &A = Tab ' &D = Date ' &T = Time ' &P = Page ' &N = Pag...

Money 2002 will not run
I had been using Money 2002 for approx. 3 yrs on my home pc (Dell Dim 2100, XP Home Ed. w/SP2). Last year, it simply would not launch. No error message, no splash screen, no app opening, no process listed in Task Manager. Just.... nothing.... The only change that was made to the system since M2k2 last ran was upgrading my a/v solution from Trend Micro PC-Cillin to TM Internet Security. I have tried disabling every aspect of the Internet Security product, as well as completely un-installing the app, and then attempting to run Money, but the same thing (nothing) happens. I was considerin...

macro which finds last cell in a column
please help me by telling a macro which finds last cell in a column thank -- Message posted from http://www.ExcelForum.com Dim LastRow as Long LastRow = Range("A65536").End(xlUp).Row or if you just want to select it: Range("A65536").End(xlUp).Select Regards Trevor "vikram >" <<vikram.15hp0w@excelforum-nospam.com> wrote in message news:vikram.15hp0w@excelforum-nospam.com... > please help me by telling a macro which finds last cell in a column > > thanks > > > --- > Message posted from http://www.ExcelForum.com/ > ...

' in excel
when i type 'something in a cell. ' - as a single quote it always display only something, without a ' anyone knows why? does that ' mean anything? thx it means the entry is in text form charlie <charlie@discussions.microsoft.com> wrote in message news:53980654-8066-4D0E-85B1-538FD378F4FE@microsoft.com... > when i type 'something in a cell. ' - as a single quote > it always display only something, without a ' > anyone knows why? > does that ' mean anything? > thx The single apostrophe (') has long been a qualifer in spreadsheets to ...