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 RepliesI am simply trying to have some code that would allow me to run relatively simple SQL Queries on Excel Data. This data however will be in a table format within Excel and I will only be needing to query on table at a time; meaning that I don't need relational database features or SQL Joins at all. At most, the SQL Statements will involve WHERE and SORT statements. So, my method is relatively simple:
1. Select an Excel Range (The table being Queried)
2. Provide an SQL Statement
3. Provide an Output range to print the results
After my recent research, I concluded that ODBC/ADODB was the best route. I am very new to ODBC Connections though. Basically, I just set up an ODBC Connection and DataSource using the Control Panel/Administrative Tools. After that, following some templates of others I constructed the following
Sub getData(SQL As String, outRange As Range)
Dim conn As Variant
Dim rs As Variant
Dim cs As String
Dim outCell As Range
Set outCell = outRange.Range("a1")
Dim row As Integer
Dim col As Integer
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
cs = "DRIVER=Microsoft Excel Driver (*.xls);UID = admin;UserCommitSync = Yes;Threads = 3;SafeTransactions = 0;ReadOnly = 1;PageTimeout = 5;MaxScanRows = 8;MaxBufferSize = 2048;FIL=excel 8.0;DriverId = 790;DefaultDir=F:2008Cell PhoneCall Details;DBQ=" & ThisWorkbook.Path & "" & ThisWorkbook.Name...............................
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.
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 RelatedIf 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.
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 RelatedI 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 ?
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?
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?
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.
I have an excel document saved in a SharePoint document library. I then have another excel document where I created a connection back to the document being saved on SP (went to data connections->add->browse for more->enter the SP URL & found the document) and then opened that connection up as its own worksheet (existing connections->chose my connection). In the original worksheet (saved on SP), there are about 20 columns that are formatted as numbers. However, when I open the connection up in the new document, half of these columns are still number formatted, but the rest are being displayed as text EVEN THOUGH they are technically formatted as numbers (I right click->format cells & they are formatted as numbers). I can click into a cell and press enter and it will "come up to speed" and enter into number formatting, but as soon as I refresh my connection back to the original document, they go back to their text formatting despite the fact that the connection they are drawing from has them saved as numbers! Very frustrating. Its not a SP issue because I recreated the scenario by saving the original document to my computer and it did the same thing. I even went through one column in the original document and made sure there were no spaces saved to trigger the column to go to text- nothing.
View 2 Replies View RelatedI have an Excel workbook that has a data connection to a Sharepoint List. I have 4 columns showing the quaterly status of a project. I want to apply conditional formatting based on the value in the column. I have 5 possible choices so I have to use VBA code to accomplish this because of the limit of 3 option in Excel. I am using this
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Range("X:AA"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case "1"
cl.Interior.ColorIndex = 4
Case "2"
cl.Interior.ColorIndex = 6
Case "3"
cl.Interior.ColorIndex = 3
Case "4"...............................
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?
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
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?
I have a macro which retrieves data from web.It works fine.I just don't know how to assign a cell value in web address.i have attached the code here. i just want to assign activesheet.(range"c2").value for "q?s".
View 1 Replies View RelatedI'm using the below code to add data to an access database and it's working fine, but I'm trying to figure out what code I would need to add a completely new field to the access database?
So maybe before the export, once connected to the DB, add the new field(s) to the table and then add export the data. I'd have to rewrite export bit to allow for any new fields that i've added, but I can do that.
It's just the actually command I need add the new field to the table. Sure it's just a simple couple of lines, but trying to find something that makes sense !!
Code:
Sub Update_data(strSite)
strDir = Worksheets("Parameters").Cells(2, 2)
strDB = Worksheets("Parameters").Cells(3, 2)
strTable = Worksheets("Parameters").Cells(4, 2)
' exports data from the active worksheet to a table in an Access database
[Code] .........
I'm creating my first stored procedure ever and it looks relatively good so far. The problem is with passing the parameters, more exactly:
Arguments are of wrong type, are out of acceptable range, or are in confilict with one another
The essential part of the stored procedure looks like:
Code:
CREATE PROC GetUserAuthForApp
@User varchar(7),
@application int
AS
while the essential code calling it from VBA looks like:
Code:
Dim strConn As String 'Connection string to SQL Server
Dim strSQLtoExecute As String 'SQL query string to execute
Dim oConn As ADODB.Connection 'Object for connecting
Dim rs As ADODB.Recordset 'Object for recordset
Dim cmd As ADODB.Command
Dim prmUser As ADODB.Parameter
Dim prmApplication As ADODB.Parameter
Dim stProcName As String 'Stored Procedure name
[code]....
and in that rs.Open comes that error.
What have I done wrong, how do I fix it?
I am wanting to find a row in a SQL database using VBA in excel. I know you can use find to find the row, but then how do you replace data on that row with the row I have in my spreadsheet?
I currently have:
Code:
If lastrow 0 Then For i = 3 To lastrow
recset.Find "Col2 = " & Sheets("Changes").Cells(2, i) 'find the value in B from B3 onwards (i)
' Now its found the row in the SQL, replace with values from that excel row (i)
Next i
End If
I have some code that will Kill a DB if it already exists, but I want to check if it exists and warn the user before this happens.
I am not that familiar with ADO, so I was fumbling through the Help topics trying to learn about ADO type names, etc. b/c i thought I could use something like:
If TypeName(MyDB) = "ADODB" Then . . .
but even if that ran, the argument in parentheses would be a string and not the actual DB object, so I am at a loss.
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 RelatedI 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].....
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.
I'm using IF functions to get the difference of two cells. An example:
View 5 Replies View RelatedI 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].....
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
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.
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 RelatedI 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 RelatedI 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 ?