Search Results

Search found 4901 results on 197 pages for 'excel macros'.

Page 19/197 | < Previous Page | 15 16 17 18 19 20 21 22 23 24 25 26  | Next Page >

  • How do you update an Excel file (Data Refresh and update formulas) WITHOUT opening the file?

    - by Alex
    I have an Excel file that want to update and save automatically with out having to open it or manually interact with. Manually, I open the file up and hit data refresh which goes and does a SQL query and then hit F9 for the formulas to update and then I just close/save. (I then would mail the file out to people using a perl script or use SAS JMP to run some numbers/charts and also mail them out. Basically I need to script some things which require the XLS file to be updated.)

    Read the article

  • Create text file named after a cell containing other cell data

    - by user143041
    I tried using the code below for the Excel program on my `Mac Mini using the OS X Version 10.7.2 and it keeps saying Error due to file name / path: (The Excel file I am creating is going to be a template with my formulas and macros installed which will be used over and over). Sub CreateFile() Do While Not IsEmpty(ActiveCell.Offset(0, 1)) MyFile = ActiveCell.Value & ".txt" fnum = FreeFile() Open MyFile For Output As fnum Print #fnum, ActiveCell.Offset(0, 1) & " " & ActiveCell.Offset(0, 2) Close #fnum ActiveCell.Offset(1, 0).Select Loop End Sub What Im trying to do: 1st Objective I would like to have the following data to be used to create a text file. A:A is what I need the name of the file to be. B:2 is the content I need in the text file. So, A2 - "repair-video-game-Glassboro-NJ-08028.txt" is the file name and B2 to be the content in the file. Next, A3 is the file name and B3 is the content for the file, etc. ONCE the content reads what is in cell A16 and B16 (length will vary), the file creation should stop, if not then I can delete the additional files created. This sheet will never change. Is there a way to establish the excel macro to always go to this sheet instead of have to select it with the mouse to identify the starting point? 2nd Objective I would like to have the following data to be used to create a text file. A:1 is what I need the name of the file to be. B:B is the content I want in the file. So, A2 - is the file name "geo-sitemap.xml" and B:B to be the content in the file (ignore the .xml file extension in the photo). ONCE the content cell reads what is in cell "B16" (length will vary), the file creation should stop, if not then I can adjust the cells that have need content (formulated content you see in the image is preset for 500 rows). This sheet will never change. Is there a way to establish the excel macro to always go to this sheet instead of have to select it with the mouse to identify the starting point? I can Provide the content in the cells that are filled in by excel formulas that are not not to be included in the .txt files. It is ok if it is not possible. I can delete the extra cells that are not populated (based on the data sheet). Please let me know if you need any more additional information or clarity and I will be happy to provide it.

    Read the article

  • Excel - Possible to create a sorted view of a column in one sheet on another sheet?

    - by Cumbayah
    Hi; I'm trying, in Excel 2007, to populate a column in one sheet with the data contained in a column on another sheet, so that I may provide another sorting on the data, related to that sheet only. I've tried to boil it down to being able to have a column on sheet2 automatically being populated with all rows from a column in sheet1, but I can't seem to do so. Any suggestions? Thanks in advance.

    Read the article

  • Excel VBA - How to copy/transpose multiple columns record into individual single row

    - by larry
    I'm working on data migration, need some help on doing a macro to copy/transpose multiple columns record into individual single row. There are also a "tag" in the first row, which indicates the columns that should not be included in the copy/transpose. From: Tag x Name Jan Feb Mar Apr Larry 2 20 34 56 Harry 3 45 77 88 Marry 5 66 44 33 To: Larry Jan 2 Larry Feb 20 Larry Apr 56 Harry Jan 3 Harry Feb 45 Harry Apr 88 Marry Jan 5 Marry Feb 66 Marry Apr 33 The "Mar" data was omitted due to there's a tag (X) above it. The data might be near hundred columns (few years), and some of the months need to be omitted. Any expert able to help on this? I had been spending whole day cracking my head on this. Worse come to worse I might have to manually copy and paste, that would probably took me a decade.

    Read the article

  • how to get data in sheet2 from sheet1 in excel

    - by romen-leung
    I have two worksheets, Sheet1 Column A = Deptname Column B = Headname Column C = Username Sheet2 Column A = Headname (???) Column B = Username Column C = UserID "Headname" column in Sheet2 is blank and what I wanted to do is to get "Headname" from Sheet1 by using "Username". I have tried to use VLookup but it did not work if username in Sheet1 and Sheet2 is not exactly same. E.G, given two differents Username as shown on below. Username in Sheet1 is "Jenny Oh" and "Chan Shu Mei" Username in Sheet2 is "ITC - Jenny Ong" and "IA: Chan Shu Mei" Any ideas whether it can be done? Thankf in advance for any help.

    Read the article

  • Excel Match Bug in a sparse range with duplicate keys

    - by DangerMouse
    Data in TheRange is {1,"",1,"",1,"",1,"",2} =Match(2, TheRange, 1) returns 9 as expected =Match(1.5, TheRange, 1) returns 7 as expected =Match(1, TheRange, 1) returns 5 which is not expected Anyone come across this ? Anyone got a fix? Additionally if I use Worksheet.Function.Match in VBA I get more unexpected results.

    Read the article

  • Excel Prorated SUMIF

    - by Pete Michaud
    I have a worksheet with 2 columns, one is a dollar amount, and the other is a day of the month (1 through 31) that the dollar amount is due by (the dollars are income streams). So, I use the following formula to SUM all the income streams due on or before a certain day: =SUMIF(C5:C14, "<="&$B$42,B5:B14) Column C is the due day B42 is the cell in which I input the day to compare to like "15" for "total of all income due on or before the 15th" - the idea is to have a sum of all income received for the period. Column B is the dollar amount for each income stream. My question is: Some of the income streams don't have a day next to them (the day cell in column C is blank). That means that that income stream doesn't come in as a check or a chunk on a certain date, it trickles in roughly evenly through out the month. So if the amount for the income stream is $10,000 and the day is 15 in a 30 day month, then I should add $5,000 to the total. That would be something like: =SUMIF(C5:C14, "",???) So where the due date is blank, select ???. ??? isn't just the number, it's the number*(given_day/total_days_in_month). So I think what I need for an accurate total is: =SUMIF(C5:C14, "<="&$B$42,B5:B14) + SUMIF(C5:C14, "",???) But I'm not sure how to write that exactly.

    Read the article

  • excel formula problem

    - by Santosh
    Kindly check the below written formula, if the formula is not correct then please provide me the correct one that is related to this. IF(A1:AV7000=F1,vlookup(f2,B1:C7000,2,0),0) As I have tried above written formulas, but it's not working.

    Read the article

  • Excel 2007 - Conditional Currency format

    - by glinch
    Hi there, Is it possible to set the currency of a column (£ / € / $) dependent on the value of a cell? Eg, If i determine that the address im using in the workbook is America the workbooks currency will be set to dollars. This would save the time of having multiple spreadsheets for different currencies. Thanks in advance to anyone who can help. Noel

    Read the article

  • excel 2007 vba: how to refer to HPageBreaks

    - by notnot
    I'm trying to write a macro which can look into the list of horizontal page breaks that a worksheet keeps, and it seems like HPageBreaks should be exactly that. I can add or remove page breaks from it, but I can't seem to isolate the collection itself to look at its contents. Even adding a watch and looking at ActiveSheet.HPageBreaks just brings up a generic looking object with a count field equal to 0 regardless of existing page breaks. I'm really confused about this now. Is there any way to look into the existing page breaks within a sheet? A listing of what rows they occur on/between would be great.

    Read the article

  • VBA Excel macro: use Range to act on a different worksheet

    - by David Oneill
    I am very much a beginner when it comes to VBA programming. I have a Macro that hides or shows columns based on the value in one cell: Sub HideColumnsMacro() Range("b8:o8").EntireColumn.Hidden = False v1 = Range("b2").Value + 1 If v1 < 12 Then With Range("b8") Range(.Offset(0,v1), .Offset(0, 12)).EntireColumn.Hidden = True End With End If End Sub I want to be able to get this same functionality when I change a cell on a different sheet. Is there a way I can tell this Macro to act on this sheet, when it is run from a different one?

    Read the article

  • Passing a variable from Excel 2007 Custom Task Pane to Hosted PowerShell

    - by Uros Calakovic
    I am testing PowerShell hosting using C#. Here is a console application that works: using System; using System.Collections; using System.Collections.Generic; using System.Collections.ObjectModel; using System.Management.Automation; using System.Management.Automation.Runspaces; using Microsoft.Office.Interop.Excel; namespace ConsoleApplication3 { class Program { static void Main() { Application app = new Application(); app.Visible = true; app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); Runspace runspace = RunspaceFactory.CreateRunspace(); runspace.Open(); runspace.SessionStateProxy.SetVariable("Application", app); Pipeline pipeline = runspace.CreatePipeline("$Application"); Collection<PSObject> results = null; try { results = pipeline.Invoke(); foreach (PSObject pob in results) { Console.WriteLine(pob); } } catch (RuntimeException re) { Console.WriteLine(re.GetType().Name); Console.WriteLine(re.Message); } } } } I first create an Excel.Application instance and pass it to the hosted PowerShell instance as a varible named $Application. This works and I can use this variable as if Excel.Application was created from within PowerShell. I next created an Excel addin using VS 2008 and added a user control with two text boxes and a button to the addin (the user control appears as a custom task pane when Excel starts). The idea was this: when I click the button a hosted PowerShell instance is created and I can pass to it the current Excel.Application instance as a variable, just like in the first sample, so I can use this variable to automate Excel from PowerShell (one text box would be used for input and the other one for output. Here is the code: using System; using System.Windows.Forms; using System.Management.Automation; using System.Management.Automation.Runspaces; using System.Collections.ObjectModel; using Microsoft.Office.Interop.Excel; namespace POSHAddin { public partial class POSHControl : UserControl { public POSHControl() { InitializeComponent(); } private void btnRun_Click(object sender, EventArgs e) { txtOutput.Clear(); Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application; Runspace runspace = RunspaceFactory.CreateRunspace(); runspace.Open(); runspace.SessionStateProxy.SetVariable("Application", app); Pipeline pipeline = runspace.CreatePipeline( "$Application | Get-Member | Out-String"); app.ActiveCell.Value2 = "Test"; Collection<PSObject> results = null; try { results = pipeline.Invoke(); foreach (PSObject pob in results) { txtOutput.Text += pob.ToString() + "-"; } } catch (RuntimeException re) { txtOutput.Text += re.GetType().Name; txtOutput.Text += re.Message; } } } } The code is similar to the first sample, except that the current Excel.Application instance is available to the addin via Globals.ThisAddIn.Application (VSTO generated) and I can see that it is really a Microsoft.Office.Interop.Excel.Application instance because I can use things like app.ActiveCell.Value2 = "Test" (this actually puts the text into the active cell). But when I pass the Excel.Application instance to the PowerShell instance what gets there is an instance of System.__ComObject and I can't figure out how to cast it to Excel.Application. When I examine the variable from PowerShell using $Application | Get-Member this is the output I get in the second text box: TypeName: System.__ComObject Name MemberType Definition ---- ---------- ---------- CreateObjRef Method System.Runtime.Remoting.ObjRef CreateObj... Equals Method System.Boolean Equals(Object obj) GetHashCode Method System.Int32 GetHashCode() GetLifetimeService Method System.Object GetLifetimeService() GetType Method System.Type GetType() InitializeLifetimeService Method System.Object InitializeLifetimeService() ToString Method System.String ToString() My question is how can I pass an instance of Microsoft.Office.Interop.Excel.Application from a VSTO generated Excel 2007 addin to a hosted PowerShell instance, so I can manipulate it from PowerShell? (I have previously posted the question in the Microsoft C# forum without an answer)

    Read the article

  • Excel VBA Macro for Pivot Table with Dynamic Data Range

    - by John Ziebro
    CODE IS WORKING! THANKS FOR THE HELP! I am attempting to create a dynamic pivot table that will work on data that varies in the number of rows. Currently, I have 28,300 rows, but this may change daily. Example of data format as follows: Case Number Branch Driver 1342 NYC Bob 4532 PHL Jim 7391 CIN John 8251 SAN John 7211 SAN Mary 9121 CLE John 7424 CIN John Example of finished table: Driver NYC PHL CIN SAN CLE Bob 1 0 0 0 0 Jim 0 1 0 0 0 John 0 0 2 1 1 Mary 0 0 0 1 0 Code as follows: Sub CreateSummaryReportUsingPivot() ' Use a Pivot Table to create a static summary report ' with model going down the rows and regions across Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long Dim FinalCol As Long Set WSD = Worksheets("PivotTable") 'Name active worksheet as "PivotTable" ActiveSheet.Name = "PivotTable" ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT ' Define input area and set up a Pivot Cache FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row FinalCol = WSD.Cells(1, Application.Columns.Count). _ End(xlToLeft).Column Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _ xlDatabase, SourceData:=PRange) ' Create the Pivot Table from the Pivot Cache Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _ Cells(2, FinalCol + 2), TableName:="PivotTable1") ' Turn off updating while building the table PT.ManualUpdate = True ' Set up the row fields PT.AddFields RowFields:="Driver", ColumnFields:="Branch" ' Set up the data fields With PT.PivotFields("Case Number") .Orientation = xlDataField .Function = xlCount .Position = 1 End With With PT .ColumnGrand = False .RowGrand = False .NullString = "0" End With ' Calc the pivot table PT.ManualUpdate = False PT.ManualUpdate = True End Sub

    Read the article

  • Excel 2007 Visual Basic Editor: eats spaces, throws cursor around

    - by Vincent
    I can't resolve this issue, I found a similar question here but: setting the workbook to Manual calculation (alt-m-x-m or alt-t-oformulas) didn't work Setting editor options to disable: Auto syntax check & Background compile didn't work anybody have any idea how to fix this very annoying behaviour, I'm used to quickly pop up VBA (alt-f11), f7 to get into code and write some quick procedures there... and it's hard to get out of that habit, I don't want to write any office extension to just add a single quote to every cell in the range For Each rg In Selection rg = chr(39) & rg.value Next F5, done...

    Read the article

  • Excel VBA - export to UTF-8

    - by Tom
    The macro I created works fine, I just need to sort out the saving business. Now I get a popup asking me where to save it, but I would like it to save it under a default name and path AND encoded in UTF-8. This is my full code I use, the bottom part saves the document I presume. Public Sub ExportToTextFile(FName As String, Sep As String, SelectionOnly As Boolean, AppendData As Boolean) Dim WholeLine As String Dim fnum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Dim teller As Integer 'Teller aangemaakt ter controle voor het aantal velden 'teller = 1 Application.ScreenUpdating = False On Error GoTo EndMacro: fnum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).Row StartCol = .Cells(26).Column EndRow = .Cells(.Cells.Count).Row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).Row StartCol = .Cells(26).Column EndRow = .Cells(.Cells.Count).Row EndCol = .Cells(26).Column End With End If If AppendData = True Then Open FName For Append Access Write As #fnum Else Open FName For Output Access Write As #fnum End If For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = "" Else CellValue = Cells(RowNdx, ColNdx).Value End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #fnum, WholeLine, "" 'Print #fnum, teller, WholeLine, "" 'teller = teller + 1 Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #fnum End Sub Sub Dump4Mini() Dim FileName As Variant Dim Sep As String FileName = Application.GetSaveAsFilename(InitialFileName:=Blank, filefilter:="Text (*.txt),*.txt") If FileName = False Then Exit Sub End If Sep = "|" If Sep = vbNullString Then Exit Sub End If Debug.Print "FileName: " & FileName, "Separator: " & Sep ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), SelectionOnly:=False, AppendData:=False End Sub

    Read the article

  • Selecting multiple columns and rows for formatting - Excel

    - by Joyce
    I have a report which I used the command subtotals. Aesthetically, I just want to make these subtotal rows (columns A to P) filled with color, be in Bold and have a surrounding border. There are hundreds of totals generated in my report. And they do not have a recurring row position. So basically in order for it to look good, I do it manually per row. Is there a faster way? Thanks!

    Read the article

  • Excel Question: I need a date and time formula to convert between time zones

    - by Harold Nottingham
    Hello, I am trying to find a way to calculate a duration in days between my, time zone (Central), and (Pacific; Mountain; Eastern). Just do not know where to start. My criteria would be as follows: Cell C5:C100 would be the timestamps in this format:3/18/2010 23:45 but for different dates and times. Cell D5:D100 would be the corresponding timezone in text form: Pacific; Mountain; Eastern; Central. Cell F5 would be where the duration in days would need to be. Just not sure how to write the formula to give me what I am looking for. I appreciate any assistance in advance. Thanks

    Read the article

  • Open an Access database and run one of its Macros from Excel

    - by sqlnoob
    From Excel, I need to open an Access database and run one of the database's macros. I'm using Excel and Access 2007. Here is my code in Excel: Sub accessMacro() Dim appAccess As New Access.Application Set appAccess = Access.Application appAccess.OpenCurrentDatabase "C:\blah.mdb" appAccess.Visible = True appAccess.DoCmd.RunMacro "RunQueries.RunQueries" appAccess.CloseCurrentDatabase End Sub In the Access Database, there is a procedure named RunQueries in a module named RunQueries. Each time I run this, I get the following error: Runtime error '2485': Microsoft Access Office can't find the object 'RunQueries.' I have also tried: appAccess.DoCmd.RunMacro "RunQueries" and I get the same errors message. Any idea how to do this? By the way, I could go into a long explanation about why I need to do this, but let me just say that I've already argued against it, and I have to do it this way (meaning, I have to use Excel as a frontend to open several Access dbs and run their macros).

    Read the article

  • Excel VBA / SQL Union

    - by Edge
    Hi, I am trying to Join 2 seperate columns from 2 different sheets to make a longer column from which i can then use a Vlookup from. Sheet1 A, B, C, D, E, F, G Sheet2 A, B, C, D, E, F, G I want to Join(Union) Columns B from sheet1 and C from sheet2 together and find the Distinct values of the new list. I have been working on this for weeks. Thanks

    Read the article

  • Export sheet from Excel to CSV

    - by Mike Wills
    I am creating a spread sheet to help ease the entry of data into one of our systems. They are entering inventory items into this spread sheet to calculate the unit cost of the item (item cost + tax + S&H). The software we purchased cannot do this. Aan invoice can have one or more lines (duh!) and I calculate the final unit cost. This is working fine. I then want to take that data and create a CSV from that so they can load it into our inventory system. I currently have a second tab that is laid out like I want the CSV, and I do an equal cell (=Sheet!A3) to get the values on the "export sheet". The problem is when they save this to a CSV, there are many blank lines that need to be deleted before they can upload it. I want a file that only contains the data that is needed. I am sure this could be done in VBA, but I don't know where to start or know how to search for an example to start. Any direction or other options would be appreciated.

    Read the article

  • vba excel: do something every time a certain variable is changed

    - by every_answer_gets_a_point
    im doing a bunch of stuff to the variable St For i = 1 To 30000 Randomize e1 = Rnd e2 = Rnd z1 = Sqr(-2 * Log(e1)) * Cos(2 * 3.14 * e2) z2 = Sqr(-2 * Log(e1)) * Sin(2 * 3.14 * e2) St = So * Exp((r - (sigma ^ 2) / 2) * T + sigma * Sqr(T) * z1) C = C + Application.WorksheetFunction.Max(St - K, 0) St = So * Exp((r - (sigma ^ 2) / 2) * T - sigma * Sqr(T) * z1) C = C + Application.WorksheetFunction.Max(St - K, 0) St = So * Exp((r - (sigma ^ 2) / 2) * T + sigma * Sqr(T) * z2) C = C + Application.WorksheetFunction.Max(St - K, 0) St = So * Exp((r - (sigma ^ 2) / 2) * T - sigma * Sqr(T) * z2) C = C + Application.WorksheetFunction.Max(St - K, 0) Next i how do i get notified every time the variable changes?

    Read the article

< Previous Page | 15 16 17 18 19 20 21 22 23 24 25 26  | Next Page >