Accessing Multiple Excel Grids With A Program Or Code

May 28, 2014

I am currently looking for a front end program to access multiple excel files at once. In a nutshell, I work for a company that uses subcontractors. Each subcontractor is graded in about 7 different performance categories. I receive daily excel files that give us the results for each category. I get separate files for each category.

What I do right now is use a master excel file. Every day I take each file I receive from our parent company and add it to the master file. I have a huge excel file that has a separate tab for each performance category. Then I have a "Report" tab where I use various formulas to summarize a sub's performance over a certain date range.

Essentially, the report tab is just a neat and concise way to display the stats of an individual sub, an office, a region or the whole company. I use tons of VLOOKUP and COUNTIF(S) formulas to look over the hundreds of lines of data and bring back the stats. It works but it is just not very efficient as I get hundreds of new lines of data every day.

View 4 Replies


ADVERTISEMENT

How Do I Program Multiple Conditions Within A Cell In Excel?

Jun 3, 2009

Trying to program cell C1:

If A1 is between .81 and 3, AND if B1 is Adult, then C1 is Priority Mail
If A1 is greater than 3, AND if B1 is Adult, then C1 is FedEx Ground
If A1 is Less than .81, AND if B1 is Adult, then C1 is First Class Mail
If A1 is Less than .81, AND if B1 is Child, then C1 is Hold for Inspection

etc.

There will be a list of ~45 conditions that will populate a specific value in C1. Can this be done? If so, how?

View 6 Replies View Related

Accessing Data From Worksheets In Multiple Workbooks

Jun 27, 2008

I have read all the rules, and the guidelines for writting a thread title, and can only hope that my title meets those criteria (Dave, if they do not please do not ban me). I am fairly new to VBA, I have been able to edit other peoples code, and have created a few macros myself, and although I think I know how to use a loop and arrays, I sometimes lack some of the basics, this may be one such case. That said here is the problem I am facing.

I am creating a database, and as such I am sent several workbooks, with each workbook containning several worksheets. Within these workbooks, the naming convention of the worksheets is consistent, though each workbook may contain different number of worksheets. (the name of the worksheets represents a specific size/format).

What I have is a "Setup sheet" that I would like populated with all the data from specific worksheets in the various workbooks. So I would need to be able to get the macro to check all the workbooks, and if it finds a worksheet entitled "Sheet B" (for example) that it copies all that data into the "Setup sheet", and progresses onto the next workbook. I have the loop working and am fairly confident on this side of things, though where I am struggling is getting the Macro to know what worksheet to look at when going through the workbooks.

I need the macro to be fairly dynamic in which Worksheet it looks at so I don't want to simply hardcode the Worksheet name into the macro, rather I would like to be able to specify the worksheet name based on a cell value in the "Setup sheet". so going back to my earlier example if the cell value in the "Setup sheet" = "Sheet B" then the macro should get data from the worksheets entitled "SheetB" in the workbooks, if the cell value in the "Setup sheet" = "Sheet F" then the macro should get data from the worksheets entitled "Sheet F".

View 2 Replies View Related

Accessing Bank Balances Automatically With Excel?

May 31, 2014

Is it possible to have my Excel cash flow sheet access my bank checking account and automatically update with my current balance?

View 1 Replies View Related

Excel / SQL - Accessing DB Where Values Of Column In Range

Oct 5, 2013

First I have a excel with a selection of customers:
Column A = customer id
Column B = customer name
...
this excel contains about 500 premium customers

Secondly, on an external server I have a database with the details (adress, phonenumber, ...) of about 100.000 customers

Now I would like to display the details from the database into the existing excel. For that I was about to write a sql-query to copy the data I need to have into the excel (sheet2) to access it from sheet 1 with vlookup (based on the customer id).

But the large amount of data makes excel to work very slowly and it is impossible to keep on working like that. So now I was looking for a way to create a selective sql-statement.

Something like this:
select details from db
where customerid in (all the customer id's mentioned in sheet 1)
without writing all customer id's manually

View 2 Replies View Related

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

Excel 2010 :: Accessing VBA Module From UserForm On Workbook Open

Feb 13, 2013

I am using Excel 2010 and late binding to generate reports. The sheets are blank and unused. I have the UserForm being opened on Workbook_Open and the actual VBA for doing the work in a separate macro in the workbook. All the posts I have found are dealing with accessing cells from the UserForm and I haven't found any dealing with connecting a macro to the UserForm output.

My question is this: I want to return from the UserForm the flags of which reports to run to the (unopened) macro containing the code to do so. How do I open the macro and pass the variables to it?

The UserForm is creatively called "SelectionForm" and the macro is called "DailyFTP" with a Main sub as the entry. I know I can use the code under to capture the radio button and checkbox choices. I have other variables like the names of files set as global variables in the DailyFTP macro. The macro doesn't exist until SelectionForm opens it, so they must be set after the macro is in memory.

View 6 Replies View Related

Grids And Colour In 2007

Jan 29, 2008

Is there a way to Show the Gridlines in Excel 2007, after an area has been selected, and highlighted with a colour?

As near as I can see, once you put the colour in, then the cells are still there, but the Gridlines just go, or is this incorrect ?

View 9 Replies View Related

VBA Code To Copy Selected Multiple Columns To Multiple Rows In Excel

Mar 13, 2014

I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.

My original data are in the form of the following:

invoice_no
product
sales
qty
total

[Code] .....

I want to display the data in another sheet in the following format:

sales_a
sales_b
sales_c
sales_d

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

View 2 Replies View Related

Where Was My Excel Program Started From?

Oct 20, 2008

I start my excel program from a "Shortcut" in "Folder 1".
That shortcut points an Excel program in "Folder 2".

When Excel is started,
Parent.Path returns "Folder 2",
but I really need to know what "Folder 1" was.

It seems that should be available as
when I do "Save File As"
it is pointing to "Folder 1".

Is there a way to find what "Folder 1" really was from the VBA enviornment.

View 9 Replies View Related

Access Another Program Via Excel VBA?

May 22, 2012

I am trying to access another program via Excel VBA. The idea is that, we fill out out time and attendance sheets for work on excel workbooks. But, we have to go into a terminal program to enter this information so it can be uploaded to whereever it goes.

I am able to get the Shell command to work. But, it will be riddled with sendkeys commands and application.wait. I am sure that this is not the best way to program something like this. I believe I read that for this to work that it has to be COM compliant. I don't know. The program is a Java based terminal program.

This works for me

Code:
ShellExecute 1, "Open", "C:UsersPublic******lib***_core.jar"
But this does not

Code:
CreateObject "C:UsersPublic******lib***_core.jar"
It gives me a Run-time error 429, ActiveX component can't create object.

View 1 Replies View Related

Break Tracker Program Using Excel Vba

Jul 28, 2008

i am creating a break track program using excel with vba. My excel file contains the data for all employees. I have a Userform where the user will enter his employee ID which will pull up his data. I have 3 option button in which the user choose what time he would start his break. Once the user click the start button, the time he started his break will be placed in a cell and a dialog box will appear stating the time the user needs to be back. Once the user click the end button, the time he ended his break will be place on a cell as well and then it will show a message "on time" if the user came on time else if the user was overbreak, the overbreak amount of time will be displayed. I have attached my sample file together with some vba code.

View 3 Replies View Related

Imported Data From Another Program To Excel

Jan 5, 2006

I imported data from another program to Excel, it is an address database. Some of the entries are all capital, some are all lowercase, some are proper. I would like to switch everything to proper. I have tried numberous times to do EXACTLY what I am reading about copy and pasting, entering the formula, designating the cells I want it to apply to. However, when I do it, nothing changes.

View 9 Replies View Related

Excel Music Program Not Sorting Properly?

Jan 25, 2013

For the last half dozen years I have been using a simple Excel table to sort Christmas mix cds. The headings are: song, artist, album, year, and songwriter. This year after adding the additions, I tried to sort and it would not work. The automatic sort stopped at the end of the previous years' work and did not pick up the additions. When I try to include this year's additions, the sort grays out. I'm sure it's something simple. Adding additions to my brother's and daughter's lists worked fine.

View 5 Replies View Related

Program Multiple Elements Of Multi Dimension Array Simultaneously

Aug 23, 2012

Is there any way to do this part of code without calling out each element individually?:

VB:
Dim aSequence(7, 1) As Integer
'setup Evaluation sequence
aSequence(0, 0) = -1

[Code]......

View 2 Replies View Related

Excel 2003 :: Search Condition And Replace Program (VBA Coding)

Nov 12, 2011

I have two work sheets where I have data.

Sheet1 contain daily input table which as follows:

Code:
Namesalary Bonus Check
XX1000 2 FN
XY900 1 NA
YY1100 2 FN
ZY1500 3 DP
ZZ1250 2 FN
AA1050 2 NA
AZ 950 1 FN

Sheet2 have table where all the information is saved. So we can say this is database of sheet1. Which store every day information of sheet 1.

Code:
SALARY DATABASE
Name SalaryBonus

What I want to do is that ---it copy all the data which fullfile condition FN and move to the Sheet 2 which is salary database. Two important things to check is that. IF name already exists in the sheet 2 then it replace old info with the new one. Second thing is to sort the whole table(Salary database-sheet 2) according to A-Z (Name column). I use office 2003. I hope I provide all the information.

View 9 Replies View Related

Link Excel To Another Program - Only Showing Data / Information When Needed

Jul 3, 2014

However, I have got an Excel file which contains a lot of information. There are four sheets with large Pivot Tables.

But the thing is, the plan is to do the tables much larger, create tables which more specifically shows information. When doing so, the tables will show much more information, and therefore be much larger. It will be difficult to read and understand the tables because of the large amount of information.

What I want is to store information in, for example, another program such as Microsoft Access. There, I can put a huge amount of information and then link it to Excel, so that in Excel I only see the Access stored information when I "search" for it with y created slicers. Like, when I use my slicers and click on one category it sort out and shows me the information i want, even though it's stored in Access. It only shows it in Excel when I "ask" for it. This is what I have thought.

View 6 Replies View Related

Program Or Function To Lookup And Locate Typed In Data Across Multiple Workbooks

Jun 12, 2014

have a formula or something along those lines that will look for the data you type in to the selected cell and will show you on how many workbooks it has been entered in. For example, I have workbook a, b, c, d, e, f. all have 12 sheets. On these 12 sheets there is a place to enter the serial number of an item. What I would like to be able to do is on a different workbook be able to type in a serial number and have it show which workbooks it has ever been typed in and its location on said workbooks. and if possible the results of the item which would be whether it was accepted or rejected( this bit is not totally must have. but the workbook and sheet location is a must.)

View 1 Replies View Related

Prevent User From Accessing The Excel File When It Is In Use By Another User

Jul 22, 2013

An Excel file named CLEARING is availble in thr central server system. it will be accessed by 5 or 6 staffs from some other systems what i need is when one person accesses/edits this file the others should not even able to open in the read only file ,when the file is saved and closed ,then only the next person should access it

View 1 Replies View Related

Excel 2003 :: Expanding List To Use In Mail Merge Program To Produce Tags For Direct Mailing

Sep 25, 2012

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.

View 6 Replies View Related

Print Color Objects But Not Objects That Are Highlighted / Colored For Input And Grids?

Dec 27, 2013

So I'm creating a grid worksheet for engineering calculations and I have a couple questions about the best way to do it. I've been messing with excel for my calcs for about a decade now, and I every once in a while I try and improve them.

First: I will have several input areas that will either be colored text or shaded background (either works for me). I don't want these 'input required' objects to print as color, just black. But I want my logo at the sheet top to print as color. I've only found ways to not print any color. Can I print the logo as color and the 'input required' stuff as black?

Second: When I do calcs by hand, I write them out on 10x10 grid paper. Each 10x10 grid is one inch. In the past I've created this grid out of the cells, which works. I frequently need to change formulas around though, and each time I do this, I end up needing to mess with the grid cells also. Is there a way to create the grid and have it in the background so it doesn't need to be adjusted each time I change formulas? I wan't the grids to print, and also want to see them on the screen, as I sometimes draw simple objects along with the formulas.

View 9 Replies View Related

VBA Code Error - Extract Data From Multiple Workbooks To Single Excel Spreadsheet

Mar 28, 2014

I have a folder with multiples excel sheets

Destination : C: Project CustomerExcel

I would like to extract and compile the information contained in these cells:
a5,c5,a6,c6,c7,a14,g14,e16,g16,e18,i18,a20,g20,h22,j22,h24,l24 all the sheets.

New sheet would contain the information of each sheet eg. Column A2= file name

And Row B2 to R2 or whatever will be the corresponding cells mentioned above for each cell. The code I have only brings back the file name but only a5 from the range but not the rest.

VB:
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range

[Code] .....

View 5 Replies View Related

Excel 2010 :: VBA Code To Search Multiple Columns And Delete Any Duplicate Cells (not Rows)

Jun 12, 2014

My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.

View 14 Replies View Related

Excel 2010 :: VBA Code For Inserting Text In All Column B-cells Of Multiple Selected Rows

Jul 11, 2012

Software: Excel 2010, Windows 7

What is the VBA code for inserting text in all column B-cells of multiple selected rows?

I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.

I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.

As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.

View 8 Replies View Related

Using VBA Code In Visual Basic: Develop A Stand Alone Program In Visual Basic

Jun 3, 2006

I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts. But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention. Then once it updates, I import the updated *.pri file back in our Portfolio program. I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). how to use external files and Excel VBA code in Visual Basic?

Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet.

Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String)
'Below are Excel VBA codes
'Uses the test.csv to look up tickers in test.pri and update the price in it
Dim PriceFile As Workbook, BigChartFile As Workbook
Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet
Dim MaxRows As Long
Dim PriceFileRow As Long
Dim BigChartRow As Long
Dim BigChartFound As Boolean
Call CheckBookOpen(BigChartPath & BigChartName)
Call CheckBookOpen(AxysPricePath & AxysPriceName)
Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv...............

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







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