w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Excel 2010 .SetSourceData vba chart
You might try selecting the source range right before inserting the chart. Excel might start out using the correct range, so you don't need to define the source data. An alternative approach is to add one series at a time: Set ganttChart = ActiveSheet.ChartObjects.Add(100, 50, 200, 200) ' clean up first ' i declared as long For i = 1 to ganttChart.SeriesCollection.Count ganttChart.SeriesCollection(1).Delete Next ' rColumn declared as range For Each rColumn In Sheets("Projects").Range("V1:AI70").Columns With ganttChart.NewSeries .Values = rColumn .XValues = Sheets("Projects").Range("G1:G70") End With Next

Categories : Excel

3-D Surface Chart Bands in Excel 2010 - VBA to ctrl transparency
Instead of: With Selection.Format.Fill .Visible = msoTrue .ForeColor.RGB = RGB(255, 0, 0) .Transparency = 0 .Solid End With use this: With Selection.LegendKey.Format.Fill .Visible = msoTrue .ForeColor.RGB = RGB(255, 0, 0) .Transparency = 0.5 <-- Or whatever value of transparency you want .Solid End With This is based upon the approach recommended by G North in this post.

Categories : Excel

Excel 2010 VBA - Storing decimal in array and using to chart percentage of stored value
When you create a chart "normally" and add a data table, all you have to do is format the source cells, and the table follows. But the method you use means you don't have "source cells"; and as far as I was able to tell, Microsoft doesn't provide a method to modify the formatting of the data table when it's generated with your method. An "ugly workaround" is to create a hidden sheet, put the data there, and format it correctly. Point to this data when you create the data table, and the formatting will be correct. It would be nice if Microsoft provided the flexibility you are asking for... sigh

Categories : Vba

Transfering shape data from Visio 2010 to Excel 2010 for further manipulation using VBA
Yes it is possible. Here is some VBA code to create an Excel report from Visio. Just remember that Excel VBA and Visio VBA have properties with the same name so make sure you fully qualify the Excel reference. Otherwise VBA gets confused. Public Sub ExcelReport() Dim shpsObj As Visio.Shapes, shpObj As Visio.Shape Dim celObj1 As Visio.Cell, celObj2 As Visio.Cell Dim curShapeIndx As Integer Dim localCentx As Double, localCenty As Double, localCenty1 As Double Dim ShapesCnt As Integer, i As Integer Dim ShapeHeight As Visio.Cell, ShapeWidth As Visio.Cell Dim XlApp As Excel.Application Dim XlWrkbook As Excel.Workbook Dim XlSheet As Excel.Worksheet Set XlApp = CreateObject("excel.application") ' You may have to set Visible property to True if you want to see the application. XlApp.Visible =

Categories : Excel

How do you disable "Save and send" in Excel 2010 (in the File ribbon (called backstage in Office 2010)?
For Ribbon UI example/documentation from Microsoft, here. They will recommend you use the CustomUI Editor. Open your file (which must be an Excel 2007+ file extension like XLSX, XLSM, XLAM, etc) in the CustomUI Editor. Then use this XML in the editor: <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> <backstage> <tab idMso="TabShare" visible="false" enabled="false"> </tab> </backstage> </customUI> Save the file, and close the CustomUI Editor. Then, open your file. Go to the File/Backstage view and you should see that the Save and Send tab has been removed: Note: This has disabled the entire "Save & Send" tab from the backstage view. It should be possible using XML to fine-tune this, i.e., to enable the tab, bu

Categories : Vba

Importing Data from Outlook 2010 into Excel 2010
You can start with writing a macro to process an mail item. And setup Outlook Rule to pickup this type of email from Subject/Account then run the macro. Change sExcelFile, sRecordSheet, iC as you see fit. I have made assumptions. This Code below is for Outlook, please note you need a running Outlook all the time to have this automation. It should get you started half way. Note you need "Microsoft Excel x.0 Object Library" in your References. Public Sub Rules_WebSiteFormRecord(oMail As MailItem) Const sExcelFile As String = "C:TestRecord.xlsx" Const sRecordSheet As String = "Record" ' Worksheet name Dim oExcel As Excel.Application, oWB As Excel.Workbook, oWS As Excel.worksheet Dim arrTxt As Variant, oLine As Variant, iR As Long, iC As Long, bWrite As Boolean Set oEx

Categories : Excel

Excel 2003 XYZ Surface Chart like XY Line Chart
Since I'm not using Excel 2003 I fear this may be no help at all (unless perhaps to clarify your requirement) but if the chart shown is what you seek then it may be helpful to know that this was with the addition of series for 4 with Series values: as shown (rather than a lot of zeros in source data): Edit I prefer time on the x-axis but if required on the z-axis:

Categories : Excel

access 2010 getting max row in excel 2010
.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) is not returning a valid range in this instance. This meas the find is unsuccessful. That is why you are getting a type mismatch. To solve, do this: Dim rng As Excel.Range Set rng = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If Not rng Is Nothing Then lastRow = rng.Row Else 'ToDo - handle the error here EndIf

Categories : Excel

Railscasts Episode #362 - Exporting to Excel: How to avoid the warning message given by Excel 2010 when opening the file?
The xls file that is generated by the Railscasts application is actually an XML file in the old Excel 2003 XML format. Recent versions of Excel have a feature called Extension Hardening that generate this warning when the file format doesn't match the file extension: The alert is a new security feature in Excel 2007 called Extension Hardening, which ensures that the file content being opened matches the extension type specified in the shell command that is attempting to open the file. Because the MIME types listed above are associated with the .XLS extension, the file must be in XLS (BIFF8) file format to open without this warning prompt. In order to avoid this warning you will have to generate an Excel file in a format that matches the file extension. Editing the registry as sugge

Categories : Ruby On Rails

Excel 2010 VBA Copy Data from Multiple Excel Files
You could try wrapping all this in a DO...LOOP. If you don't know how many .csv files you'll be working with, just put in an error handler to quit once no more files are found. You can use string concatenation to make the filenames change: for example, using a variable called filenum, if filenum = 1 then filenum & ".csv" would be "1.csv," if filenum = 2 then filenum & ".csv" would be "2.csv," etc. For example: Public Sub testr() Dim filenum As Integer filenum = 1 On Error GoTo my_handler Do Windows(filenum & ".CSV").Activate 'rest of code goes here filenum = filenum + 1 Loop my_handler: MsgBox "All done." Exit Sub

Categories : Excel

Excel 2007 VBA code doesn't run under Excel 2010
As your error raised in: bperiod = InStr(bdash, Mid(Dfiles(ii, 1), ".")) Your problem is right there in Mid(Dfiles(ii, 1), ".") part: Mid Function (Visual Basic) Public Shared Function Mid( _ ByVal str As String, _ ByVal Start As Integer, _ Optional ByVal Length As Integer _ ) As String I think your code should be something like this: bperiod = InStr(bdash, Mid(Dfiles(ii, 1), bdash), "."))

Categories : Excel

Create Excel Chart within Excel File
Till now as the apache POI limitation is saying "You can not currently create charts. You can however create a chart in Excel, modify the chart data values using HSSF and write a new spreadsheet out. This is possible because POI attempts to keep existing records intact as far as possible". However in my case, I have created a chart manually on excel sheet using Named Ranges, and using java, I am updating named ranges as per my requirement. Since the chart is based on named ranges so it also get updated. For updation please check here

Categories : Java

Resize a chart before printing with Highcharts
For me it works fine, take a look: http://jsfiddle.net/Fusher/6hyfk/6/ Probably you are trying to set size in wrong place, some code would be useful (jsfiddle even better). $('#container').highcharts({ series: [{ data: [1, 2, 3] }] }); $("#b").click(function() { var chart = $('#container').highcharts(); chart.setSize(200,500, false); chart.print(); chart.setSize(600,500, false) });

Categories : Javascript

Saving and printing a google chart
The download was never recognized as a png - you always had to rename it manually. I tested the Nverba fork of that code, which updates the code to reflect the improvements in the Visualization API, and it functions in Chrome but not Firefox. Since the code used to work in Firefox, I assume that either an update to Firefox or the canvg library broke functionality in Firefox (there's nothing specific in the Visualization API that would cause functionality in Firefox to break). Since this is a highly browser-specific tool anyway, I would just recommend using Chrome to handle your chart-to-image conversion.

Categories : Javascript

Using the IF function in Excel 2010
Assuming a data setup similar to this: In cell F2 and copied down is this formula: =IF(D3="",COUNTIF(B:B,">="&TIME(,,D2)),COUNTIFS(B:$B,">="&TIME(,,D2),B:B,"<"&TIME(,,D3))) You could then create a chart from the data in columns D:F. Does that work for you?

Categories : Excel

Update Excel 2010 But No Row Changes
knowing how difficult it could be to find a solution to such mysterious issues I recommend you to post an extract of your excel file. Furthermore I need to know what happens in GetExcelConnectionStringByWrite(filePath). Because those items are abscent I can only give two common reasons: the HDR section in your connection string is not set to 'YES' and your provider doesn't know any header names the provider will check for the columns data type within the first eight rows -> does your sheet provide enough data values in those eight rows? Regards Jan

Categories : C#

VBA code in Excel 2010
I think the problem may lie in this section of code: With Worksheets("Share").Range("B2") .Offset(RowCount, 0).Value = Me.cboMembers.Value .Offset(RowCount, 0).Value = Me.cboMonth.Value .Offset(RowCount, 1).Value = Me.txtAmountPaid.Value End With you are copying the value from cboMembers into cell (rowCount + 2, 2). You then copy the value of cboMonth into the same cell - overwriting the value from cboMembers that had previously been put there. It's not 100% clear what you're trying to do but this code may work better: With Worksheets("Share").Range("B2") .Offset(RowCount, 0).Value = Me.cboMembers.Value .Offset(RowCount, 1).Value = Me.cboMonth.Value .Offset(RowCount, 2).Value = Me.txtAmountPaid.Value End With

Categories : Excel

Outlook 2010 GAL with Excel VBA
Give this a try. Although if you have tons and tons of entries in your GAL, it will take awhile to complete, and you may have to increase the 65000. Sub tgr() Dim appOL As Object Dim oGAL As Object Dim oContact As Object Dim oUser As Object Dim arrUsers(1 To 65000, 1 To 2) As String Dim UserIndex As Long Dim i As Long Set appOL = CreateObject("Outlook.Application") Set oGAL = appOL.GetNameSpace("MAPI").AddressLists("Global Address List").AddressEntries For i = 1 To oGAL.Count Set oContact = oGAL.Item(i) If oContact.AddressEntryUserType = 0 Then Set oUser = oContact.GetExchangeUser If Len(oUser.lastname) > 0 Then UserIndex = UserIndex + 1 arrUsers(UserIndex, 1) = oUser.Nam

Categories : Excel

How to set icon size in Outlook 2010 add in c#
If you use the old CommandBars based API, your button will appear on the Addins tab, which is probably not something that you want. Your add-in (on the low level) needs to implement the IRibbonExtensibility interface and return your customization as XML - see http://msdn.microsoft.com/en-us/library/office/bb226712(v=office.12).aspx

Categories : C#

Specific transpose in Excel 2010
You can do this with Excel formulas. Assuming that your data are in cells 'A2:D4' and that your results begin cell F2, the following OFFSET formulas will transform the data as you want: The first formula would begin in cell F2. This is the formula that gets and repeats the id: =OFFSET($A$1,INT((ROW(F2)-ROW($F$2))/3+1),0) The second formula would begin in cell G2. This one traverses the columns of the data: =OFFSET($B$1,F2,MOD(ROW(F2)-ROW($F$2),3)) Both would be copied down as far as would be needed to transpose all the data.

Categories : Excel

VBA & MS Excel 2010 Run-time error '13'
Have you set the value of i and paramerange to an whole number value? I can be 0 in your formular but Paramerange must be a positive whole number. You can check the different parts of the line to try to find the error. Sub TestLine1() Dim i As Integer Dim rating As Integer Dim Paramerange As Integer rating = 2 Paramerange = 13 i = 1 MsgBox(Worksheets("Model").Range("A" & i + 5).Value) MsgBox CStr(Worksheets("Model").Range("A" & i + 5).Value + rating * Worksheets("parameters").Range("E" & Paramerange).Value) End Sub

Categories : Excel

Excel 2010 pick a time
You could set up 2 cells (one for hour, one for minutes), and set up corresponding spin buttons and have each cycle through the hours and minutes as you desire. then a button to grab the result and place it where you want. Info on adding a spin button here

Categories : Excel

How to consume web API with authentication in Excel 2010
web service Authentication is not possible in Excel. reference from http://cwebbbi.wordpress.com/2012/07/31/using-the-webservice-function-in-excel-2013/

Categories : Asp Net Mvc

Is Excel Object Model changed in 2010?
Even if you could trace/capture, I don't think that would help you much. Based on the simplicity of the code, you would be better off remaking it from scratch. If it's something proprietary and very useful, you should probably consider having both versions installed at the same time, which has worked for me just fine.

Categories : Excel

I am getting an error Subscript Out of Range in Excel 2010
Sub RunCompare() compareSheets "Latest", "SFDC" End Sub 'Compares two sheets and colours yellow any cell in sheet2 that is not the same as in sheet 1 Sub compareSheets(sheet1 As String, sheet2 As String) Dim rCell1 As Range Dim rCell2 As Range Dim nDiffs As Long ' Using a long because Integer may one day be too small Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = ActiveWorkbook.Worksheets(sheet1) Set ws2 = ActiveWorkbook.Worksheets(sheet2) For Each rCell1 In ws1.UsedRange.Cells Set rCell2 = ws2.Range(rCell1.Address) If rCell1.Value <> rCell2.Value Then rCell2.Interior.Color = vbYellow nDiffs = nDiffs + 1 End If Next rCell1 Debug.Print nDiffs End Sub This should help you produce

Categories : Excel

create exit button in excel 2010
Excel 2003 allows you to create buttons on toolbars. That is the past; with Excel 2007 and following you need to use the Ribbon to create buttons. You can also customize the Quick Access Toolbar

Categories : Excel

VBA, Excel 2010, open internet explore in new tab
Assuming that IE is an object variable representing an instance of InternetExplorer.Application: Sub TestNewTabInIE() ' modified from code originally found at: 'http://www.ozgrid.com/forum/showthread.php?t=174692 Dim IE As Object Set IE = CreateObject("InternetExplorer.Application") With IE .Visible = True While .ReadyState <> 4 'READYSTATE_COMPLETE DoEvents Wend '# Navigate to one URL .Navigate "http://google.com" '# Navigate to another URL, in a new tab. .Navigate "Http://yahoo.com", CLng(2048) End With End Sub Sometimes it is hard to find answers if you don't know what question to ask. I find the answer using this google query: https://www.google.com/search?q=VBA+explorer+new+tab I always make sure my query has "VBA" in it, and a few word

Categories : Internet Explorer

Excel 2010 32-bit mscomctl.ocx reference missing
I think this is what solves your problem: http://social.technet.microsoft.com/Forums/office/en-US/5b241f09-6c76-4320-b302-4cb8224eab7f/controls-from-mscomctlocx-can-no-longer-be-used-in-excel-2010-32bit: Run CMD under administrator mode run "REGSVR32 MSCOMCTL.OCX" without the quote

Categories : Excel

Excel 2010 - change US dates to UK format
I'm assuming that the date you received is formatted as text and that simply formatting it as date is not changing anything. You can run the following formula on the date: =(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"-"&LEFT(A1,FIND("/",A1)-1)&"-"&RIGHT(A1,4))*1 If you get numbers, you just need to format it as dd/mm/yyyy and it should be good.

Categories : Excel

Excel 2010 VB Script – Highlight Row Issue
Your static rr variable is a Variant and will not have a default value of "". So, when you re-open the file, the cursor will be in the row it was in previously, and because rr is not equal to "" it will not remove the highlight from this line. (In fact, I'm not sure how it is removing the highlight currently.) Anyway, try: Static rr If IsEmpty(rr) Then rr = "" End If Alternatively, give rr the data-type of Integer or Long, which will assume a default value of 0.

Categories : Vb.Net

How to remove only 0 (Zero) values from column in excel 2010
This is not a great answer, but it should lead you to the proper one. I haven't written VBA in a solid minute so I can't recall the exact syntax, but here's some 'psudeo code' for you -- I know you can easily implement this in a VBA macro for all worksheet.rows if cell.value == 0 then cell.value = " " endif endfor Basically, have VBA run through each row. If a cell in that row is an integer and equal to zero, simply replace it with a " ". It won't be blank but it'll seem to be. I think there's also a property called cell.value is empty that might clear cell contents. Use the library in VBA, I'm sure there's something in there you can use. Alternatively, if this is a one time job, you can use a special filter. Just select filter from the ribbon and replace all 0 s by ro

Categories : Excel

Combining data from two tables in Excel 2010
You can run a VLOOKUP on the second column with a modified lookup value: Assuming that the big table is in Sheet2, the small one in Sheet1, there are no headers and the digits are both in column A of their respective sheet, put this in Sheet2, cell B1: =VLOOKUP(LEFT(A1, 4)*1, Sheet1!A:A, 1, 0) And drag the formula down till the end of the table. All those that have #N/A means there is no match in the smaller table. Otherwise, the matched digit will be returned. EDIT: To explain my edit, LEFT(A1, 4) returns a text value, to convert the digit back to a number, multiply by 1.

Categories : Excel

Excel 2010 find and replace hyperlinks
The old link is starting with File:///, you should also let your new link to a (network)drive location start with that: hLink.Address = Replace(hLink.Address, "File:///C:Users/username/AppData/roaming", _ "File:///O:BrisbaneBrisbane_GroupsOffices") Also I think your initial link looks strange, as when I create a link all slashes in the path are backward "": "File:///C:UsersusernameAppData oaming"

Categories : Excel

Separate words with commas in Excel 2010
Here's a somewhat different approach. Aside from the "less than 5" issue, it also deals with the "5 words with no space at the end" issue: =LEFT(A1,FIND("^",SUBSTITUTE(A1 & "^"," ","^",5))-1) EDIT 1: I just noticed the part about the leading "- ". My addition isn't very elegant, but it deals with it, and also TRIMS any trailing spaces: =TRIM(LEFT(IF(LEFT(A1,2)="- ",MID(A1,3,999),A1),FIND("^",SUBSTITUTE(IF(LEFT(A1,2)="- ",MID(A1,3,999),A1) & "^"," ","^",5))-1)) EDIT 2: Oh yeah, commas: =SUBSTITUTE(TRIM(LEFT(IF(LEFT(A1,2)="- ",MID(A1,3,999),A1),FIND("^",SUBSTITUTE(IF(LEFT(A1,2)="- ",MID(A1,3,999),A1) & "^"," ","^",5))-1))," ",",")

Categories : Excel

Issue in reading xml file in excel 2010
Excel natively has far, FAR better XML handling capabilities than VBA without MSXML. Read up on the XML Mapping feature.

Categories : Xml

Increasing Excel 2010 memory allocation
It sounds like you need a 64-bit version of excel. Running a 32-bit version of Java is difficult enough as it need a continuous region of memory. If you had a 64-bit version of excel, it would be able to use as much memory as you need. An alternative is to start a Java process and communicate with it via sockets. This means it will not be limited by excel, but what your OS can do for the process. e.g. it can be 64-bit even if excel is 32-bit.

Categories : Java

Datagrid to Excel in ASP.NET C# - Visual Studio 2010
Add following code after htmlwriter line `if (dtDetails.Rows.Count > 0) { for (int i = 0; i < gvProduction.HeaderRow.Cells.Count; i++) { gvProduction.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1"); } int j = 1; //This loop is used to apply stlye to cells based on particular row foreach (GridViewRow gvrow in gvProduction.Rows) { gvrow.BackColor = Color.White; if (j <= gvProduction.Rows.Count) { if (j % 2 != 0) { for (int k = 0; k < gvrow.Cells.Count; k++) {

Categories : C#

Calculate the Biweekly Pay-period in Excel 2010
If you have a date in A1 then you can return the next payday on or after that date with this formula =CEILING(A1+1,14)-1 Assuming paydays are every 2 weeks with one payday being on Friday 26 July 2013 If it's the "alternate" Fridays then switch to this version =CEILING(A1+8,14)-8 Assumes you are using default 1900 date system

Categories : Excel

import from excel error in datagridview vb.net 2010
replaced connection string Dim conExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & txtPath.Text & "';Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""")

Categories : Vb.Net

VLookup and drag and drop using vba in excel 2010
Lets say Cell B1 contains the formula you want to AutoFill you could do something like: Range("B1").AutoFill Destination:=Range(Cells(1, 2), Cells(ActiveSheet.UsedRange.Rows.Count, 2)) This will fill the formula in B1 to the end of data in column B. If you have many formulas that you need to AutoFill at once you can use the same method but change the range values like this: Range("B1:C1").AutoFill Destination:=Range(Cells(1, 2), Cells(ActiveSheet.UsedRange.Rows.Count, 3))

Categories : Vba



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.