How To Import XML Identifying Certain Tags / Headers
Aug 17, 2010
I am about to re write my vba project as I can see that I need to make it more robust. One of the main issues I have is that the source XML file is not controlled by me. The quality and content is consistent but sometimes the order of the columns is different. At the moment that totally throws out my macro as it relies on certain columns for certain functions.
Trying to nut out the best way to import an XML file, keep only the specific columns I want - by content not location so then all follow on formula's will work and reference the correct data.
I have one theory that I can't get going. I thought if using defined names based on column headers this would allow me to keep and sort the columns I want. I can't find any examples to support this. I have been looking at the advanced filter but most examples reference exact cells which isn't flexible and leaves open to wrong data being pulled. Since I can't control the source doc what can I do?
I have a nice little code which imports data from a mastersheet to my input sheet. Though it will only work as long as the mastersheet is static.
I'm trying to match the product code in column A between my mastersheet and my workbook with a input sheet. And then copy the cell value in column D and E of the relevant row.
The messy part is that some of the products are split into sub categories (less than :$$$:, between :$$$: etc) and they dont have any info in column A.
I'm unsure if this is doable. Is it? And if I get permission to edit the mastersheet somewhat.
I tossed in the start of my currect static c/p, thought I don't think I'm keeping it if I get a handle on this match macro issue.
Code: Dim ws As Worksheet, wb As Workbook, t As Date, wb1 As Workbook, wb2 As Workbook, wbName As String, janei As String, spm As String Dim fil Set wb1 = ActiveWorkbook ChDir ""
I have a userform with two listboxes in them, i have set the columns in the listbox to 9 and would like to populate the column headers in the listboxes with the column header of one of the sheets.
I am building an application through Excel to update specific internal website information. My question is, is there an easier way to identify and view the tags on a web page without having to right-click and "view source"?
Name of player Score of round bag tag the player came in with bag tag the player is leaving with
Here is what I'm trying to accomplish. I usually just copy and paste (then sort the data) but I want a spreadsheet that I can enter raw data and just look at the results I will already have the players names in my sheet. Before the round I'll get everyone's tags it will not always be in order (EX: 1,3,15,22,34,66...etc) some tags will always be missing. After the round I'll enter the scores. What I can't figure out is how to leave my sheet in alphabetical order and have my spreadsheet populate the "leaving tag" column with the correct tag based on the players score....
name score tagin tagout bob 22 2 2 carl 44 15 23 ed 33 23 15
Can't tell if that translated, couldn't use a table....
basically I would like to leave column A where it is the whole time add the data to column B and C The use the data from column B to sort the values from column C numerically to column D (basically copy and rank then display the numbers in order). To throw in a twist if the data in column B is the same for two or more people the formula would need to take the lower number from column C to determine who gets the lower tag in column D.
having to create some kind of database in access....
I have a pie chart, and the value's are based off of a refreshable query. How can I set the pie chart tags to, for example, =C14 and then the next tag =C15?
I know you can go to source data, and select the cells that you want the pie chart to display, but the problem with that, is when the data is refreshed the data in the cells refresh, whereas the pie chart tags do no.
Finding a way to tell excel to pull Comments and Tags from files. I pulled the following code from another source:
VB:
'Force the explicit delcaration of variables Option Explicit Sub ListFiles()
'Declare the variables Dim objFSO As Scripting.FileSystemObject Dim objTopFolder As Scripting.Folder
[Code] ....
I have been using it to get the Name, Size, Type, and relevant Dates associated with files in a folder. However, this doesn't seem to work with finding Comments, Tags, etc.
I have found functions that are used to pull Comments and Tags from files but haven't been able to incorporate them in to the code I already have.
I'm trying to figure out a macro that will add b and /b tags around bold text. Bolded text can appear multiple times within a cell. I need it to run on all cells within a sheet.
So I need it to: 1. Search for bold strings within cells 2. Identify the length of the bolded strings 3. Add b and /b at the beginning and end of those strings 4. Repeat for all active cells
The only macro I was able to work out adds tags to only the last bolded string, whereas I need the tags on several non-continuous strings.
I have multiple excel sheets wherein the content has bold, italic, underlined, normal text. I want to replace such texts with relevant html tags.Example is below
Identify the adjective in the following sentence. Can you please pass me the black pepper?
should change to
Identify the <b>adjective</b> in the following sentence. <i>Can you please pass me the black pepper?</i>
Any way via VBA to remove HTML tags from a string that has been imported into a cell?
For example, if the cell contained(bearing in mind I can't actually use HTML tags in this post...
"LeftPointyBracket" B "RightPointyBracket" CatDog "LeftPointyBracket" I "RightPointyBracket" Elephant "LeftPointyBracket" B/ "RightPointyBracket"I would need CatDogElephant returning. So, any pointy brackets and anything between pairs of pointy brackets needs to be removed.
Basically I have a large database of folders of examples of projects/notes etc, each with essentially lots of potential topic "tags". I want to sort them to be able to tell the macro what topic I am looking for and it give me the folders that contain that info. I will need to tag each folder individually (which is no problem). I am looking for a piece of script (that either exists or that I can write) that will do the following:
- tag specific folders with numerous "tags". The folders will all be stored in a database. The tags will be user input.
- sort these folders based on specified tags and show filepath/location/name/file number or something unique based on the tags input.
In theory it seemed fairly simple to loop through a sheet and write before and after each cell, but once I tried writing the code I realized I was in way over my head.
This is going to be difficult to explain, but I'll try... I need to be able to format a sheet that has all data in column format. Column A contains a number and B an application. C contains the issue data. I need the issue data to be moved under column A and B as illustrated below. What formula can I do to accomplish this?
Original:
Final:
Can't get html or the image tags to work for some reason.
I have an Excel 2003 list with four columns as shown below:
Zipcode CRRT Count Bundles
85710 C004 693 14
85710 C005 867 18
85710 C006 1021 21
I want to "expand" this list to use in a Mail Merge program to produce tags for our direct mailing. Each bundle contains 50 letters, so in the first line on the above spreadsheet, there will be 13 bundles of 50 and 1 of 43. Currently, I can cut and paste to create the following table, but there has to be an easier way...
Zipcode CRRT Count bcount Bundle ibundle
85710 C004 693 50 1 14
[Code] ........
So as I pull this into a mail merge I will get a tag that shows the zipcode, the crrt, the total pieces for that crrt, then number of the bundle for example " 1 of 14" and the quantity in that bundle, for example "50 of 693", then the next bundle tag will print, 3 to a page. I know that here has to be an easier way either in the mail merging process itself or with EXCEL? Am even open to build these with ACCESS to create the tags. The beginning database contains anywhere from 350 to 800 lines of original data, so as you can see the cut and paste is VERY time consuming.
I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.
However, I have a problem in that my import macro gives me 'Run-time error '1004:
Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!
The code below shows is for the import macro only:
Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer
Is there a standard way to organize sections of code with code tags? Due to the nature of my work, there is a lot of documentation involved. And, my spreadsheets rely heavily on VBA. These code tag sections will be going into a design specification, so that there is a layer of traceability from the document to the program. These are the general sections or item numbers that I've come up with.
1. Dimensions and Variables
2. Data
3. Processing the Data
4. Rules of analysis
5. Analysis
6. Formatting
I guess I'm looking for something standard like UML diagrams for DFDs, but with tagging code to break them up into sections?
So I have a list of integers in col A that is a truncated list from col B. I want to select the units in col B that do not appear in col A. I tried this statement
=IF(A2=B2,A2,0)
Since both lists (A and B) are ordered from smallest to largest this only worked until a value that is not in ColA appears in ColB and then all the cells following = O.
I have a column of cells, of which certain values have the prefix "EmployeeID:". I'm trying to identify the cells containing the prefix by evaluating each cell using activecell.offset and value to no avail. I've also tried to use the selection. find, however the first instance of the prefix is continually selected, and does not move on. I'm assuming that I must use the findnext feature, however I'm not having much success.
I am trying to develop an IF formula based on 2 variables;
1. The value in column Q = 4 2. The value in column AA is between -10% and 10%
When both are true, it should return a value of "YES" (or "NO" when untrue). I am having trouble with the range part. It could be that the range spans negative and positive integers, but I'm not sure. Here is what I've got at the present time:
one table (TABLE1) contains the name and a mile point a
the other table (TABLE2) contains name, mile point 1, mile point 2, and other data
I want to be able to pull data from TABLE2, based on TABLE1's name and MP. I want to be able to select data from TABLE2 whose name and mile point's fit with the data from
TABLE1 Line MP A SYS
100 8 ?
TABLE2 SYS Line MP1 MP2 DATA
A 100 0 5 BOB
A 100 5 10 KON
B 100 10 15 B
I want to be able to replace the ? from table1 with the correct SYS from table2 based on the MP given
I started with something like: =if(and(name=name,mpa=>mp1,mpa=<mp2)),vlookup...
The problem is this doesn't work because im comparing one to many.
I've collected some data from a GPS logger regarding the speed of an athlete. I want to calculate how many sprints this particular athlete undertook during a training session.
Sprinting is defined as a speed of > 20 kph.
One sprint would be the attainment of one peak >20 kph before decreasing below 20 kph.
It's easy to identify the 3 peaks and thus sprints from the xy scatterplot in the attached file, but I'm struggling to find a way to calculate this.
I'm trying to identify duplicates with a return of "True" or "False". The attached workbook has column F for results (to read PI2 A if in column L, PI2 B if in column R or Both), but I want to query if everyone in column E is in column L or is in Column R and if so which one.
i have a spreadsheet that contains dates spread across different rows and columns. How can I identifying dates for this week and next week by colouring the cells?
example dates that fall within this week will be red, next week will be blue.
