Visual Basic :: Calling An Excel Macro From VB

Calling Macro In Excel From Access With Vba
HI

Im new here, this is my first question and I hope someone out there can lend me hand or point me in the right direction. Thanks.

I have developed an Excel template with a macro in VBA. I have ten different templates which I want to fire off. At the moment, all ten is fired off from Access using a shell command, but Im finding that it uses too much virtual memory. The ten templates are all the same except using different parameters, so I am thinking of just having one template and using the Access to call up a template and passing in the parameter so the macro can act on it using that parameter. Is there a way of calling a macro function in Excel using vba?

Anyone got any advice, will be greatly appreciated.

Cheers

Calling Access Macro From Excel
I have a model, running from a macro in Excel. The model cycles through hundreds of thousands of iterations for forecasting purposes. I am taking the output from the model after each iteration and dumping it into Access.

I am experiencing two problems calling the Access macro (that imports the data and places it in the appropriate table) from Excel:

1.) The database connection times out. The speed of the Excel macro is drastically reduced if I open Access after each iteration to dump data in. Therefore I have connected to Access in the beginning of my Excel macro and am only calling the Access macro after each iteration. I tried to open and close the database with each iteration, but it is time consuming and sometimes the database doesn't close which causes the program to halt.

2.) I call the Access macro in the following manner:

In the beginning of the macro the following is established:
Dim cn As New Access.Application
cn.OpenCurrentDatabase ("C:
ame.mdb")
cn.Visible = False

Then after each iteration:
SendKeys "MacroName{ENTER}"
cn.RunCommand acCmdRunMacro

Then at the very end:
cn.CloseCurrentDatabase
cn.Quit acQuitSaveNone
Set cn = Nothing

The problem is with the "SENDKEYS" statement. Sometimes, about 1% of the time, the macro seems to skip over the line. The macro window will open in Access prompting me to enter the macro name and "ENTER".

Any wisdom that could be imparted regarding these two issues would be greatly appreciated! Thanks for your help.

In Excel, Calling Word Macro
Please Help, i have gotten this far and am stuck: I have an Excel Macro that copies a specific cell, and it then needs to call a Word macro to paste the contents. I know the copy/paste commands, but am having trouble calling the Word Macro. Any help would be appreciated, for i am tearing my hair out

Calling Ecternal Macro In Excel Using VB
I have 2 files open-- abc.xls and xyz.xls.
How can I call a Sub (say Sub Test() in module1) from abc.xls to a macro in xyz.xls

Thanks.

~BS

Visual Basic Calling An Excel Macro?
Is it possible to have Visual Basic call a macro from excel?

Calling A Regina REXX Program From Excel VB Macro
Does anyone know if it is possible to call a REGINA REXX program from an EXcel VB Macro.

Cheers

Simon

Calling An Excel Macro From An Access Database That Refers To That Database
hi

i have a macro in excel that inserts some values into a table in a database in access, but i want to make it so i dont have to touch the excel file, instead i have a module in the access file that calls the sub in the excel file, but then i get an error saying that the database is already open....is there a way of getting around this?

thanks

jimmyp

Calling A Macro
How can i call a macro as soon as a form is loaded from outlook?

Calling A Macro...
Hi all,

I need to call ADD-IN and use one of its functions to run on a workbook that is opened from VB6.
I have noticed that when VB opens an instance of Excel it does not open the ADD-IN's that would have opened with it if it was opened as a Normal App.

How can I call this ADD-IN without having to copy its code across to the app I am writing.

Any help advice would be great....

Thanks in advance

[Edited by Rocks on 01-04-2001 at 11:43 AM]

Converting An Excel 2002 Macro To An Excel 95 Macro
Hi there, I’ve finally got my macro program to work (thanks everyone). Now I have to load the macro on a much older machine. I wrote the Macro on Excel 2002 and now I’m trying to run it on Excel for Windows 95 Version 7.0. I’ve loaded it up, but there are all sorts of errors. Does anyone know of a simple way to convert the macro code so it will run smoothly on Excel 7.0?

Calling A Macro From A Macro
Hi

I am writing a macro within Excel. I want this to invoke a word object:

Dim obj As Object
Set obj = CreateObject("Word.Application")
obj.Visible = True

And then maybe load my template file, and call a macro within that. Basically, I want to create a mail merge autoamtically from the data in the Excel file at the touch of a button.

Regards
Ben

Easy One - Calling Macro From Another
I'm writing a macro which goes through each shape, header and footer in a Word document individually and runs an external macro on it. This external macro was not written by me and I've no access to the code.

So I have the macro loop through all the objects in question and call the macro with Run, but the whole thing seems to stop running after the Run statement, and the macro appears to run on the whole document, excluding the headers and footers. Should I be using something else rather than Run?

VB Code:
Sub CleanWholeDocument() Dim sShape As ShapeDim fNote As FootnoteDim HeadFoot As HeaderFooterDim sSection As Section For Each sSection In ActiveDocument.Sections For Each HeadFoot In sSection.Headers HeadFoot.Range.Select Application.Run MacroName:="tw4winClean.Main" Next HeadFootNext sSection For Each fNote In ActiveDocument.Footnotes fNote.Range.Select Application.Run MacroName:="tw4winClean.Main"Next fNote For Each sShape In ActiveDocument.Shapes If sShape.TextFrame.HasText Then sShape.TextFrame.TextRange.Select Application.Run MacroName:="tw4winClean.Main" End IfNext sShape Application.Run MacroName:="tw4winClean.Main" End Sub

Calling A MSAccess Macro
Is there a way that I can call a MSAcess macro in a closed database from VB 6.0? Thanks for any help.

Calling A Macro In A Different Project
Hi
I have a macro within Excel which opens all workbooks within a specified folder. For each of these workbooks I need to call a macro that exists within the workbook.
I'm having problems getting VBA to do the call using a variable as the workbook name (the module & macro names can be specified as a constant). In fact I'm even having problems doing the external call when I explicitly write out the workbook name in the call.
Thanks for your help.

Calling A Macro From Another File
Dear Friends,

How do i call a macro from another file? For example, I want to write a macro in "c:ook1.xls" which will call a macro(sub test()) present in "d:ook1.xls".

Requesting your kind help in this regard.

Regards,
S. Sivakumar

Openning Excel And Running Macro From Word - Macro
Ok - I have a DB which I export to Excel, I then run a macro to "Groom The Data". I then run word and run a Macro (MailMerge to print labels) which retrieves data from the excel spreadsheet groomed above. Everything is working fine, despite my novice knowledge of VB, but I would like to do this in a single click.

I use a command line shortcut to Launch word and automatically have it run the macro, but I still have to Launch Excel and manually run its macro first.

I did not find a command line switch to make excel launch and run a macro. Is there any way I can Lauch Word have its Macro link to excel, then launch and run the excel macros before performing the macro I have set up in word (mailmerge)

In Other Words I have working macros in Word and Excel, I want the Word Macro to force the Excel macro to run before completing the rest of its duties.

Thanks
WW

Calling Access Macro Problem
Is there anyway to keep access from popping up while doing the snippit below?

'Open database
objaccess.OpenCurrentDatabase "my database"

Thanks

Calling A Access 2000 Macro Within VB6
Does anyone know how to call and run a Access 2000 macro from within a VB6 application.

Any help appreciated,

Regards,

Moben.

Need Help With Calling Macro Automatically <RESOLVED>
I'm currently using vb in excel to do several tasks, although one troubles me. I would greatly appreciate some sort of insight in this matter. Im trying to print the spreadsheet at midnight. The printing is no problem, i just call on a macro to do that. The issue arises with how to call that macro at midnight. Ive tried several ways including an ondata command, but have found no way to check the time, to call the macro automatically from either the spreadsheet or a command. Any help would greatly be appreciated....

Edited by - flopro on 6/14/2004 2:18:07 PM

Calling A Macro By Doubling Clicking A Cell
Can i call a macro from a sheet by double clicking a cell?
thanks

Calling Microsoft Access Module Or Macro From VB
I would like to call a Microsoft Access Module or Macro from a VB executable. This way I could schedule some database activity from my NT server.

I do not know the commands to open an Access Database (with password protection) and us the DoCmd.

Please Help!!

Calling A Macro In Another Template In Word 2000
GOod day,

I am trying to call a macro in a template that I attach a run-time and the error I get keeps telling me that it is unable to run the specified macro...the code is as follows

Code:Sub ApplyNewTemplate()

' attach the new Signature template to override styles

    With ActiveDocument
        .UpdateStylesOnOpen = True
        .AttachedTemplate = _
            "C:WINNTProfiles" + Environ("USERNAME") + "Application DataMicrosoftTemplatesSignature.dot"
    End With

Quote:THE ERROR OCCURS HERE...Should I be using another method?
' auto-run the conversion to the new look after the signature template is attached
    Application.Run MacroName:="Signature.NewLook.NewLookSignature"


End Sub

Thanks in advance...

J.C.

"Do or do not, there is no try."

Performance In Embedded Macro And Calling From EXE Project
I wonder if embedded macro will perform much more faster than calling excel
object from standard .exe application?

Anyone with the answer why...please explain to me. Thanks!

Run Access Macro From Excel Macro
Hi

I am using Microsoft Office 2000 and trying to run an Access macro from an Excel macro, but my Excel macro does not seem to be working. When I run the Excel macro, nothing happens - I do not get an error and the code does not do what it is suppossed to. Below is my code:

Code:
Sub DisplayForm()
' Initialize string to database path.
Const strConPathToSamples = "C:Miscell est.mdb"

strDB = strConPathToSamples & "test.mdb"
' Create new instance of Microsoft Access.
Set appAccess = _
CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strConPathToSamples
' Run Access macro.
appAccess.DoCmd.RunMacro "Macro1", 1
End Sub
I have included the references to "Microsoft DAO 3.6 Object Library" and "Microsoft Access 9.0 Object Library."
Any help on this subject would be much appreciated.
Thanks

Lotus Macro To Excel Macro Please Help!!
I know nothing about Lotus but I need to convert Lotus macro to Microsoft Excel. Can anyone help

Excel Add-ins - Making With Modules Only - Calling From Excel Worksheets Via Vba
I ave 2 questions;

Question ONe

How do I make a add-ins for excel with modules only. Excel says that I must have atealst one worksheet - can I make a add-ins which are not COM dlls without worksheets, ie simply se existing excel modules ?

Question Two

How do I can a function or sub procedure from a module contained in my add-in ?

thanks

adam

How Can I Run Vb Code Calling Excel Application On A Machine Without Excel
Dear All,

I have a vb component which is calling Excel Object.
I have to run this code on a machine where Excel is not installed.
Please can anyone suggest me what all excel dll files are required
to run this code.

With Thanks in advance,

-- Vivek

Excel VB Macro Invoked By Customized Button Not Moved When Excel File Is Copied && Pasted
hello....the thing is I wrote a macro in an excel file which get's invoked when a customized button is clicked......now when I copy and past this excel file, the button in the new excel file still keeps pointing to the macro in the old file and only the macro in the old file is invoked again.....can anybody tell me how to resolve this problem?

Possible To Launch A UserForm In Excel From Startup Macro Without Showing Excel?
Greetings to all VBA/Excel Excperts!

I have a project at work that is requiring me to utilize Excel/VBA (something I'm not super-conversant in) and I am wondering if it is possible to have a UserForm be the first thing that pops up when you launch (i.e. double click on) a spreadsheet (likely an .xlt but not necessarily)? I know I can have an Autostart Macro (or something of that ilk) that could launch the form, but I am hoping there is a way to do this so that it just looks like I launched the UserForm and am not firing up the entire Excel program.

As soon as the functionality encapsulated in the UserForm is completed it will shut down Excel, which is why I'd rather just have the form and nothing else.

My reason for doing this is to make deployment of this "application" simple - just drop the spreadsheet onto the target system (which of course must have Excel 2003 installed, which all of our company systems do) and double click. Saves having to create an install package for a plain ole VB app.

Thanks for any suggestions and/or taunts you may have!

How To Create Report In Word Format Through Excel Or Excel Macro
Hi...
I want to create one Report in Word document Format through Excel.
I have some Data in excel.Now How can I create report in word format using Macro or any other feature, if yes then How ??
Can anyone help me in solving this.
Thanks a ton !!!

Want To Write An Excel Macro That Automaticaly Closes Excel When Finished.
HELP!!! I am trying to get excel to close itself down after a macro has run but have no idea how to do it. I can get it to save&close the current spreadsheet but thats it. Can anybody please help?

Whats The Vb Code To Close Excel Within Excel Using A Macro
i need the code to close excel within excel using a macro and saving all of the changes.

please help//

Jas

Emailing Excel Files Via Outlook Using An Excel Macro
Ok maybe this is an easy one for someone out there.

My goal is to write a macro within Excel to automatically grab a file, attach it to an email and send it from Outlook. I know this can be done. I currently have a copy of a macro that will do this exact sequence but through Lotus Notes instead of Outlook. My hang up has to be with the communication between the two programs.

This is what I have currently (Excel to Lotus Notes):

Const embed_attachment As Integer = 1454
Dim session As Object
Dim db As Object, doc As Object, ws As Object, rtitem As Object
Dim CurRow As String
Dim Recipient As Integer
Dim special As String, location As String, ext As String

Sheet2.Select

'Establishes Notes session using server and .nsf file
'Server and sender must be the person sending the email
server = Cells(2, 2)
Sender = Cells(3, 2)

Set session = CreateObject("Notes.NotesSession")
Set ws = CreateObject("Notes.NotesUIWorkspace")
Call ws.opendatabase(server, Sender)
Set db = session.GetDatabase(server, Sender)

'Begins the mail loop for the number of recipients
Recipient = 8
send_to = Cells(Recipient, 2)
Do
Set doc = db.CreateDocument()
doc.savemessageonsend = True
subject_out = Cells(4, 2)
Message = Cells(5, 2)
Message2 = Cells(6, 2)
special = Cells(Recipient, 3)

doc.sendto = send_to
doc.Subject = subject_out
doc.returnreceipt = "1"

Set rtitem = doc.CREATERICHTEXTITEM("Body")
Call rtitem.APPENDTEXT(Message)
Call rtitem.ADDNEWLINE(2)
Call rtitem.APPENDTEXT(Message2)
Call rtitem.ADDNEWLINE(3)

location = "h:Monthly BudgetsColumnarxls"

'Reads filenames and attaches files until it reaches the end of the file list
z = 4
file = Cells(Recipient, z)

Do
Call rtitem.EmbedObject(embed_attachment, "", location & file & ext & ".xls")
z = z + 1
file = Cells(Recipient, z)
Loop Until IsEmpty(file) = True

'Sends file, resets doc variable to empty, and advances to next recipient

Call doc.Send(False)
Call doc.Save(True, False)

Set doc = Nothing
Recipient = Recipient + 1
send_to = Cells(Recipient, 2)

Loop Until IsEmpty(send_to) = True

'Sets variable to empty to save on memory
Set session = Nothing
Set db = Nothing
Set ws = Nothing
Set rtitem = Nothing

End Sub

Help!!!!

Thanks,

Matt

Delete An Excel File With An Excel Macro
Hi

Did any know if I can delete an excel file with an excel macro? (the file is not the ones that I am, instead is one in the same directory).

Any suggestions I'll appreciated it

Question From Excel Or Excel Macro Gurus
Hi To ALL

i really want to now that is there is any utility/way or wizard through which i can make my interface on Excel just like this
here i m trying to display my interface which wil be on sheet or user forum

TextBox1 Textbox3
TextBox2 TextBox4

Button--- Add ---.Display result in TextBox4

and Run time Change on TextBox3[that will automatically add textbox1 & textbox2 and display in [Textbox3]

but i want to do this without coding is their is way or wizard in Excel Macro to Do this Please Confirm me this thing is it possible without codind i just want to do this without coding

Somebody told me that through Excel we can do this as Front Page Make WebPage & made their code automatically

Please Reply me about this Question

Thanks in Anticipation

Bye......

Calling Excel?
I know this is a strange request, but using the code below i export data from VB into Excel, but what im after is to open excel ie "C:Program FilesOfficeExcel.exe" then go File>Open and open the file "D:Software Developmentadc1632.xlt" all in one swift movement.
Is this possible?

##################
Private Sub cmdNext_Click()

Dim oXL As Excel.Application
Set oXL = New Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range

Excel.Application.Visible = True
oXL.Workbooks.Open ("D:Software Developmentadc1632.xlt") 'This is a Excel template
With oXL
.Range("A2").value = "Whatever text"
.Range("A3").value = lblProject.Caption
.Range("A4").value = lblJobNo.Caption
.Range("A5").value = lblJob.Caption
.Range("C3").value = txtProject.Text
.Range("C4").value = txtNumber.Text
.Range("C5").value = txtJob.Text

End With
Unload Me
Set oXL = Nothing
End Sub
###############

VB6 Calling Excel
I created a VB6 app with a SQL server 7 backend. I open Excel and read a recordset from SQ7 into the spread sheet. Then, I print the spread sheet. Everything works fine. My problem is when I exit the worksheet, Excel will ask do I want to save the spreadsheet. Under no circumstances do I want to give the user the option of saving the spreadsheet. Is it possible to prevent the Excel save option from popping up prior to exiting the spreadsheet. Here is the guts of my code.

Public rs As ADODB.Recordset
Public cn As ADODB.Connection

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & OuterPackagingdb & ";Persist Security Info=False"
cn.ConnectionString = "DRIVER={SQL SERVER};SERVER=KMU04;DATABASE=KONICA;UID=SA;PWD=sequel;"

cn.Open
Set rs.ActiveConnection = cn
rs.LockType = adLockOptimistic
rs.CursorType = adOpenKeyset

SQL = "SELECT * FROM outer_pack_info WHERE " & "Schedule = '" & txtprint & "'" & _
"ORDER BY approve_Date"
rs.Open SQL

'Declare the excel object variables

Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet

Set objExcel = CreateObject("Excel.application")
Set objWorkbook = objExcel.Workbooks.Add
Set objSheet = objWorkbook.Worksheets.Add

intC = 14

'Get Data from recordset and place in designated cell location
Do Until rs.EOF
objSheet.Cells(intC, 1) = rs!SS_OK

Loop

‘ Print worksheet
With objExcel
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
.Quit

End With

Set objExcel = Nothing
Set objWorkbook = Nothing
Set objSheet = Nothing

End Sub

Calling Excel Application From VB
In my project the output data is written to a DOS file (.DAT) in three column format. I am presenting some graphs using picture box control. But for other reasons like printing I want to present the same graphs using excel charts also. How to invoke excel from VB and show graphs (X vs Y and X vs Z) from a file called "DATA.dat" which is existing in the same path. I have included the excel 9.0 library from references. And wrote the following two lines of code :

Dim TEST As New Excel.Application
TEST.Visible = True

For similiar use of Microsoft Word I am using code like:

Dim oWordApp As New Word.Application
oWordApp.Visible = True
oWordApp.Documents.Open (App.Path & "help.doc")

Which will directly opens word application and shows the file named "help". Similary is it possible to open an excel chart which automatically open and show above said graphs. Becuase it helps in printing. Is it possible to print a form without showing form caption and other bars?

Calling A Function From Excel
I am trying to attach a function which performs a select case to a column in Excel. I don't know how to do this. Can anyone help?

My function is called LetterCodes and the Workbook/Sheet is called:
PCPSelLetters.xls / Sheet 1 (Output)

Thanks.

Calling An Excel Function
I'm attempting to design a program that can call upon an Excel function, perform a find and replace, and then save the excel function under a specific, user entered name. Unfortunately, I am only knowledgeable in basic C++, which is unable to deal with Excel functions. I was wondering if anyone could give me some pointers on if and how this can be accomplished using Visual Basics.

Calling An Excel VBA Subroutine From VB
I have a feeling I'm missing something quite easy here,
but in the interest of time, how do I call an Excel subroutine or
function that is written in VBA from Visual Basic 6?

Thanks!

Different Methods For Calling Excel
Hello:
Can anyone explain me the difference between starting Excel with one of the following methods? I´ve read VB Help and some posts but still don´t understand the difference...

1.- Dim oExcel as Object ; Set oExcel = CreateObject("Excel.Application")

2.- Dim oExcel as New Excel.Application

3.- Dim oExcel as Excel.Application; Set oExcel = New Excel.Application

Thanks a lot

VBA Calling Other Programs In Excel
Hi,
Not sure if this is really the right place for this thread but....I am trying to get Excel to call another program (written in prolog). Is this actually possible? Please help!

Help In Calling EXCEL Application In VB
hi.
i am able to create an excel document using my program by using this code:

Dim xlObject As Object
Dim xlWB As Workbook
Dim xlSheet As Worksheet

Application.DisplayAlerts = False

Set xlObject = GetObject("", "Excel.Application")
Set xlWB = xlObject.Workbooks.Add
Set xlSheet = xlObject.Application.ActiveWorkbook.Sheets(1)

xlObject.Visible = True

With xlSheet

.Name = "Test"

End With

-- what if i want to call an existing excel document. i tried this code but it unloads on its on.

Dim xlObject As Object

Application.DisplayAlerts = False

Set xlObject = GetObject("C:Test.xls", "Excel.Template")

Application.Visible = True

--- i want to show the template and edit it... thanks...

Calling An Excel Function From VB
is the approach along the lines of:

....
x=Application.NORMSDIST(myVariable)
????? thanks in advance.

Calling Forms In Excel
Hey,

This is the problem. I have a form that kind of controlls my makros, BUT it will rest on top of Excel, waiting to be closed in order to proceed with normal Excel work. In VB, calling the form dependant to another window works, however, this example won't work in Excel.

Code:
Private Sub Workbook_Open()
KickStart.Show , ActiveWorkbook
End Sub

Can I work around this?

Calling Excel From Outlook
Hello

I would like to call a vba excel procedure from an outlook vba procedure,
is that possible?

I parse the body of an email in a outlook procedure, write the results in an array and then I want to use the
array data in excel.

Thanks

Walter

Problem Calling Custom VB Dll From Excel
I've written a custom little ActiveX dll in VB and it works fine in a little tester VB app. I've set the Instancing to GlobalMultiUse.

My trouble is trying to use it in Excel.

The VB dll function has the signature:
Public Function ZPrime(ByRef LoValues As Excel.Range, ByRef HiValues As Excel.Range, _
Optional ZPrimeThreshold As Double = DEFAULT_ZPRIME_THRESH, _
Optional CVThreshold As Double = DEFAULT_CV_THRESH, _
Optional OutlierColor As xlColorIndex = DEFAULT_COLOR, _
Optional OutlierLimit As Integer = 3) As Variant

I've compiled the dll and registered it and the Excel module code looks like:
Public Declare Function ZPrime Lib "c:TEMPHardcoreVBPrimePrime.dll" _
(ByRef LoValues As Excel.Range, ByRef HiValues As Excel.Range, _
Optional ZPrimeThreshold As Double, _
Optional CVThreshold As Double, _
Optional OutlierColor As XlColorIndex, _
Optional OutlierLimit As Integer) As Variant

My Excel sheet function call looks like:
=zprime(A1:A16,B1:B16)

and the result I get is:
#VALUE!

What am I doing wrong?

Thanks in advance,

Keith