SQL Queries And Methods - Accessing Different Database

Sep 4, 2013

Below is an example of how I prefer to work with a database. Results are fast; even across the network and I understand the syntax perfectly.

Code:
Sub DBPreferredWay()
folder = ThisWorkbook.Path & "mydatabase.mdb"
Set db = OpenDatabase(folder)
strSQL = "SELECT * FROM transactions WHERE [SimilarItems] = " & myFocus
Set tR = db.OpenRecordset(strSQL)

[Code] ........

I'm trying to access a different database; a database being hosted on my SQL server. Because of security and what not; it looks like I'm needing to use adodb but I can't stand it and the slowness (minutes to receive queries. IS there a way for me to access my sql server database similarly to how I'm accessing MDB tables across the network? Below is what I've been trying with the adodb

Code:
Sub SqltoACCPAC()
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
phrase1 = "DRIVER=SQL Server;DATABASE=" & "VNODAT;SERVER=192.168.0.91,1433": conn.Open phrase1, "SA", "password"
strSQL = "SELECT * FROM ICITEM WHERE [SEGMENT1] >= 101 AND [SEGMENT1]

View 8 Replies


ADVERTISEMENT

Accessing Multiple Workbooks To Form A Master Database

Jan 22, 2009

I have 100 or so workbooks named A1234.xls, A1235.xls, A1236.xls etc.

I want to gather information from the same worksheet and same cell in each workbook.

I want this information in a separate workbook named Master.xls

In the Master spreadsheet I have the workbook names in column A e.g.
A1234
A1235
A1236 etc.

In column B, I want the information form each of the 100 workbooks. For example, from "sheet 1" Cell C2. This is the same place I want the information from in each workbook.

Is there an easy way of doing this with a formulae rather than a macro. For example, in the Master spreadsheet column B1 formula would read =[A1234.xls]Sheet1!$C$2 ... and then can you drag this changing the filename according to column A in the master spreadsheet?

If not can a macro be used? I have only started looking at macro's and my knowledge on them is very basic.

View 9 Replies View Related

Run SQL Queries In Excel Against Database Using Macro?

Jan 29, 2014

I have sql queries in spreadsheet column and which needs to run against Db2 database daily and then update the result back to spreadsheet. This is tedious process and could there be a way to create a a macro using VB which performs the following?

1. connect to db2 database

2. take the sql query one at a time from every row and then run against database

3. Obtain the result and then update it back to last column of the spreadsheet.

View 3 Replies View Related

Report Design. Layout Multiple Database Queries

Jan 9, 2007

Is there a really good tutorial for laying out multiple database queries in excel? Or even just for automating reports between Excel and Access? Here is my situation: Monthly, Quarterly & Annually I report on performance standards such as: duration, best practices, cost-benefit analysis, etc. The information is pretty standard and is pulled from Excel Reports. Access is used to filter this information by appropriate month, quarter, year, and/or data type. In access I calculate the performance averages, max and min. Then this is in linked back to Excel to update the charts and summaries. My concern is how much time I spend rebuilding this system every month (about 4 business days)

View 3 Replies View Related

Difference In Two Methods Of Referring To Other Workbooks?

Feb 22, 2014

These workbooks are located in different folders.

The first is pasted "as link" from two open instances of Excel and is an array formula.
The second is just pasted from another open workbook.

{=Excel.Sheet.12|'xx.xxxFolderFolderFilename.xlsx'!'!SheetName!R2C9:R1500C22'}=IF(H4="N/A","N/A",VLOOKUP(A2,'I:FolderMore Folders[FileName.xlsx]Sheet'!$A$1:$T$35,13,FALSE))

What is the difference between using these two different methods?

View 1 Replies View Related

Override Methods In Transient Worksheet?

Apr 10, 2013

I am developing a VBA for Excel application which uses transient worksheets to collect data from users before writing the data back to master spreadsheets.

My application opens an instance of excel with a workbook and worksheet using the following code:

Dim xlsInstance As Object
Dim wkb As Workbook
Dim wks As Worksheet

Set xlsInstance = CreateObject("Excel.Application")
Set wkb = xlsInstance.Workbooks.Add
wkb.Activate
Set wks = wbk.Worksheets.Add
wks.Activate

I can reference various ranges in the worksheet and do most of what I need but I need to override the Worksheet_Change method. Easily done for static sheets, but this must be set at runtime when the worksheet is created.

View 9 Replies View Related

Comparing Time Value Generated By Two Different Methods

Feb 28, 2014

I have thousand rows of data in the following format:

Despatched Time
Batch
Date
Time
Amended Time

20/4/2013 3:45:00
1
20/4/2013
03:45

[Code] ..........

'Date' is generated by =IF(A2="", "", TRUNC(A2))
'Time' is generated by =IF(A2="", "", IF(E2 = "", A2 - TRUNC(A2), TIME(LEFT(E2,LEN(E2)-2),RIGHT(E2,2),0)))

And sometimes I may want to change the Time, so I used another column 'Amended Time' to insert a 4-digit value to do so.

I want to arrange them into batches according to the hh:mm, so for the column 'Batch', I used the following formula:
=IF(D2 = "", "", IF(EXACT(D2, D1),MAX(B$1:B1),MAX(B$1:B1)+1))

The problem is, when I changed the time by inserting 4 digits in 'Amended Time', the time value is actually different from those generated by TRUNC(), so even two cells have the same time '03:46', the time value is not exactly the same and so they are arranged into different batches (please refer to the following table).

Despatched Time
Batch
Date
Time
Amended Time

20/4/2013 3:45:00
1
20/4/2013
03:45

[Code] ..........

View 9 Replies View Related

Shorten Code Typing Methods

Aug 11, 2007

Instead of typing out my code hundreds of times, I would like to replace it with an abbreviation or variable(?). Here is my example. If I have this in the wrong place please let me know. I want to replace Activecell.Interior.Colorindex with ACIC. I tried to Dim ACIC as range, variant, string...to no avail. In combination I Set ACIC = Activecell.Interior.Colorindex and that didn't work. I have been toiling with VBA for a year and I am still trying to learn the terminology and understand it so please forgive me if I seem really NOOBIE. :-)

View 9 Replies View Related

Declaring An Object As A Workbook Is Limiting The Methods I Can Use.

Jun 18, 2006

I am trying to compare two workbooks and am running into the problem that since I declared my two workbooks, I am limited in the methods that I can use. Here is all of the code so far.

Option Explicit
Sub UpdateMasterFile()
Dim wbMaster As Workbooks
Dim wbEmailed As Workbooks
Dim wsPC As Worksheet
Dim Master As Long
Dim Emailed As Long
Dim intMaster As Integer
Dim intEmailed As Integer
Set wbMaster = Workbooks("Master Info.xls").Sheets("PlantsCom")
Set wbEmailed = Workbooks("EmailedData.xls").Sheets("NewInfo")
Master = Workbooks("Master Info.xls").Sheets("PlantsCom"). Range("a65536").End(xlUp).Row
Emailed = Workbooks("EmailedData.xls").Sheets("NewInfo").Range("a65536").End(xlUp).Row..............

View 2 Replies View Related

Access Worksheet Controls Properties & Methods

Aug 12, 2006

I am trying to implement code that can handle a dynamic number of objects, specifically check boxes.

I know of eval( ) in perl that allows one to dynamically create command lines. For instance, the following code would change all of the Checkbox values to 1. The syntax may not be correct but the idea is solid.

Do While i < num_checkboxes
eval("Checkbox" .i. ".Value=1)
Loop

Does this funtion also exist in excel?

View 6 Replies View Related

Range Collection Properties, Methods & Events

Aug 16, 2007

I need to learn the syntax and all options I can use with the Selection function - where can I find that info? I can't find anything in in Visual Basic Help (I find microsoft help is generally totally useless).

For example, in the code below, what are all the options I can use after "Selection. " (ie .Copy, .End, .Select) and what do I use if I want to select a certain number of cells to the left or to the right or up or down?

Sub Fill()
Selection.Copy Destination:=Range(Selection, Selection.End(xlDown).Offset(-1))
Selection.End(xlDown).Select
End Sub

I'm just noticing now that the "(xlDown)" is X L, not X 1, which I thought might make sense to say "1 time Down". What does the "xl" stand for?

View 7 Replies View Related

Time Efficient Methods For Calculations Based On Different Set Of Data?

May 27, 2014

how to calculate averages and standard deviations based on different time periods without having to manually change the cells?

example:
1st average output at z3, 1st std dev output at z4
data to calculate from c3:c50

2nd average output at z5, 2nd std dev output at z6
data to calculate from c51:c98

3rd average output at z7, 3rd std dev output at z8
data to calculate from c99:c148

and it goes on based on this sequence. i would like to know how to do this without having to change the cells each time i want to calculate. basically what is the quickest way to calculate following this sequence?

View 6 Replies View Related

Methods To Pull Data From Another Sheet Using Dropdown List

Jul 19, 2014

I am doing a spreadsheet where I have 1 main sheet and 2 sheets with data.On the main sheet I have a drop down list to select either one of the 2 sheets and upon selecting,all the data from that selected sheet will be displayed on the main sheet.

What i do not understand is the code given below

data:=IF($C$7='A2'!$A$2,INDEX('A2'!$A$6:$B$20,ROW( $A3),COLUMN(B$1)),
INDEX('A1'!$A$6:$B$20,ROW($A3),C OLUMN(B$1)))

How do i attach spreadsheet in this forums btw?

View 7 Replies View Related

Favorite Methods/Properties Exposed By The Range Object

Feb 11, 2009

some of your favorite Methods/Properties exposed by the Range Object. How's bout some that you just can't live without?

Have an tricks for example that you can do with XLS03 that for example couldn't do with XLS2K or perhaps XLS07 that you couldn't do with XLS03?

View 9 Replies View Related

Creating Images Database Where Pics Are In Excel Database

Mar 31, 2004

I am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.

What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.

View 4 Replies View Related

Database Named Database And Return A Time In Column

Nov 7, 2008

I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri

eg of Database
A B C D E F G
10:00 5000 Mon Tues

11:00 5000 Wed Thur Fri

and so on

if a1= 5000 & b1=Wed
how can I return 11:00

I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))

View 9 Replies View Related

Sum Database Totals But Only If 2nd Database Greater Than X Number

Oct 17, 2013

I've used a countifs, but I'm having trouble doing a sum in a similar way. I have 2 databases and I will try to explain below. I need the to sum the values of database 1 if the second database is >= 20. So the value I would expect on this example would be 900. I would also like to be able to highlight which ones are elliminated. Such as coloring the text red.

Database 1

Eric 100
Jenny 200
Gina 300
Doug 400

Database 2

Eric 18
Jenny 20
Gina 34
Doug 55

View 5 Replies View Related

Accessing WinZip Via VBA

Sep 5, 2006

This Sample is the code that I've adopted for my use.

My problem is this. My normal target .zip files are in the neighborhood of 75 - 80 Mb in size. Yes I know that they are huge..... (They are backup files for mainframe extracts ) Each zip file contains in the neighborhood of 2000 files. Currently, I have coded two ways of extracting data. Well really only 1.

1) I specify a location to extract all files. Which is time consuming ast it takes in the neighborhood of ( 5 min I thing for all files to get extracted ).

2) If I let the user specify a file filter parameter ( part of a file name ) while the WinZip shell is extracting files I'm searching all the file names in the dest directory and deleting the files that I don't want.

So what I want to know is it possible ( without buying software ) to be able to get the filenames of the files in a WinZip file.......

View 9 Replies View Related

Accessing Controls

Mar 21, 2007

I have a ListBox in my worksheet "A", and I'm tryin to acces to it... I want to use that ListBox like a log to print the error i have found while running my code. The name of that ListBox is "IncongruenciesListBox".

now if that ListBox was in a userform, i can access to it easily, but if it is in the worksheet how can i change (add more rows) to it?

I tried:

Dim WS As WorkSheet
Set WS = ThisWorkBook.WorkSheets("A")
If added = False Then WS.IncongruenciesListBox.AddItem ("error")

And there is a message saying it cant find "IncongruenciesListBox".

View 6 Replies View Related

Accessing Isblank In Vba

Apr 6, 2007

Is the following valid code in Excel VBA?

If Application.WorksheetFunction.isblank( Cells(RowIndex, ColIndex)) Then

View 3 Replies View Related

Accessing A Worksheet

Apr 26, 2007

I would like to know if it's possible to access a worksheet in an Excel spreadsheet using a pre-prepared string or character-set? Usage is for years consecutive, so worksheets are '1990', '1991', '1992', etc.

In which case I'd like to be able access these with a prepared string '1990', instead of using the hard-coded method for hundreds of data accesses.

View 7 Replies View Related

Accessing Different Sheets Via A Formula

Jan 10, 2010

I am currently accessing data from one worksheet to another using: =INDEX(AD!C:C, MATCH($B9,AD!$B:$B, 0)). Ie from my current worksheet ‘Claim’, I’m going specifically to sheet ‘AD’. My full workbook contains 18 sheets, one for each of 18 engineers and of which, the name of the worksheet is the engineers initials. What I would like to do, is to use a lookup table located on ‘Claim’ which will hold a list of all 18 Initials, so that when an engineer selects himself from a drop down list, his initials automatically populate cell K7.

My question therefore is, is there a way of substituting the ‘AD’ in the formula: =INDEX(AD!C:C, MATCH($B9,AD!$B:$B, 0)) with the contents of K7? Or, if you can see what Im trying to do here, is there any other way of accessing specific sheets by inserting the sheet name into a formula?

View 4 Replies View Related

Accessing The Properties Of A Shape

Apr 2, 2009

How does one access the properties of a shape? For instance getting the text on a button ( from the forms toolbar ) on a worksheet. This works

View 4 Replies View Related

Accessing Elements Within Arrays

Nov 10, 2009

I've been teaching myself VBA for the purpose of processing some time resolved data from an instrument. I've come up against a problem and I just can't work out why the code is failing. (using excel 2007, VBA 6.5)

I declare some arrays as dynamic, then ReDim them to a user defined number (with Option Base 1) and input data from a spreadsheet into the array. I can then output the data into the spreadsheet again and the lbound and ubound functions return the correct values for the array. What I cannot do is actually access individual elements. If I specify array(i) I get the "Subscript is out of range" error. The code relevant to this problem is as below (simplified as there are more arrays):

View 5 Replies View Related

Accessing A Closed Book

Dec 14, 2006

I have designed a spreadsheet that updates automatically and shows the company's perfomance on several sheets.

To add to the use of the sheet I would like to offer the user a preference set up. So they choose the sheet it opens on for example.

I am happy with how to do all the above except for the fact that the sheet is required to be available to all staff! so only one employee can have it open in read/write everyone else will eb read only!

I was thinking the only way around this would be to have a second sheet that saves their environ user name and preferences.

That would require using the sheet closed though would it not?

View 9 Replies View Related

Accessing Worksheets Via A Variable Name

Apr 21, 2008

i have a simple PO tracking system, i have a workbook with multiple sheets at any one time approximately 20, projects come and go and i need to add a sheet periodically, rename the tab to the project ID - 4x numeric.

All this i have managed to do, and sort the sheets numerically, i now need to copy the last (added) row on the Project specific sheet to the last clear row on the summary sheet.

I have also achieved this, but in order to do it properly i need to switch between the specific Project sheet i am adding the current PO to, (any one of 20) and the summary data sheet, the sheet i am copying to.

To achieve this i thought i could assign the TabName (of the Project) to a variable and then refer to it

Sheets(TabName).Select. using msgbox throughout appears to provide the correct results, however, this produces a {subscript out of range error no9} on this particular line. Any suggestions welcome. Same issue i believe i could address my problem if i could whilst in the Project specific sheet i could determine the codename of the sheet which i believe i could then use to switch between sheets programatically.

View 9 Replies View Related

Accessing The Buttons In The Command Bar

May 25, 2007

i am trying to access buttons like ' Format - Column - Hide'

View 5 Replies View Related

Accessing Data From Other Workbook

Jan 17, 2008

How can i access data from other workbook i have opened.?
I am new in VBA.So little code snippet would be good to show.
I want to get the cell data from workbook2 to workbook1

View 9 Replies View Related

Accessing Values Of 'alphanumeric' Variables

Jan 3, 2010

I have declared a number of string variables named "hlpYear1", "hlpYear2" etc in the 'Worksheet_SelectionChange' function. "hlpYear1" contains the appropriate text to display if the selected language is English, "hlpYear2" is for French and so on. I was hoping that I would be able to use VBA to display the appropriate text by way of a 'For..Next' statement along the following lines:

View 4 Replies View Related

Accessing Name Property Of Active Worksheet

Feb 23, 2012

In VB when I select a worksheet, I can amend the (Name) of the worksheet in the properties window. There is also the .Name property which is the same as the sheet tag name.

I can use the (Name) in vb code to identify the sheet e.g.

ControlSheet.cells(1,1)

without having to define ControlSheet as a worksheet first.

How do I access the (Name) which appears at the top of the list of properties in the properties window.

View 1 Replies View Related







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