need help writing a vbscript to open a spreadsheet and run a subroutine in a macro

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - Obtain Unique Work Units via SQL Adamus, I am still getting 197 for total WorkUnits. Code Snippet SELECT DISTINCT Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units], 'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals], FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults] FROM WorkUnitsFaultsMainTBL WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")) And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt)); What about in line four in the code above where I am dividing with /Count([WorkUnit]),2) AS [Pct NoFaults] Could this be causing the WorkUnits field to be total records and not distinct WorkUnits Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 2
    - Copying Named Ranges I have a worksheet with a number of named cells (so that I can put data in them or retrieve data from them with VBA and not have to change anything should rows / columns be added or removed). I have a sort of template file from which I copy and paste the spreadsheet with all the named ranges to create a series of new files. The problem is, the cell names do not copy, so they do not appear in any of my new files. I also notice that on my original file, the range names are defined locally, like with ="Sheet1!$U$33". I imagine that if the sheet was to be renamed something other than Sheet1, I would have a problem. So I guess my question is, a) How can I name ranges in such a way that the name will stick even when I copy and paste the sheet to other workbooks. b) How can I go about naming ranges relative to the active spreadsheet, such that calling Range("my Range name") will always work no matter what spreadsheet I am in, so long as there is such a named range in the worksheet. Thanks! Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 3
    - how to code an EDI transation with VBA Hi, I am looking for some documentation and advise on how to handle EDI within an Access2002 (VBA) app. Thanks if anyone can provide feedback, Jay Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 4
    - Date set and iteration I want to create a spreadsheet to go to the first day of the current month and set a date if the day is a weekday and not a weekend(or holiday if that is possible). I will also want to check against the sheets in the spreadsheet as I have sheets numbered 1 through 31 and will be hiding each day that is not a regular business day. I basically just need to know how to find the current day in the month and iterate through it as well as set the format for the date in excel. Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 5
    - System.Web.UI.WebControls not serializible in Web Parts (MOSS 2007) I create System.web.ui.webcontrols in CreateChildControls and I get error messages that they cannot be serialized. Scott Hillier's book shows examples of this yet I cannot get them to work. Thanks Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 6
    - encrypting and decrypting passwords for application (two-way encryption) Hello there. I have an application that needs to store a password used to connect to SQL. The problem is that I want to be able to encrypt it and decrypt it. One of the things I noticed while googling is that when you are going to decypher something you need a key. so basically where can I store this key so I can decypher the encyption. And which encryption techniques should I use I know encrypting doesn't solve all problems but hey, at least it will buy me some time. Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 7
    - Dusting up on VBA - Excel/Powerpoint I have a few projects requirying knowledge of VBA for PPT and XLS - can anyone recommend any online guides to get me updated and back into VBA ...... tis been so long since I have touched this language! Cheers Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 8
    - ActiveWindow, Macros, and Outlook 2007 So for the past couple of days I have been working on updating my companies outlook auto-responce macros for Outlook 2007. I am almost done but I have hit one hurtle. So far, it is doing everty thing I want it to do, but the last part, which is paste of information in to the original window I executed the Macro from. What this Macro does is it opens a word doc, copies it's content and ads it to the Email I have open. Only thing is, when it get to selection.paste I get a runtime 91 error. Only thing I can assume is that when it gets to TargetDoc.activate it doesnt understand what TargetDoc is, even though the first line in the macro is set TargetDoc = ActiveWindow. I am guessing, the new Outlook does not understand what ActiveWindow means. Any clues on how to fix this Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 9
    - How to write code for dynamically (programatically) generated combo boxes (excel-shape object) in excel? Hi, I am making several controls through code. what code should I write in the class module How can I refer the change events of those combo boxes I have created Can any one explain. cheers Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 10
    - WER wish list for developers 8-) Thanks DHON, We can make the change in text from Event ID to Bucket ID to match the client logging, and update the documentation (folks are already working on that). What does the turnaround time (Aggregating and calculating growth trends for WER reports from around the world) need to be for developers to be effective Thanks, -Jason Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 11
    - Ajax in CRM I think your problem is that you are making an asynchronous call to your .aspx page. Try making it synchronous: xmlHttp.open("GET",url,false) ; Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 12
    - Populating combo box via the workbook open event Hi all, I have a worksheet with a combo - box embedded in it of type 'form' rather than active x. I have written a function to populate it via the worksheet_activate event, passing the combo-box to a module function by reference. However I find that if the workbook opens to that page because it was active when last saved the worksheet event does not fire. As a workaround I have thought of using the workbook_open event instead instead but don't know how to pass the combo-box by reference to the module function as it is not visible using the 'me.combobox' syntax I used for the worksheet event. Anyone have any ideas Rob Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 13
    - LCS 2005 Internal Vs External Hey all, I am trying to get our LCS2005 server setup in a way so our Communicator 2005 settings are the same. I would like to have only have one naming channel to use, as opposed to 2, so that those on laptops can come in or be out on the road and connect using the same connection name that EVERYONE else uses, while ensuring that everyone goes through the proxy server. LCS Server's FQDN: lcsserv.domain.local LCS's Access Proxy FQDN: aproxy.domain.local Setting within Comminicator 2005: Server name used to configure the connection to a communcations service: inside people: lcsserv.domain.local road warriors: com.domain_pseudoname.com For our DNS, I have the settings configured/alias setup so that if anyone types in or tries pinging com.domain_pseudoname.com; the correct internal IP address is associated and IS pingable. Also, if you do an nslookup on com.domain_pseudoname.com, it resolves to aproxy.domain.local. So as far as I can see, my DNS isn't an issue as of yet. On the outside, if I were to type in com.domain_pseudoname.com, the correct Public ip address resolves. At this point, my inside and outside people can connect to the Commincator's service, however they are using two seperate "servernames used to configure the connection to a communications service." Anyone have any ideas Thanks all in advance! Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 14
    - How do I update a cell in a closed workbook? Did you include the reference to the library as stated at the top of the page External reference(s): Microsoft ActiveX Data Object 2.5 Library and later. Use menu Tools > References.. Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 15
    - iexplore.exe - Application Error I discovered the problem. It was because of Yahoo Toolbar add on. I uninstalled it and now it works fine. Marty Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 16
    - Using a Check Box in Excel I have never written any code before so I need some help. I would like to insert a check box on a sheet in excel. If that box is checked I would like to copy specific information from a range of cells from another sheet to the sheet with the check box in it. For example, I would like to copy data from cells D7 through CO7 in sheet 1 to cell D1 in sheet 4. I know how to insert the check box but I don't know how to write the code. I would also like to know if the code is affected by the name of the sheet. For example, if sheet 1 is named "quizzes" rather than "sheet 1" will that affect how I write the code Thanks in advance for your help. Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
- Next
  - 1
    - Verifying completion of a file's creation from VBA I need a way to monitor a file in a folder (created by an external application, such as Word or Acrobat), and verify that the process of writing to the file has completed (in Windows 2000 or XP). I.e., that the file is finished, and can be opened by Excel. It's easy to verify that the file exists, and what it's file length is, but the file length continues to increase as the file is written to (especially for a large file). Anyone know of a way in VBA to verify that the file is complete I assume that Windows sets some kind of bit in the file to indicate completion. Thanks... Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 2
    - "StackHash" crashes Hi all, this is probably not the right place for this question, but I'm giving it a shot anyway, counting on the fact that we have experienced people here who crash applications for a living I recently noticed APPCRASH report data which specify something like "StackHash_1703" in the "faulting module" parameter of the Windows Error Reporting crash data. When this type of crash is reported, the fault offset in the module always seems to be 0. Fault bucket 42424242, type 1 Event Name: APPCRASH Response: None Cab Id: 0 Problem signature: P1: foo.exe P2: 1.42.42.42 P3: 46b17782 P4: StackHash_1703 P5: 0.0.0.0 P6: 00000000 P7: c0000005 P8: 00000000 P9: P10: There does not seem to be a module (=DLL) called "StackHash", so I assume it rather specifies a special class of crashes. When I search the web, I find lots of other similar incidents, but (so far) no good explanation of what this crash class means. There seems to be at least a weak connection with DEP (data execution prevention) in practice, but this may be coincidental, of course. My current hypothesis is this: The application crashes. The kernel's top-level crash handler tries to walk the application's stack, or at least checks stack consistency. The stack has been corrupted beyond recovery, or at least so badly that the faulting module cannot be determined anymore. Lacking the module data, the OS crash handler inserts "StackHandler_xxxx" as the name of the faulting module, indicating that the stack has been corrupted. "xxxx" seems to vary and either indicates the type of stack corruption, or some kind of (hash ) value representing stack configurations. Any hints most welcome! Claus http://www.clausbrod.de/Blog Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 3
    - VBA in Excel - organizing charts as objects in a sheet I am using a macro to create multiple charts from data on a separate sheet in the Excel file. However, the program places each new chart on top of the previous one in the sheet I select. I would like to place these charts apart on the sheet so that they are viewable. 1) Is there an command to order the charts down and across the sheet 2) Alternatively, can I select and move each sheet without using the actual object name in the while loop, "Chart1," for example. 3) Specifiying the object name would only work for the first chart. Is there a way to number the charts with a variable Here is the while portion of my code as it is now ( red text indicates the portion I am having difficulty with ) : While j < 243 Charts.Add With Sheets("TRESULTSdmp_3") ActiveChart.SetSourceData Source:=.Range(.Cells(i + 1, j + 1), .Cells(i + m, j + 1)), PlotBy:=xlColumns End With ActiveChart.ChartType = xlXYScatter ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Test point to the right" .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With ActiveWindow.Visible = False <----- question 2 above) want to move object to a specific location ActiveWindow.WindowState = xlNormal (will have to make Range a variable) ActiveWindow.WindowState = xlMaximized Range("A1").Select ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.Paste ActiveSheet.Shapes("Chart 3").IncrementLeft -123.75 <--- question 3 above) want to make object # variable ActiveSheet.Shapes("Chart 3").IncrementTop -8.25 j = j + 1 Wend Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 4
    - Special cell does not work I have one Excel file. In One sheet I have written 34898 formulas. I m using office 2000. but when I used SpecialCells method of Used Range of Excel Sheet, It just hangged out. The code is working fine, if i run from macro. But if I run the code from VB and if Application.Visible and Interactive mode is set to "FALSE", It is getting hanged. So plz suggest me possible solutions Thnxs, Viren Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 5
    - 2 Small Macro problems. Hi Keith. The problem is probably me; I don't know how to set the "save changes" parameters. Gary Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 6
    - iexplore.exe - Application Error (EVERY TIME I CLOSE IE7) :( I just installed Norton 360. I'm running on IE6. The same/similiar error message occurs. I checked the event viewer logs and provides the following explanation: The program could not load a driver because the program user doesn't have sufficient privileges to access the driver or because the drive is missing or corupt. To correct the problem, the article goes on to suggest these solutions: Ensure that the program user has sufficient privileges to access the directory in which the driver is installed. Reinstall the program to restore the driver to the correct location. Based on this information, I can guess what occured and a possible solution. The installation of Norton 360 caused a file or registry entry to become changed, deleted or corrupted. The other possible cause could be a bug/incompatability in the software. The solution may be to un-install and re-install Internet Explorer. I have yet to try this. Good Luck Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 7
    - Sharepoint alerts (email) not opening in Outlook 2007 (error)... Bryan Jeffries wrote: Interesting... do you have a virus scanner for your Exchange Server I am trying to figure out what has changed, because it used to work for me, and now it doesn't. The only thing I can think of is if the Exchange IT folks installed a scanner for the email that mucks with the headers, and Outlook doesn't like it. Here are some of my headers I thought was interesting (with sensitive data removed): Date: Mon, 12 Nov 2007 12:18:38 -0800 To: < me@server.com > Message-ID: < 3BD50098E401463AA228377848493927-{625C86EF-2D82-45C6-87F5-D5E1A7834A10}@server.com > X-AlertTitle: = utf-8 Q Test_Design_Specs=3A_All_Items_=28All_Changes=29 = X-AlertId: {A5D55072-12A2-4980-92D5-3D0F386FF60B}:{9BA10C36-9BE3-44E0-9813-C8D1B1F6FC0D} Content-Class: sharing Content-Transfer-Encoding: 8bit From: = utf-8 B SW5ub3ZhdGlvbiBUZXN0IFRlYW0gU2l0ZW== = < do_not_reply@server.com > X-AlertWebUrl: = utf-8 Q http=3A=2F=2Finnovation=2Ftest = X-AlertServerType: STS X-Sharing-Config-Url: stssync://sts/ ver=1.1&type=documents&cmd=add-folder&b ase-url=http%3A%2F%2Fserver%2Ftest&list-url=someList&guid= %7bF7D84FE8%2dDC89%2d47EB%2dAF80%2d1EC87BF9BB60%7d&site-name=wsssite&list-name=someList X-Sharing-Remote-Uid: = utf-8 Q {F7D84FE8=2DDC89=2D47EB=2DAF8=30=2D=31EC87BF9BB6=30} = Content-Type: text/html; charset="utf-8" Subject: You have successfully created an alert for 'someList' Reply-To: do_not_reply@server.com X-AlertWebSoap: = utf-8 Q http=3A=2F=2Fserver=2Ftest=2F=5Fvti=5Fbin=2Falerts=2Easmx = X-Sharing-WSSBaseUrl: = utf-8 Q http=3A=2F=2Fserver=2Ftest = MIME-Version: 1.0 X-Mailer: Windows SharePoint Services (version 3) X-imss-version: 2.049 X-imss-result: Passed X-imss-scanInfo: M: P L:N SM:0 X-imss-tmaseResult: TT:0 TS:0.0000 TC:00 TRN:0 TV:5.0.1023(15542.001) X-imss-scores: Clean:0.59771 C:2 M:3 S:5 R:5 X-imss-settings: Baseline:3 C:3 M:3 S:3 R:3 (0.5000 0.5000) Return-Path: do_not_reply@server.com X-OriginalArrivalTime: 12 Nov 2007 20:18:40.0451 (UTC) FILETIME=[36FEA130:01C82569] So your saying all clients cant receive email alerts In my case its only a few, so at least in mine, i dont think its our antivirus or even antispam programs.. We are using GFI v10 (antivirus) and GFI v12 (antispam) Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 8
    - SQL Express 2005 Expiration Thank you very much. This did help. Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 9
    - 0x62304390 referenced memory Hi..........i had the same problem. I also unistalled the Yahoo Toolbar and still had the problem. I found that there was still a Yahoo browser installed which i then uninstalled.....problem solved. Good luck Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 10
    - Execute a Macro at Print Event in a Mail Merged Document. (New instance named Letter1 gets creted) Hello All, I want to execute a macro on a mail merged document at a Print Event . Some Info reg my dev. My program is in SAP-ABAP. It transfers its data to word file in a mail merged manner. In this process it creates a new instance of a document and this is "Letter" document. Through ABAP i can print the whole "Letter Document" but not the partial print. So i write a macro (with the help of previous posts) which can do the partial printing with the data from ABAP. Now the problem is that the macro do not trigger when i am printing this "Letter Document". I am really stuck here. I have tried no of ways to trigger this macro automatically like writing it on Document Open, Writing the macro in Normal . dot file , but this doesn't workout. Kindly let me know if there is some other method through we can execute a macro on this document. Please bear with me if am asking silly question/s. Thank you, Vinod. Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 11
    - Obtain Unique Work Units via SQL Adamus, I am still getting 197 for total WorkUnits. Code Snippet SELECT DISTINCT Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units], 'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals], FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults] FROM WorkUnitsFaultsMainTBL WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")) And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt)); What about in line four in the code above where I am dividing with /Count([WorkUnit]),2) AS [Pct NoFaults] Could this be causing the WorkUnits field to be total records and not distinct WorkUnits Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 12
    - Creating and Editing Microsoft Access Reports using Visual Basic Hi, I am trying to create a report based on the selection from a combo box in a form. i have some data, of which one of the fields is callled "projects". the combo box selects selects a project, and i would like to be able to open a report showing the data that belongs to the specific project on the click of a button. i have written the code below, but the report is only ever blank. i can create a query based on the same sql statement, and it works fine. can someone help Dim thisDB As Database, strSql, projID As String, rpt As Report Set thisDB = CurrentDb projID = existingProjSelector.Value strSql = "SELECT * FROM MyTable WHERE ([PROJECT ID]='" & projID & "');" ' the following query code is commented out but works fine ' thisDB.CreateQueryDef "reportQry", strSql 'DoCmd.OpenQuery "reportQry" ' thisDB.QueryDefs.Delete "reportQry" ' why doesnt this work DoCmd.OpenReport "Report1", acViewPreview, , strSql i would also like to know now to change the way the data is grouped on the report etc using vb cheers, ben bennymacca6@hotmail.com.dontspamme Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 13
    - Exception Occured Can someone please help me with an error that keeps coming up on my computer. Details are: Line : 180 Error: Settings Closed is undefined An exception occured. Address is 00413E6C Runtime error occured in script. No debuggers registered. Unable to jit debug. Thanks Chris Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 14
    - Excel - Resize image to specified target cells' dimensions Dragon, no there is no reason other than I have been too busy to think. Thanks for pointing it out though, since I'm making other changes to the code I'll incorporate your suggestions as well. Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 15
    - How to create a menu & submenu ? Hello, I��m beginner on Access-VB. I try to create a menu and sub-menu programmatically. I can create a menu but not sub-menu. Could you help me Here my code. ------------------------------------------ Sub SubMenuCreation() Dim MyMenubar As CommandBar Dim NewMenu As CommandBarControl Dim MyNewMenu As CommandBarControl Dim NewMenuCtrl As CommandBarControl Set MyMenubar = CommandBars.ActiveMenuBar Set NewMenu = MyMenubar.Controls.Add(Type:=msoControlPopup, Before:=10, Temporary:=True) NewMenu.Caption = "Menu" If Mid$([Forms]![frmLogin]![Acces], 11, 1) = 1 Then Set NewMenuCtrl = NewMenu.Controls.Add(Type:=msoControlButton, Id:=1) NewMenuCtrl.Caption = "Menu-1" NewMenuCtrl.TooltipText = "Acces Menu-1" NewMenuCtrl.Style = msoButtonCaption NewMenuCtrl.OnAction = "M-OpenForm-Menu1" End If If Mid$([Forms]![frmLogin]![Acces], 12, 1) = 1 Then NewMenuCtrl.Caption = "Menu-2" NewMenuCtrl.TooltipText = "Acces Menu-2" NewMenuCtrl.Style = msoButtonCaption NewMenuCtrl.OnAction = "M-OpenForm-Menu2" End If �� How create Menu-2.1 below Menu-2 EndSub ------------------------------------------ Thank for all suggestion. Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro
  - 16
    - Making a Automatic Updatable Hyperlink List OK I got the code to work but All the Hyperlinks is Invalid i keep getting message " Reference not Valid" Am I doing something wrong Tag: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro

ronald cole

Actually, I need help coding the "running a macro" part. I have a spreadsheet with a macro called "snafu" on sheet2 that contains a private subroutine called "runme" tied to a button. I just need to script up something that opens the spreadsheet and runs that subroutine and closes the spreadsheet. Sounds simple, huh Six hours of googling and reading the Microsoft Excel Visual Basic Reference could not clue me! I don't think I'm stupid... but I've been wrong before. Here's what I tried:

Set objExcel = WScript.CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("fubar.xls")

' I don't know what to do here. I tried:
' objWorkbook.Sheets(2).Activate
' objExcel.Run("runme")
' but I got an error about not being able to find "runme".
' please clue me in! thanks!!

objWorkbook.Close
objExcel.Quit

Re: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro

bi-lya

Do you open Excel some other application Then move your "runme" in a some Module (not Sheet or Workbook or ClassModule) and make "runme" like Public.
You may not do "objWorkbook.Sheets(2).Activate" for it.

Re: need help writing a vbscript to open a spreadsheet and run a subroutine in a macro

Shasur

Have you tried

objExcel.Run "'fubar.xls'!runme"

(Assuming the macri runme is in fubar.xls)

Cheers
Shasur