Preserve Formatting After XML Import

Jun 11, 2008

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 6 Replies


ADVERTISEMENT

Preserve Formatting In PivotTable

Sep 18, 2007

I have made a pivot table and in Pivot table option Merge labels for colum b. I the problem i am facing is once i give this the next colum text filed is automatically arranged in center. I want the next colum as left alignment. Once i delete some rows in the data then if i refresh it automatically align the next colum to center. I have given the preserve formatting as well as i have un selected the autoformat colum.

View 2 Replies View Related

Preserve Formatting When Pasting Into A Cell

Feb 24, 2010

I am making template and trying to preserve formatting when users paste into a cell. From my research, I have found that this is impossible with simple format protection, it works to prevent changing formating, but if something is pasted (with normal paste), the format will change.

I have found a few solutions, but can't get them to work correctly

Solution A: Disable normal paste and substitute Paste Special - Values

How will this work with pasting via CTRL + V and the right click menu? I need both to function.

Solution B: Allow users to paste normally, but use a macro to copy formatting from a hidden sheet

I actually found code for this, but I couldn't figure out exactly how to impliment it. The sheet that users will be pasting into is called "Batch Summary" and I was trying to pull formatting from a sheet names "Batch Summary Format", but was getting an error.

View 9 Replies View Related

Preserve Formatting When Pasting Part Of A Cell

Jun 4, 2013

Is it possible to preserve original text formatting when copying and pasting only part of a cell. Like, for example, a cell may contain ten words one of which is in red and underlined. How do you copy that one word and paste it in another cell so that it remains in red and underlined?

View 4 Replies View Related

Preserve Formatting On Pivot Chart Refresh

Sep 22, 2009

have come across the apparent inflexibility of a pivot chart retaining its user defined formatting whenever it is refreshed.

I understand I can save my chosen formatting as a 'user defined' custom chart, which can be applied post-refresh, but was wondering if a simple bit of code would do this automatically, saving the additional clicks!

how to deal with the attached. How could I adapt the code to call in other 'user defined' charts I may create in the future?

View 8 Replies View Related

ADO ADODB.Connection: Preserve Cell Formatting

Oct 4, 2006

With ADO (ADODB.Connection), is there a way to preserve the cell formatting on the worksheet that the recordset data is copied to? Right now, if I format a Cell's font and font-size, then run the Macro to refresh the data, the formatting is gone and it's back to default formatting.

View 2 Replies View Related

Preserve Cells Word Formatting Concatenating Text

Jun 7, 2008

I have a cell with some text in it that has formatting applied (bold in this case). I want to append some text on the end using VBA, but preserve the existing font formatting information. So say I have "one bold one" in my cell, I'm executing the following: ActiveCell.Value = ActiveCell.Value & "two"

But this wipes out the bold information. Is there any way of preserving the bold that's already in the cell. Either perhaps by copying the Characters object and reapplying it, or by some other method?

View 4 Replies View Related

Preserving Pivot Table Formats. Preserve Formatting Checked But...

Aug 28, 2009

I'm having a devil of a time trying to preserve the formatting on a pivot table that I made in Excel 2003. As near as I can tell, I've done the appropriate things --- check the preserve formatting box, uncheck autoformat --- but my formatting is not preserved when I pivot.

Adding to the mystery is the fact that some formatting is preserved but not all. I'm guessing either I'm missing something obvious or this is just an unruly, rogue pivot table that refuses to be formatted.

View 2 Replies View Related

Error Formatting When Import Data From Google Into Excel

Feb 16, 2013

I use Google Spreadsheet to feed some data online. Later on I use Excel to import this data that it is hosted at Google to do a better handling (reports, charts, etc). However when I import the data from Google, the contents of 1 row is splitted on 4 in Excel.

Are there any way to keep the same formatting from the original ?

[URL]

View 5 Replies View Related

Array Won't Preserve Values?

Apr 8, 2014

I am trying to store values into a dynamic array. The size of the array will vary each time, so I need the range to be dynamic. Most importantly, I need all values to be retained in the array. Currently, a value will be stored, but once the next round of the for loop is initiated, the array changes to "<subscript out of range>" and stays that way until the it is replaced by the next value. So, there isn't an accumualtion in the array--it goes one value, to out of range, to one value, etc.

View 8 Replies View Related

VBA - Preserve Hyperlink When Using VLOOKUP?

May 26, 2014

I am having when trying to preserve a hyperlink when using VLOOKUP. I have two worksheets within the same workbook. Worksheet 1 contains a lot of information for internal use(many of the cells in all of the columns contain hyperlinks to web addresses), and Worksheet 2 should be a version identical to this, showing only the selected columns suitable for external use. This is to avoid using two different 'work trackers'; so, when info in Worksheet 1 is updated, Worksheet 2 should automatically be updated and reflect this.

I am currently using the following code:

Function GetHyperLink(r As Range) As String
If r.Hyperlinks.Count Then
GetHyperLink = r.Hyperlinks(1).Address
End If

[Code].....

View 8 Replies View Related

Convert To Pdf Preserve Link

Aug 27, 2009

I m trying to save my excel file as a pdf. all is working great except the fact that i have a few cells that contain hyperlinks (via HYPERLINK formula). when exported, they are being converted to a simple text, loosing the link.

thus my question is this, would anyone know of a proper way to export an excel file so that ALL hyperlinks are preserved? be it with excel or any other program/converter.

View 9 Replies View Related

Preserve Function When Inserting Cells

Apr 21, 2009

I'm using IF functions to get the difference of two cells. An example:

View 5 Replies View Related

Delete Rows But Preserve Formula?

Apr 29, 2012

I have an inventory sheet with rows containing a formula. I've placed the same formula in all 65536 rows that Excel 2003 has.

I have a macro to delete unused items. It searches for an item number then deletes that row. (Actually it deletes the item number and a partial row based on an offset, but that was for an earlier version and an entire row would be okay) When a row is deleted, all the other rows move up and Excel creates a new last row (65536) The problem is that this new row has no formulas. While it's probably unlikely that I'll ever blow through 65536 rows, it seems sloppy to leave this unaccounted for.

Is there a way to either add the existing formula(s) to the last row...or insert a new row *somewhere* that is empty except for the formula of the other rows in the sheet? Here's what I have for the existing code.

Code:
'Search for item to delete based on entry to InputBox
Sub Delete_Item()
Dim FindString As String
Dim Rng As Range
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String

[code].....

View 9 Replies View Related

BeforeDoubleClick But Preserve Original Cell

Sep 18, 2008

Within Worksheet_BeforeDoubleClick, I have the following simple code extract that sets the appropriate autofilter to the correct value (and highlights a couple of cells for clarity) when a particular cell is double-clicked...

If Not Intersect(Target, Range("C10:AQ11")) Is Nothing Then

If Not Intersect(Target, Range("C10:D11")) Is Nothing Then

Selection.AutoFilter Field:=10, Criteria1:="C"
Range("C10:D11").Interior.ColorIndex = 36

End If

You'll be happy to hear that the event works as expected.

However I would like to add the following enhancement: after Worksheet_BeforeDoubleClick completes the "double-clicked cell" is selected, is it possible for the "original" cell to be re-selected once the event completes?

View 9 Replies View Related

VB Code To Reinstate/preserve Formula

Apr 25, 2009

My current headache is that I want to be able to reinstate excel formula when the delete key is hit. However there this is a slight catch. I don't just want to be able to do this for one cell but reinstate the same excel forumla in an entire cloumn (ie for multiple rows). On top of this I also need to find a way of being able to do this for multiple columns (ie column 1 has formula 1, column 2 has formula 2 etc.....)

I have found this but I am having trouble adapting it to suit my purpose. This code just reinstates the formula for 1 cell.

Worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address "$A$1" Then
Application.OnKey "{del}"
Else
Application.OnKey "{del}", "myFormula"
End If
End Sub

Sub myFormula()
Worksheets("Sheet1").Range("A1").Formula = "=RC[1]+RC[2]"
End Sub

View 9 Replies View Related

Preserve Formula References After Sort

Oct 23, 2006

I have noticed that if I create a row and put a reference formula in one of the cells (i.e. in Cell B3 I have "=B4", to show contents of B4), then after I do Sort and Row 3 moves else where, formula in what used to be B3 no longer points to B4, but rather to B<new row + 1>.

Absolute reference doesn't seem to be an option (i.e. "=$B$4") since after sorting rows in question the cell references in my B cells do get screwed up. I guess I want to bind a certain cell to a certain other cell, and keep that bind no matter where I move the row.

View 9 Replies View Related

Modify Array Forumula And Preserve Brackets

Dec 11, 2008

How would I modify a (very long) array formula, while preserving the brackets? Or re-creating them? I've tried doing it "at the cell" without luck.

View 2 Replies View Related

Search & Replace - Preserve Cell Format

Jul 13, 2006

I'm using the code below to do a simple search & replace. The code is working fine.
My problem is after the search & replace is done, certain cells that contain numbers look weird with decimal points & plus signs similar to Format Cells - Scientific. How do I prevent these cells from looking 'weird' in my code?

I've attached a workbook with 2 sheets called Before & After. You can see the 'weird' cells in After.

Sub Macro1()
Cells.Replace What:=": ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

View 5 Replies View Related

Preserve Cell Number Format In Message Box

May 28, 2007

I'm writting an Excell VB application which reads data from my spreadsheet and places it into the text member of an IXMLDOMElement node in order to generate a QBXML request.

The cell I am reading from has datatype number with two decimal places as required by the xml scheme. The problem is that the Range.Value Variant data type drops the decimal point and any trailing zeros so that 1197.00 become 1197. Unfortunately, the QBXML parser requires to see the decimal as well as trailing zeros. So, 1197.90 must show up as precisely that. Changing 1197.90 into 1197.9, for example, also breaks the xml.

My Excel VBA book specifically states that a Variant data type holding numerical data will maintain the data type assigned to it.

I also tried declaring a local decimal (Dim MyDecimal As Decimal) thinking to try copying the Range.Value value to it before assigning to the QBXML field in order to force the correct data format but my VBA macro compiler does not apparently recognize "Decimal" as a valid data type event though my book clearly indicates it is one of the valid VB data types.

I am using Excel 2000. Are these problems solved in newer versions?

View 9 Replies View Related

CSV Data Has Leading Zeros - How To Open In Excel To Preserve Them

Jan 29, 2013

I will be receiving a regular .csv file with data I need to manipulate in Excel. There will be a column of data, some of which has leading zeros. These are critical text data - in Excel it's easy enough to format a cell as text - but when I OPEN a new CSV, it assumes numbers and doesn't save them. When I change the format to text, it does not re-populate that column correctly - I'm going to have to catch it on the "open in Excel" command .

View 4 Replies View Related

Combine/Join Cells & Preserve Leading Zeros

Sep 6, 2006

I have 2 columns which contain numbers that have laeding zeros. example:

Column H has 002345 and Column I has 0678. I have tried H1&I1 but the leading zeros are stripped off. Is there a way to combine 2 cells AND preserve the leading zeros in both cells ?

View 3 Replies View Related

Import Multiple Text Files & Copy Each Import

Jan 29, 2008

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

View 8 Replies View Related

Hit Tab Button To Insert New Line And Preserve Previous Lines Formulas

Feb 23, 2014

I am brand new and I don't know a ton about macros and programming in Excel.

What I'm trying to do here in this attached spreadsheet is at the end of inputting my line, I want to hit the tab (marked in red to make it clear where) button in the cell and have it automatically create another blank line but keep the formulas the same.

Basically I just want to avoid having to right click and insert line and then drag the formulas column by column down which is difficult.

See attached : TEST2_2014 Sales & Commission Spreadsheet.xlsx‎

View 5 Replies View Related

Preserve Format Of Table When Splitting Master Into Separate Tabs

May 27, 2014

I have a macro that enables me to split my master table into separate tabs based on the first column. However, the column widths and the header format is not retained. Is there something I can add to the macro below to keep the formatting?

Code:
Sub DispatchTimeSeriesToSheets() Dim ws As Worksheet
Set ws = Sheets("MasterList")
Dim LastRow As Long

LastRow = Range("A" & ws.Rows.Count).End(xlUp).Row

[Code] ......

View 4 Replies View Related

Dynamic Array Redim Preserve: Run-time Error 9, Subscript Out Of Range.

Jul 20, 2009

I found quite a few posts about this problem, but none of the answers was any use to me. I need to redimension a 2 dimensional array in a Sub. I deleted all the code that is not of interest:

View 3 Replies View Related

Import CSV Every X Minutes Without Import Dialog Appearing

Apr 1, 2008

I have a simple macro importing a .csv data file to a worksheet every 30 minutes.

When the macro runs an Import Text File dialog box displays.

What do I need to do to have the macro run to completion without the dialog box interruption?

Sub RefreshHourlyData()

htime = Now + TimeValue("00:30:00")

Application .OnTime htime, "RefreshHourlyData"

Sheets("H1Updates").Select
Sheets("H1Updates").UsedRange.Select

Selection.QueryTable.Refresh BackgroundQuery:=False

Range("A1:A1").Select

End Sub

View 3 Replies View Related

Editing Existing Macro To Preserve Option Button Settings Upon Activating Worksheet With OBs

Sep 19, 2013

I have been using this code and just noticed that it resets all of my options buttons to false when I exit and then re-enter (activate) the sheet with the option buttons.

I see where this is going on, but don't know how to correct it. I only want the option buttons changed to false if

The Sol named range is something other than "Primary Vendor". It seems to call the macro ClearOB whenener the sheet is activated.

Private Sub Worksheet_Activate()
If Range("Sol").Value = "Primary Vendor" Then
For Each OB In ActiveSheet.OptionButtons
OB.Enabled = True
Next OB
ClearOB
ActiveSheet.ScrollArea = "A1:K58"

[Code] .......

View 1 Replies View Related

How To Remove Data Connection But Preserve Data

Jul 24, 2013

If I run this vba it removes the external data connection to the pivot table:

ActiveWorkbook.Connections("EXAMPLE").Delete

When I attempt to drill down into the pivot table it states the data connection was removed.

Is there a way to remove the data connection but preserve the data in the pivot cache?

It's probably possible to store the data somewhere else in the document and link the pivot table up to it but that seems cumbersome.

View 4 Replies View Related

Conditional Formatting - Two Different Fields Affect One Cells Formatting For Date?

Sep 16, 2013

A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.

dust 1.xlsx

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved