Answer Questions

• Coach24 Erratic Performance using Workbooks.Open

  Using Excel 2003, I'm writing what should be a quick application using VBA to open a series of Excel files and perform nearly 800,000 calculations (sad, but true). Typically, I use the Workbooks.Open method, either preceeded by Application.GetOpenFilename so the user can select the file to open or without where the filename is generated programatically. I'm running into Error 1004 (application generated, no details) with this simple statement: Workbooks.Open Filename:=sStats, Origin:=xlWindows, AddToMru:=False If I paste the contents of sStats into the Excel file open dialog, the workbook opens. All similarly coded statements in this application (and every other time I've used it on other projects) work. I had the same probl ...Show All

• TJ2007 insert column after pivot table

  Using a VBA macro, I am creating a pivot table based on text read in from a tab delimited text file. After the pivot table is created, I need to add a column on the worksheet after the pivot table. Is this possible Thanks, Mike Hi Mike <<Using a VBA macro, I am creating a pivot table based on text read in from a tab delimited text file. After the pivot table is created, I need to add a column on the worksheet after the pivot table. Is this possible >> Since you still haven't gotten an answer here, try asking the Excel specialists in the Excel programming newsgroup, here: http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.ex cel.programming&lang=en&cr=US <http ...Show All

• Dottj worksheet_change and data validation

  Hi there I have an Excel spreadsheet in which one column has data validation set. So the cells in this column have drop-down lists with the pre-set allowed values. Now I want the cell in the neighboring column to be set to some default value when the user selects an item from the drop-down list. This seemed like it would be easy, using the Worksheet_Change event handler. When a cell in the first column is changed, it initiates a function which sets the value in the neighboring column to some default value: Target.Offset(0,1).Value = "default". The problem is that it doesn't work when using the drop-down list. If the user types in the value manually, or if a cell in the first column is deleted, it DOES fil ...Show All

• cconrad Dimensions in BSM

  Hi We are building a scorecard to report the sales. We need the rows to display the time, ie., by year, quarter and month. And the columns to display the autaul sales and its target values for a particular time dimension.My datasource is SSAS cubes. Is there a way by which I get the Actual and the target value for a year and by drilling it will I get the values for a quarter for a particular year, and by drilling through the particular quarter, will I get the values for the month of that particular year and quarter.I get all the values in the format that I wanted in my cube. Can I achieve the same using Business scorecard. I have created 3 KPI's. One for year, one for Quarter and another for Month. After adding them to my scorecard a ...Show All

• PeterVrenken opc

  I am trying to develop an opc - da client using .net technology. Have gone through the documents on opc and opc client and all, but am still not sure how to implement it, can someone give me a direction on how to start with it Post your code here to have a look at it, leandro Are you using a reference to COM OPC Altough the exception is very clear. When i try add an Opc group, i get te following error message: Opc Server add group failed with exception: Unable to cast object of type 'OpcAutomation.OpcServerClass' to type 'OPCAutomation.IOpcGroups' Does anyone help me Sorry , this one should work http://www.faweb.net/us/opc/sample_ ...Show All

• arkiboys Help with Excel VBA Code

  I am trying to format different spreadsheets. I need the data to be in the same cells for each worksheet. This data is in column A. I need to check to see if DBCS# ( ) is above Tour, and if so insert rows until Tour is in row 175, column A. The problem is that the number after DBCS# is never the same. How can I use an IF statement to make sure that the cell above Tour says DBCS# (whatever the number is) This is the If statement that I tried. If I put DBCS#46 in my IF statement, it will not work because the number after # isn't always 46. If (Range("A174:A174") = "DBCS#") And (Range("A175:A175") = "TOUR") Then GoTo label_177: Else: Do While (Range("A174:A174") <> "DBCS#") And (Range("A175:A175") <> "TOUR") Select ...Show All

• RichardCasey Validation with List drop down?

  Hi I want to add some simple validation to my Excel sheet but can not for the life of me remember how to do it! Basically i have a drop down list as in the image below and want so that when 'No' is selected, the user is forced to enter the reason in the next cell http://i102.photobucket.com/albums/m82/sc0ttb_2006/excel_list.jpg I can remember doing this before and used some VBA code but can not remember how i did it :( Please put me out of my misery! Thanks... Hi, Not sure how you can force enter but this make the cell for that row active. In what way did your other workbook force entry Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("C:C"), Target ...Show All

• nobugz Automatically attaching a txt file to a sent form

  I've got a form put together in Outlook 2k3 for our helpdesk. We use the Cerberus ticketing system, so having a form the customer can fill out and automatically e-mail to Cerberus just made sense. The only problem is, Cerberus doesn't see the fields from the form. (Click here to see a screenshot of the Form in question) There are two options I'm seeing that would fix this problem, unfortunately I'm not knowledgeable enough in VBA to do either. I'm hoping you all could help. Here's my current script for the page: Sub Sysinfo_Click Item.GetInspector.ModifiedFormPages("Message").Controls("IPaddress").Text = GetIP() Item.GetInspector.ModifiedFormPages("Message").Controls("Hostname").Text = WshNetw ...Show All

• RWF faxing through execl

  Hi. is it possible to fax a sheet from excel using vba i also would like to take the fax number from a cell. if yes would anyone please tell how to do this thank you ...Show All

• Zoinky Merry Christmas and Happy New Year

  This is the forum I've maybe spend the most time in, although not so much recently but that can't be helped. To everyone who posts here often enough to know my name, and to anyone else to happens to read this... have a great Christmas and New Year... and even if you don't celebrate them I hope you enjoy the holidays. Merry Christmas Derek Thanks for all your posts, here and on your Blog. Like many others I read the questions and answers on this forum to expand my knowledge of VBA. Your answers, and those of the other frequent posters have helped me enormously this year, thanks to you all. Keep posting and have a happy new year. Regards ADG I am new here and have only posted a few p ...Show All

• chunkai Need help with Winsocket control in Microsoft Access Database form.

  I'm trying to create a telnet session within my microsoft access form. I included the microsoft winsocket control and some text boxes to place the recieved data. The problem I'm having is I recieve data but it is all scrambled like yt%yy and it doesn't take any of my data I'm entering. The current telnet software I'm using is using VT101 emulation if that might have something to do with the scrambled info, but I dont want to use this software and Access. I would like to keep it all in one application if possible. I just need some help where I might be going wrong or any other idea's. Here is a sample of the code I'm using. Option Explicit Const EchoPort = 23 Private Sub cmdConnect_Click() Dim temp As String temp = ...Show All

• Paul Monaghan Attaching Contents of a Folder

  Hi all, Using a macro running on excel, I am trying to attach the contents of a folder in Outlook. The files contained in the folder change, so it is not as simple as writing the code to attach a specific file. I need it to attach all the files regardless of the file name. Is this possible Thanks in advance Hey Andy, When I tried to run your code I got this error message, " The specified module could not be found ". How do I fix it Best Regards Cathrine Andy I am using Outlook as email client. I have tried your solution, but it still not working. Cath Hi, ...Show All

• Ted12893 Argument syntax problem

  I have a problem with the syntax of my code. I first have a loop that checks if an object in the selection is a chart. If it is a chart then it passes that chart on to a sub as an argument. In that sub changes are made to the chart in therms of style, size etc. My code: Sub arrayLoop() Dim obj As Object For Each obj In Selection If TypeName(obj) = "ChartObject" Then Call linjeDiagramKnapp(obj.Chart) End If Next End Sub Sub linjeDiagramKnapp(argChart As Object) Dim mySize As Double With argChart.Chart .ApplyCustomType ChartType:=xlUserDefined,TypeName:="Standard" With argChart.Chart .Height = 150 .Width = 150 .Top = 100 .Left = 100 End With My problem is that I do not know how to write the part of the code referring ...Show All

• John Woodiwiss How do I...

  select the next row down at the end of my macro. I tried to do this recording a macro using the down arrow and it did not work. Any help is greatly appreciated. You need to provide more information ie how is your macro moving from cell to cell. At the end of your macro, where is the active cell etc. Selection.Offset(1,0).select could be one method but need to know more ChasAA Hello Sleeks, The selection.offset(1,0) needs to to have a bigger offset (ie the number of rows that are selected already) use the following lines: nbrRows = Selection.Rows.Count ' store the number of selected rows ActiveCell.Select ' select the first ...Show All

• coln Disable events in userform

  Is there a way to disable Userform events within Userform code I am trying to dynamically set scrollbar.max without triggering the Scrollbar_Change event procedure. Hello Joe, I was a bit curious and tried the following: Private Sub CommandButton1_Click() MsgBox ("Command Click") UserForm1.ScrollBar1.Width = 20 UserForm1.ScrollBar1.Max = 100 End Sub Private Sub ScrollBar1_Change() MsgBox ("In Scroll Bar Change") End Sub Private Sub UserForm_Initialize() MsgBox ("In Initialise") UserForm1.ScrollBar1.Max = 15 End Sub The only time the ScrollBar_Change event is triggered is if I clicked on the scroll bar, or the up/down arrows of the scroll bar. The command butto ...Show All