Xml Integration: Use XPath To Point To A Tag In Which To Insert Text Using VBA
I'd like to send data from an excel sheet to an XML file that already exists. Is there a simple way to use XPath to point to a tag (in which I would want to insert text) using VBA?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Create Tag In VBA?
I found this really cool code that creates a tag cloud in Excel using VBA. It's far too complex for my understanding but I was hoping I could get some of the experts opinions on here about modification possibilities. I have attached the macro-enabled 2007 workbook. What I am trying to do is search through a list of keywords and determine each keyword's density within a list. The list for example could look like this: slow windows xp windows xp running slow windows xp computer [slow windows xp] [windows xp running slow] [windows xp computer] [computer running slow windows xp] "computer running slow windows xp" "slow windows xp" "windows xp running slow" "windows xp computer" You'll notice the characters " [ ] of which I would like to ignore when the cloud builds. The cloud would list the most dense keywords first, and gradually decrease to the least dense keywords. The macro code is inside the workbook but I'll list it here to:
View Replies!
View Related
Getting Web Page Source (HTML Or XML) In VBA
I would like to retrieve contents of a web page, be it HTML or XML, into VBA variable! Later, I would chop, cut, parse or extract the data I need. Both importing as XML or WebQueries is unsatisfactory for a certain number of pages I need. XML has bad schema, WebQuery tells me it can't find any data. I tried with WinHTTPRequest, but Excel gives me back error "undefined user type" in other words it doesn't recognize that object. Basically I want the source of web page to become a string in my VBA code. In other words that would be replication of funcionallity of
View Replies!
View Related
Creating A Numerical Integration Function
I'm a VERY basic user of Excel. I have a mathematics and programming background, but no knowledge or experience in VBA programming. I'm wishing to create a new function as a VBA custom function procedure to numerically intergate an expression (such as 3*X^3 + 2*X^2), defined by a worksheet formula (not by data points). The five parameters required by the function would be in the form of cell references which point to (1) the expression to be integrated (such as the above example), (2) the integrating variable (in this example, X), (3) the lower limit of X, (4) the upper limit of X, and (5) a tolerance value for termination. I've been trying to implement the Trapezoidal Method - but not a flicker of light so far!!. I understand the Formula property of a cell may be used to get the expression to be integrated into the VBA code as text. Then the Substitute function may be used to replace the variable of interest by a value, and the Evaluate method may be used to get the new value of the expression. I have no experience in implementing these steps. I've made extensive attempts to do the above, but without success so far. ("I started off with nothing, and still have most of it left"!). I'm hoping someone may be able to assist me, or else point me to a relevant area of the forum (or elsewhere), by demonstrating the methods and techniques needed to achieve the function I'm seeking. Perhaps it has all been done before, but I haven't located it.
View Replies!
View Related
Automatically Change The Point Name To Match The Point Name In Column
I have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes. Option Explicit Sub PointName() Dim Ws As Worksheet Dim Rng As Range, Cel As Range Set Ws = ActiveSheet Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp)) For Each Cel In Rng Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " " Ws.Columns("A:B").Copy Sheets.Add With ActiveSheet .Paste .Name = Trim(Cel) .Range("A1").Select End With Next Ws.Activate End Sub I turned it off for a while and when I turned it back on I am getting an error Unable to set the _Default property of the PivotItem class Debugger is highlighting Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
View Replies!
View Related
Copying Selections To Power Point With VBA
I am trying to write a routine (via two comboboxes) that will enable users to select a choice of charts from one of three possible worksheets and have the charts on the selected sheet copied to PowerPoint. My difficulties are these: a. I cannot determine how to pass the selections to Excel (see b. as well) b. In some instances my charts are actually groups of a chart and a picture. how I should reference these? They are obviously not ChartObjects: when I use the macro recorder all I get is a range.
View Replies!
View Related
Auto Numbering A Tag Name
I would like to know if there is a way to Auto number a text. I have a column with text tags (lets say Column B). These cells look at a specific cell (ex. A1) and see what text is written in it then copy the text into their own cells B1, B2, B3 and so on. So if cell A1 reports AAA then Column B cells become AAA all the way down. Now what I like to do is for column B cells look at A1, copy the text and add _01 infront of their copied text. so for Column B, B1 reports AAA_01, B2 is AAA_02, B3 is AAA_03 and so on
View Replies!
View Related
Sort The Data :: By TAG And PA Claim
I have a monumental amount of data that needs to be sorted. The data is sorted by TAG number and then by the claim type EG: PA or TA and so on. There are 8 possible claim types and Each TAG may have only 1 EG: PA or it may have PA, TA, BC and so on. The thing i need to do is sort the data firstly by TAG and PA claim, then instead of having the tag number again below the PA claim type, and having the same TAG number again and next column being a TA type claim, i want to have all CLAIM TYPES IN ONE ROW......
View Replies!
View Related
Formula To Change The Tag On The Other End
I am making several thousand wire tags for a water treatment plant which need to be installed at each end of the cable runs. What I have made so far is tags for one end of the run not the other. I am looking to create a formula to change the tag on the other end so that instead of it saying 1234 to 5678 it would now say 5678 to1234
View Replies!
View Related
Insert Text In Front Of Text OR Number - User Defined Format
I can't seem to make user-defined format that puts a text in front of a number and/or a text. Let's say I have A1: 13, A2: texttext A3: text7 and I want to format a lot of cells to "Ilike 13" / "Ilike texttext" / "Ilike text7"... ie add the same text in the front of the cell, no matter what the content is. I did manage it seperately, with "texttext" @ for text and "texttext" # for numbers, but what's the general one?
View Replies!
View Related
Set Control Visibility Based On Tag Property
I have a userform which uses the tag property of the frame to determine whether a frame is visible (and hence the controls that reside within the frame). A frame contains three combo boxes, and six text boxes. The tag property of the frame matches the number of frames that are visible on the form, so that if the user selects five frames, frames 1 through five become visible and for all other frames visible = false. A frame contains all the data for a single entry. There can be up to fifty frames/ entries that are visible on the form depending on the user selection. I want to use the visibility property to do two things: first the combo boxes are filled from an array after the user selects the number of frames (or entries). I only want to fill the comboboxes where the frame is visible. The second thing is that I have a function which uses the data from the text boxes and combo boxes as required arguments. Since these are required arguments, I will get a data mismatch error if I try to call it and the controls are empty. Therefore, I only want to call the function if the frame which houses the controls is visible. Public Sub Visible1(Entry As String) Dim ctrl As Control For Each ctrl In UserForm2.Controls If TypeName(ctrl) = "Frame" And ctrl.Tag <= Entry Then With ctrl .Visible = True End With Else If TypeName(ctrl) = "Frame" And ctrl.Tag > Entry Then With ctrl .Visible = False.............
View Replies!
View Related
Read & Write Mp3 File Tag Properties
The problem with the Write macro was that, although it did work, it used SendKeys which has to be slowed down considerably. Here is a new version of the Write macro that works as normal. I originally tried to use CDDBControl.dll version 1.2.0.51 which is widely available on the internet but found that I could only get it to change 1 file before crashing Excel. I have put the READ macro in the next message. Dim ws As Worksheet Dim FromRow As Long Dim LastRow As Long Dim FilesToChange As Integer ' number of files to change Dim FilesChanged As Integer ' number of files changed Dim MyFilePathName As String ' full path & file name Dim MyFileType As String ' mp3 wma etc. '- Dim id3 As Object Dim MyArtist As String Dim MyAlbum As String Dim MyGenre As String Dim MyTrack As String Dim MyTitle As String Sub WRITE_TO_EXPLORER() Application.Calculation = xlCalculationManual Set ws = ActiveSheet Set id3 = CreateObject("CDDBControlRoxio.CddbID3Tag") LastRow = ws.Range("A65536").End(xlUp).Row ' count worksheet rows FilesToChange = ws.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Count If FilesToChange = 0 Then MsgBox ("No files to change."): Exit Sub FilesChanged = 0........................
View Replies!
View Related
Name Of XML Map
I have an excel sheet I made by opening a XML file using the "view source" option. From there I made a map and build a macro off that. What I want to do is convert it to allow the user to select which XML file to load into the map. I found the following code at technet
View Replies!
View Related
XML Chart Not Displaying
I have an XML file which I can open in Excel as an XML table. When I try to insert a chart from any of the data, a chart appears but the data appears as the legend but not as any lines on the chart. The column headings, which would normally appear as the legend, do not appear. The chart itself is blank. Does anyone have any ideas of a way to solve this?
View Replies!
View Related
Import Xml Values
i have one button in my ExcelWorkbook.i have sample.xml in my "c:" i want to read that sample.xml and disply the datas in my Excel Workbook if i click the button. using VBA macro programing i want to do this.
View Replies!
View Related
Query An Xml Document With Ado
I was able to create a recordset of data and then save the recordset as an XML document. This XML document is aggregated data for a chart in Excel. I need to hit the XML with ADO into a recordset from my select statement... As you can see below, I think I found the Driver I need to use??? I am not sure that this is correct for Excel VBA ADO??? sSQL = "Select * From C:ADO.XML" When I open the recordset.open sSQL, cnDB the error I get is this: "The filename, directory name, or volumne label syntax is incorrect" Set GetXMLDB = New ADODB.Connection With GetXMLDB .Open "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;" .CursorLocation = adUseClient End With
View Replies!
View Related
Preserve Formatting After XML Import
I have programmed a button that allows the user to import XML data into my workbook via the following Sub Button5_Click() FileToOpen = Application. GetOpenFilename("XML files (*.xml), *.xml") If FileToOpen <> False Then ActiveWorkbook.XmlMaps("RouteDoc_Map").Import (FileToOpen) End If End Sub All my XML data comes in great to the associated mapped cells (on several worksheets) but the data that is coming in as a list erases the alternating white/gray conditional formating that I've applied via FORMULA IS =MOD(ROW(),2). How would I be able to preserve the alternating white/gray conditional formating, no matter how many rows are being imported via my button ?
View Replies!
View Related
Include XML Files In Workbook
My Excel VBA application needs to communicate with 2 XML files. Instead of compressing these XMLs with the Excel file in a Zip file, and send them to the client, I would like to include them inside the Excel file. This way, I will only send the .xls file (the 2 XMLs will be included inside).
View Replies!
View Related
Create Tables From XML With CSS
how to create tables from XML with CSS.Below are the codes.... <?xml version="1.0"?> <?xml-stylesheet type="text/css" href="feedrecipe.css"?> <recipe_summary> < percentages>Percentages</percentages> <percentages> <eggshells_heading>Eggshells</eggshells_heading> <eggshells>5%</eggshells> <wheat_heading>Wheat</wheat_heading> <wheat>60%</wheat> <sunflower_heading>Sunflower Seeds</sunflower_heading> <sunflower>5%</sunflower> <meat_heading>Meat and Bone Meal</meat_heading> <meat>10%</meat>..................................
View Replies!
View Related
Winamp XML Printout To Xl Table/access?
I suffered a very annoying HDD failure and lost loads of music. The only record I have is the winamp media library which I could only export to .xml. From there, I've been unable to do anything remotely useful with it (access would have been best, I suppose) so have resorted to trusty Excel. Even then I've underestimated the task at hand: I've now got 9 excel files, each 65536 rows long, with the xml data streaming down them. Can anyone think how I could get a macro to 1. run through the list(s), line by line, deleting any line that had (e.g.) Track ID OR Genre OR etc... In order to remove all but the 5 categories I have in bold below, i.e. name, artist, album, date, folder....
View Replies!
View Related
Load XML With Msxml.dll References
We've developed some Excel spreadsheets which load in XML data from the web and then parses through it. Dim oDoc As MSXML.DOMDocument Dim fSuccess As Boolean Dim oRoot As MSXML.IXMLDOMNode Dim oTimeProperty As MSXML.IXMLDOMNode Dim oAttributes As MSXML.IXMLDOMNamedNodeMap Dim oChildren As MSXML.IXMLDOMNodeList Dim oChild As MSXML.IXMLDOMNode Dim oOpChildren As MSXML.IXMLDOMNodeList Dim oOpChild As MSXML.IXMLDOMNode To do this we add "Microsoft XML, version 2.0" in the " References" menu (which is from C:WindowsSystem32msxml.dll). This works fine on Windows XP and 2000 but Windows Vista doesn't have msxml.dll so it doesn't work. I've searched the web and found a page recommended using msxml6.dll as a direct alternative but unfortunatly the VBA script fails when implementing the MSXML object.
View Replies!
View Related
Insert Vba Code With Vba
I have a code that needs to be placed on any new worksheets created. All new worksheets are made using VBA code already so they just need to be told to add the code into each new sheet. How do I do this?
View Replies!
View Related
Importing Values From .xml File Into An Excel Row?
Basically, I have a column that contains a list of names of files without the extension, ie "A2=File1" What I want, is for excel to open the file associated with the name in the column, "(A2).xml", and extract a value from that.xml file and insert it into B2. I have searched and all of the examples I find aren't quite what I am looking for. I have an add-in that works exactly the way I want it, but does not extract certain values that I need it to. This is a commercial add-in so I don't have the source code for it or else this would be a snap.
View Replies!
View Related
Refresh XML Data In A Map If The Source File Is Updated
If I have a spreadsheet with some data loaded in through XML and viewed as a map, what is the VBA command to reload/reimport the source XML data? The scenario is that the source data (XML) may change at any time, and I would like to reflect this to the presentation layer (excel) at regular intervals. I have a loop set up but cannot find the command to reset the source XML data.
View Replies!
View Related
VBA To Insert Rows ...
I have file which is repeating, and i want to insert a row after the end of each repitition. here is my sample data from: 123sat 123sat 444mat 444mat 444mat 555abi to: 123sat 123sat new row insert 444mat 444mat 444mat new row insert 555abi I know it involves a macro and i dont know how. pls help as its a HUGE data and i need to automate it.
View Replies!
View Related
VBA To Insert Rows
I need to insert 2 blank rows within a spreadsheet above certain other rows that contains data that starts with a particular text string. Details: Column A has office codes in the form of "0301A", "0301B", etc. Column F will have billing codes that could start with either "L" or "ND" or "NF". Problem: I want to separate the L's from the ND's and also the NF's in column F based on which office they are billed to. End result: What I want to end up with are two blank rows in between each office code in column A, then another two blank rows between the L's, ND's and NF's.
View Replies!
View Related
Insert Username Using VBA
I'm trying to use the code listed below on the works shared drive. When I open up the worksheet and save it, my log on identity is left as required in Cell B2 etc.. When someone else uses the sheet it just leaves the company name in this cell and not their log on identity? The date & time function always works ok. Can anyone advise me if what I'm after is achievable. The code I've used below was used from a previous string on a similar question. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Now() Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Application.UserName End Sub[/b]
View Replies!
View Related
Insert New Row In Vba
I have extracted a portion of a record from the original file attached herein What I have been trying to do is to manually insert a new line after the last record of each country_id. and I need to the routine for about 3500 line items with over 130 country id instance.
View Replies!
View Related
Using VBA To Insert A Formula
I am wanting to use VBA code to insert the formula =IF(B8="","",VLOOKUP(B8,Information!C4:D200,2,FALSE)) into cell B9 (it is merged to form cell B9:B13). I have the formula copied into cell M4 on sheet 3 and have tried to reference it but can't seem to do it. I am happy either using VBA to reference it or using a VBA code to insert the text into cell B9 How can i get the formula from sheet 3 into cell B9 in Sheet 1, or simply put the text in.
View Replies!
View Related
Insert Formulas With Vba
I've got a large excel spreadsheet that is used for cost calculations and I need to be able to replace the formulas in three specific columns with the value of 0.01. I also need to be able to change the values back to their original formula. My problem is getting the formula back into the cell. The formula begins with "=IF(And" which seems to be where I'm getting most of my errors. So right now I run the code and the value of the cells in the three columns change to 0.01 and when I run it again I'm getting errors. Here is the formula. How should I be entering this in VBA to get this line into my cells? The row numbers will be changing as I go down, but the rest of the formula does not. =IF(AND(E48<>"",AH48<>0),(AH48/E48)*100,0)
View Replies!
View Related
Insert Formula With VBA
In the attached sample workbook, I am trying to insert the formula contained in "C2" down column "C" for each row of data. Is this possible using a Loop? Have I entered the code incorrectly?
View Replies!
View Related
Insert Text Into An Inputbox **
I have the following code that allows a user to type in an email address into an inputbox, and email a spreadsheet to the recipient in the input box. However, as 99% of the time this is going to be the same email address every time, can I populate the inputbox automatically with a given email address? for eg email@email.com vaRecipients = Application.InputBox("Please enter recipient's email address. Please ensure Lotus Notes is open before sending.", "Email Literature Request")
View Replies!
View Related
Macro To Insert Text
I need a macro that will insert text into merged cells. I have merged blocks where all will be exactly the same size one after another. (there are 19 blocks). The problem is that each month the size of the blocks change.(see sheet for example). Right now blocks are 8 rows will get to 16 by end of year. In each block I need a "- (city) (Name)" Each block has a different city and name that goes with it. It would be an awesome time saver If I could click a button and have the city and names be entered into the appropriate spot no matter what month were in.
View Replies!
View Related
Insert A Line Of Text
Simple question: Can an Excel Macro insert a line of boilerplate text under the last thing copied to a worksheet? I want to paste something into a worksheet, and add a message under it, and a blank line.
View Replies!
View Related
Sumif; Find The Amount Spent Based A A "tag"
I have a spreadsheet(see attached) that I am needing to find the amount spent based a a "tag" I have created. If you bring up the spreadsheet and look at the dashboard sheet, I am wanting to find the total tag amount based off what is listed in the paychesk sheet. For tests I have used this formula and have put it in B6 on the dashboard sheet. =SUMIF('PayCheck - DEC-09-B'!$F$2:$F$1000,A20,'PayCheck - DEC-09-B'!F$2:$G$1000) But the "bank amount" field on the paycheck sheet is a negative number the value of course coming back is not right. How do I fix this?
View Replies!
View Related
Some VBA Code To Insert Pictures
I am doing up a spreadsheet for work. I have a master sheet which has important 'feeder' info on it. I also have 6 other sheets where the code will reference. Here is the Data Entry sheet setup. ColK --> Contains numbers starting in row 2 and ending in row 116 atm, could expand but no further than 500 ColL --> Same as above but with different numbers ColM --> contains formulas which produce the sheet name that the data in ColK and ColL is related to (6 sheets) ---------- I require some code that will look at each row from 2 to 500 in ColM for something other than "out of range" or nothing (the "out of range" or empty ones should be ignored), and for each relevant row it should identify the sheet name in ColM and then identify the cell that is at the cross reference of the 2 numbers from ColK and ColL and then insert a picture at that cell. Each sheet (the 6 of them) has numbers in ColA from A2 and Row1 from B1. The numbers will exact match those from ColK and ColL from the Data Entry Sheet.
View Replies!
View Related
Insert Blank Cell In VBA
I've a sheet and in row A is a list of tasks to do like: A1 - Task1 A2 - Task2 A3 - ... Is it possible in VBA to do this: A1 - Task1 A2 - Blank cell A3 - Task3 A4 - Blank Cell A5 - .... in other words to input a balnk cell between tasks (text)
View Replies!
View Related
Vba: Insert Row And Paste As Value
I'm copying rows and pasting to a new worksheet but want to insert at the bottom not simply paste at the bottom of my destination table. If wsExists(ShtName) Then Set NextCl = Worksheets(ShtName). Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) cl.EntireRow.Copy Destination:=NextCl End If I've tried adding If wsExists(ShtName) Then Set NextCl = Worksheets(ShtName).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) NextCl.EntireRow.Insert cl.EntireRow.Copy Destination:=NextCl End If it adds rows but alternately and isnt correct.? I would also like to paste as Values not copy and paster the formulas
View Replies!
View Related
Insert Data Validation By Vba
I want to insert data validation to a cell by VBA. When i record the macro to put a list (value1;value2;value3) to a Cell the following code is generated and the validation works OK.(dropdown of the 3 values) Sub Validation_by_VBA() Range("A2").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="value1;value2;value3" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub If i run this code from VBA I have only one value in the dropdown (value1;value2;value3) How can i change this code so it will work from vba as well?
View Replies!
View Related
|
TRACKER
