Excel 2010 :: VBA Code To Connect To SQL And Pull Records To New Worksheet
Oct 18, 2012
i found this code...
Code:
Sub Button1_Click()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim SNfound As String
'Your sqlserver 2008 connection string
Const stADO As String = "Provider=SQLOLEDB.1;" & _
"" & _
[code].....
but i dont see where to put in the Database object...
The Database it needs to connect to in SQl is called
The Server name is SQLSRV when you expand databases the database is called SWHSystem the Table is called dbo.Credential and from that i need to get SELECT All from the Name and CardNumber from dbo.Credential and put that in a New Sheet titled Personal
using Excel 2010 connecting to SQL 2008
View 4 Replies
ADVERTISEMENT
Jun 15, 2012
I have an excel 2010 spreadsheet that lists all of our vendors and the amount we spent with them over the last year.
I want to know how many of them were local vendors.
I have a list of all the zip codes within a 100 mile radius (there are about 500 zip codes). I would like to write a macro or sort function that searches the entire vendor list and only reports back those vendors that are included in the zip codes I specify.
The columns are as follows:
A B C D E
Vendor City StateZip CodeTotal AP
Vendor 1TROY AL36082527.37
Vendor 2PHOENIXAZ85054100
Vendor 3TUCSONAZ8571416255
I want to keep the all the columns, I just want to eliminate all of the vendors that do not fall within the zip code criteria I set.
View 5 Replies
View Related
Feb 12, 2013
I have 2010 Excel and MS SQL 2008R2.
I'd like to import data from my stored procedure from MS SQL into a cell in Excel....
No pivot.
I looked for a standard coding all over the place - no success.
View 1 Replies
View Related
Nov 5, 2012
I have a 2010 excel workbook with several locked worksheet (to which I manage the PW). I and another staff member manage different section of the macro but the other staff member doesn't have access to the locked areas.
Is there a way I can encrypt the password within VBA so it's not visible to the other staff member?
Locking the VBAProject doesn't work as the other staff member has to be able to edit his VBA section.
Many staff run the macro (via a button) and don't need to access the Macro and don't have access to the protected sheets.
I understand excel isn't ideal with PW protection for people wanting to bypass the protection and this isn't an issue.
View 3 Replies
View Related
Aug 6, 2014
Using Excel 2010. I have 2 worksheets, saved in same folder (if that matters).
Worksheet 1=Master_List.xlsx
Column1
Column2
Column3
Column4
PartNumber
1002
Sony
Playstation
4D
W74859
Worksheet 2=Sample_List.xlsx
Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
Column1
Column2
Column3
PartNumber
Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
1002
Sony
Playstation
Basically, I'm looking to populate the blank cells in the PartNumber column (Sample_List) by looking at the data in Column1, Column2, Column3 then going to the Master_List, matching up Column1, Column2, Column3. The PartNumber value in the row with the matching values would then be copied to the Sample_List.
View 4 Replies
View Related
Aug 12, 2010
In Excel 2010; the pivot Tables drop down filter is limited to 10,000 records. if more than 10,000 are available then a message saying "Not all items showing" is displayed at the bottom of the list.
Clickin on the message would display a window saying: "This field has more than 10,000 items under one or more parent items. Only the first 10,000 items are displayed under each parent item."
View 3 Replies
View Related
Jul 25, 2013
I have a spreadsheet which is used by users unfamiliar with Excel. They are using the filter to select records, however when this is used some records appear which have no entry in the cells of that column. Can I overcome this? There is no data in the blank cells, other than a data validation drop down.
View 1 Replies
View Related
Feb 14, 2014
I am using Excel 2010.
My objective is to pull specific values from an external file corresponding to the correct name and year of my choosing. The first way I thought would be best is to use an INDEX/MATCH function. The problem is the external files change names so I would need a method to easily change the source file name from one cell. I then stumbled across the INDIRECT function, but the INDIRECT function will only work when the source file is also opened. I then came across Harlan Grove's pull function which allows you to import data from closed excel files.
So, this is what my formula looks like: =INDEX(pull("'"&G12&"");MATCH(C15;pull("'"&G14&"");0);MATCH(D15;pull("'"&G16&"");0))
Cells G12,G14, and G16 contain the file paths for the ranges. C15 is name and D15 is year.
The problem I have though is that when I try to execute the function, Excel gets stuck. if the code cannot handle large amounts of data. I tested the code with a simple SUM function for a small range from an external file and it worked just fine.
This is the code I am using:
[Code] ...
View 3 Replies
View Related
Oct 27, 2011
Using excel 2010
In cell U24 I have this formula:
=SUMPRODUCT(($E$3:$E$2000=51014)*($F$3:$F$2000=101)*($K$3:$K$2000))
Currently, as you can see, this is summing up the criteria specified from row 3 to row 2000...(which i made the last row 2000 becuase I will never exceed row 2000 with info)
What I am trying to do is in cell T22 and U22 be able to plug in date ranges and make the above formula give me the sums of thos date ranges.
For example in T22 put in 8-1-11 and in U22 put in 10-1-11, and have the sum of that date range populate in cell U24.
Now ther kicker is this: some dates are repeated so I may have six rows of date 8-3-11....and they will have to be part of the sum total.....and ALSO the dates are not in chrological order....I may have six rows of 8-3-11.......then four rows of 8-10-11.....then maybe another three rows of 8-3-11, and so on and so on...this is for all dates. Sorting the dates in order is not an option.
View 2 Replies
View Related
May 9, 2013
I have a requirement to pull report from BMC remedy using VBA. I am sure that many people would have achieved this. .
View 1 Replies
View Related
Dec 6, 2012
I have previously used the following code to successfully pull out IE webpage source code for string manipulation.
Its a crude example to demonstrate the principle:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public IE As Object
Sub Sample()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
[Code] ......
However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"
The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......
The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.
Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?
Using :
MS Excel 2010
IE Explorer 8.0
View 1 Replies
View Related
Nov 23, 2008
I have three worksheets called North, East and South. Data is held in each of these sheets starting in D1:BZ144 and the data is in columns of three. Example columns D:E is one set of records, F:H is another and so on. The first cell of each set of columns is the company name.
I need to copy all of these records into a Summary works sheet so that the records are in held in columns B:D and that the first cell is shown shown in column 'A'.
Secondly, in a second sheet I'm trying to setup a lookup so that I can call the company name and the records from the sheets North, East and South are shown that relate. I've tried using HLOOKUP but as the company name only appears in the first column I cannot get columns rwo and three to show.
View 9 Replies
View Related
Feb 26, 2007
Some time ago, a friend of mine told me he didn't use any Pivot Tables at all, due to the imense space they require.
Instead, he made connections between Forms in Excel and the Databases using SQL.
Do you know of any Internet site where I can start to learn something about this?
View 9 Replies
View Related
Feb 26, 2008
I've got a master excel sheet with about 6000 rows of unique variables. In separate source files, I have many rows of these variables with information next to each. Each file has a random number of these variables, some a couple of hundred, some a thousand or two.
What I'm looking to do is to have the master sheet with all 6000 rows and have all of the information next to each variable, with new columns for each new bit of information.
I could sit there for hours copying and pasting each new bit of information to the relevant variable in the master sheet, but I'm convinced there's a quicker way. Is there, for example, a way to filter the master sheet based on the variables contained in one of the source files and therefore (once a-z sorted) copy all the information from a source file and simply paste it into the master file? Then once the filter is lifted the same can be done for the next file (there's only about 20 source files so that's manageable).
View 7 Replies
View Related
Jul 20, 2012
I have a Workbook (Test 1) with several columns A-S. I'd like to be able to continue to add data into Test 1, and have some of the data pulled into a new Workbook (Test 2). Basically pulling data from Test 1, cols. D, L, J, E, I, C, K, Q and S - into the new workbook Test 2.
Some of the columns in Test 1 have conditional formatting and data validations, but I need the data in these columns to be replicated into Test 2 - without having to copy/paste between the 2 workbooks.
I figured how to do this in Excel 2010, but my office is still using 2007 and I can't seem to get it to work....
View 1 Replies
View Related
Oct 14, 2008
I need to pull a reference number and assign it to all records in a particular section. The code I recorded keeps going back to the first section. Also would like it to repeat until all records have the reference number attached.
06010-100Beginning Balance07/09/2004AD06011-10007/23/2004AD06011-10008/06/2004AD06011-100
06011-100Beginning Balance07/09/2004AD07/23/2004AD08/06/2004AD
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/14/2008 by acantu
'
'
Selection.Copy
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B7").Select
Selection.End(xlDown).Select
Range("C63").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
End Sub
View 9 Replies
View Related
Oct 2, 2013
I have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
View 2 Replies
View Related
Apr 16, 2008
I have the following data :-
SHEET 1
COL A_______COL B________COL C
V1990_______J100_________U1212
H2323_______Y999
U2222
SHEET 2
COL A_______COL B________COL C
U2222
I0000_______U8900________T67888
H2323
I need to search data from range defined A1.C3 and if any data in that range found in the sheet 2 that having the same records.
View 7 Replies
View Related
Dec 11, 2012
Excel 2010 / Win 7.
I have some code that imports a csv file into a workbook. This works fine when i use a file named .csv. I was hoping to use a random file name (in this case .bmhs) so that we can determine which files we need to import.
The problem i have is when importing a '.bmhs' file the data comes in column A and is a comma seperated list. If i use .csv then the commas are used as the column seperators (which i need).
impFle = Application.GetOpenFilename(filefilter:="BMHS Files, *.bmhs", Title:="Select Import File")
would give me 1,2,3,4 all in column A
impFle = Application.GetOpenFilename(filefilter:="CSV Files, *.csv", Title:="Select Import File")
would give me 1 in column A, 2 in column B, 3 in column C and 4 in column D.
Is there any way that i can maintain using my own file name (.bmhs) but have excel treat it as a csv file.
View 2 Replies
View Related
Jul 30, 2014
I am using lesson 33 of Excel VBA and Macros with Mr Excel as my guideline. The coding is as follows:
Dim WBO As Workbook 'original workbook
Dim WBN As Workbook 'individual data workbooks
Dim WSL As Worksheet 'List of files worksheet
Dim WSN As Worksheet
Set WBO = ThisWorkbook
Set WSL = WBO.Worksheets("List")
Set WSD = WBO.Worksheets("Data")
[Code] ......
I have indicated above with ( ) the beginning of my problem.
From the workbook with the list of workbooks to open, in each case I want to refer to a worksheet in each workbook
named "Report" and grab all the rows with data exept heading (rows 1 & 2) and 9 columns.
How do I get WSN in the code above to include the worksheet named "Report" or do I need different coding. Using Windows 7 & Excel 2010.
View 1 Replies
View Related
Apr 24, 2014
I have windows 7 and excel 2010 and am using a macro that opens up a csv file(I think) of daily reports into Excel and then automatically delineates it and formats it how I want it. I will be using this to save a new file every day for the reports from the previous day and want to include at the end of that macro a way to prompt the user to "save as" so that each day they can run the macro and enter in the date and save that report for further use. I am wondering what VBA code I could use at the end of the macro code to prompt the Save As box and if I could already have the save us set up in the following folder... "W:Daily to Fortis Excel2014(the user will put in the date here)".
I've been looking around sites and trying to figure it out. I need the file format to be the same as when you save as "Excel Workbook". I was trying to use the Saveas (filename) function and could get it to save every time as a specific file name in that location but when I run it the next day it has the same name and saves over itself. So I need the user to be able to put in todays date as the filename to create a new one every day.
View 7 Replies
View Related
Jun 2, 2014
The only change I made was to change the "Sheet1" to "Journal" to match the worksheet name in my workbook.
As you close and reopen the workbook, the timer should start with =NOW() in A1 (formatted as "HH:MM:SS") and count up with the current time until you close the workbook.
I use Excel 2010, could that be the problem, that I copied a VBA code for an older version of Excel???
Question: What exactly is a regular module, do I use Module 1 for the first portion of the code or place it in ThisWorkbook?
AUTO TIME UPDATE VBA
PLACE IN REGULAR MODULE
[Code] ....
View 7 Replies
View Related
Jul 5, 2012
I'm using Excel 2010 and XP. I have data in rows 1 -4 that can not be altered. There are headings in Row 5, B-L & Row 6, F-J.
Data starts in Row 7 and follows the headings. This repeats for several thousand rows. I need a way to move Row 6, F-J and put it on Row 5, M-Q
Row 6, F-J and put it on Row 5, M-Q
Row 8, F-J and put it on Row 7, M-Q
Row 10, F-J and put it on Row 9, M-Q
Row 12, F-J and put it on Row 11, M-Q
....
....
....
Then delete blank rows below Row 5
View 7 Replies
View Related
Jul 17, 2012
I have some VBA that dumps various sheets data into an SQL Database.
Part of that requires me to sanitize all of the fields before they make it to the DB, (at least to prevent the code from breaking itself w/ errant ' characters.
right now my code is as follows
Code:
If InStr(aa, "'") > 0 Then
aa = Replace(aa, "'", "''")
End If
If InStr(bb, "'") > 0 Then
bb = Replace(bb, "'", "''")
[Code] ......
I was hoping to condense it to something like the following, however it is not working how I hoped / want it to. .. I had found somewhere out there that this Eval() function possibly could be used to 'reference' dynamic variables, however it does not appear to work at all anymore, and even then it may only have worked to 'read' and not 'write' to the variable. (Excel 2010, on Windows 7 64-bit).
Code:
itemsToSanitize = "aa,bb,cc,dd,ee,ff,gg,hh,ii,jj,kk,ll,mm,nn,oo,pp,qq,rr,ss,tt,uu,vv,ww,xx,yy,zz,aaa,bbb,ccc,ddd,eee,fff,ggg,hhh"
ITSArray = Split(itemsToSanitize, ",")
For Each thing In ITSArray
If InStr(Eval(thing), "'") > 0 Then
Eval(thing) = Replace(Eval(thing), "'", "''")
End If
Next thing
View 7 Replies
View Related
Dec 6, 2012
Code + Button to automate certain row heights.
1. Starting in row 5 the row height is to "30"...every 17 rows after row5 to have a height "30"...next row 22 is "30" , then row 39 is "30" and so on...
2. All rows in between row height "30" will be with a row height of "11"
3. Can this be associated with a button....
4. where would i place this code.....
Version:Excel 2010
View 9 Replies
View Related
Dec 14, 2012
I need a code that when i place a date in a cell D10 (Example:25-January-2013) it will then add 40 days of dates daily to AP10.In D9 can it also add the weeknumber (every 7 days the weeknumber increases by 1) corresponding to the day date in D10 (iso).Can this be attached to a button.Enter the date in D10 then press the button and the dates auto insert across the sheet daily to AP10.
Can the button say ADD DATE or REMOVE DATES.First date in D10.When the button says REMOVE dates all dates deleted when button pressed and cell D10 then says "add date here".If no date is placed in D10 and ADD DATE button is pressed a warning messagebox appears and says NEED TO ADD DATE .Will not work until date entered.When date entered in D10 "Add Date Here" disappears until REMOVE Date button is selected and again "Add Date Here" is displayed....not sure if this is possible but would be good if achieved. Excel 2010
View 3 Replies
View Related
Jan 8, 2013
As seen below, I'm looking for a vba code to highlight color on every Friday and through columns 1, 4, 6,9,11,14,16,19,21 upto columns 28 i.e. AH
Excel 2010ABCDEFGHIJKLMN1DateQty1Qty2TotalDateQty1Qty2TotalDateQty1Qty2Total
21-Aug-124559289374521-Aug-122721298357041-Aug-1222792338461732-Aug-121161244636072-Aug-12347918036592-
Aug-1225723358593043-Aug-12128088221623-Aug-124369158259513-Aug-1227723299607154-Aug-124096192960254-
Aug-124679386585444-Aug-122081870295165-Aug-12333751838555-Aug-124270357278425-Aug-1231793455663476-
Aug-122061336654276-Aug-1241466142076-Aug-123761939231587-Aug-123757375975167-Aug-12212203622487-
[Code] .........
View 2 Replies
View Related
Dec 23, 2013
how to run some VBA code (written by someone else, unfortunately) only when clicking once in cell A1, and not run in any other cell. This is being run in Excel 2010. The code I would like to run in this manner is below, and currently will run when the user clicks on any cell in the worksheet it is applied.
[
Option Explicit_________________________________________
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'If IsDate(Target.Cells(1, 1).Value) Then
Set DatePickerForm.Target = Target.Cells(1, 1)
DatePickerForm.Show vbModal
Cancel = True
'End If
End Sub
]
View 3 Replies
View Related
Mar 4, 2014
I created the following macro (using the recorder), and now need to modify it so that the formula extends to the end of the data (and no further). I would also like to include a total at the end.
Sub ExcessUsage2()
' ExcessUsage2 Macro
Rows("39:250").Select
With Selection.Font
.Size = 8
.Strikethrough = False
[Code0 ....
View 8 Replies
View Related
Jun 22, 2014
I need a VBA code that will when used with a Form Control "Check Box" will unhide / hide a row. To be more exact, I'm needind the code to "Hide" row 34 when unchecked and "Unhide" the same row when checked. I'm using Excel 2010.
View 9 Replies
View Related
