Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

edit/input records in multiple tables from a single form

Hi,

I am trying to set multiple tables as a record source for a form in MS Access.
This is - to be able to edit/input records in multiple tables from a single form.

Subforms will not do here due to the nature of the project - the design is set and can not be changed.

I would greatly appreciate if somebody can suggest a workable solution.

Thanks in advance
Irina


Post your answer or comment

comments powered by Disqus
I have a Access Database. I want to be able to import tables from a different Access Database. I have accomplished this task with the TransferDatabase Action. I have code in place that will use a Open FileDialog to allow the user to Select the database they want to import tables from. I need a function that will import all the tables in the database that was selected without know the table names in the other database.

I created a similar function to delete all the tables in the current database before importing new tables. I need the Importing code to peform a similar task except instead of deleting the tables in the current database, I am wanting to import all the tables in the database selected by the user without knowing the table names.


	VB:
	
 DeleteTables() 
     
    Dim TblName As String 
    Dim obj As AccessObject, dbs As Object 
     
     
    Set dbs = Application.CurrentData 
     
     ' Search for closed Tables.
    For Each obj In dbs.AllTables 
        TblName = obj.Name 
        If obj.IsLoaded = False Then 
             ' Delete the tables.
            If Not (Left(TblName, 4)) = "MSys" Then 
                Access.DoCmd.DeleteObject acTable, obj.Name 
            Else 
                 
            End If 
        End If 
         
    Next obj 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I hope I explained this well enough. Any help would be appreciated.

Hi all,

I would be grateful for any help you could provide with this query.

Basically i have a lot of repetitive tasks to perform in work and need to speed them up so i can spend more time on interesting stuff.

I am attempting to record a macro from a single work book that creates and saves multiple work books containing graphs of the data and the relevant data from the original workbook.

I would also like to create hyper links for these graphs in another work book which contains all of the data graphed weekly.

The data is spread through out the workbook so simple dragging will not work. I must do this for the start of every week. So for example i would generate data for drip rates for 50 seperate samples for week one then graph them and save them in a folder under week one then hyper link to them from another work book and then do the same the next week from data generated that week.

I would truly love to automate this process.

Thanks

Hi All,

I need help with code or some how try to make my data show up in multiple columns from a single column as attached in the example (this is not the entire column - it goes on for 10000's of data values).

The single column is split up by the same heading - i.e. Calcein_Shape Area and the number of data points varies between each heading within the same single column.

I would like these data points to be listed in multiple columns all labelled with the same heading - i.e. Calcein_Shape Area with the data points underneath as listed in the single column.

Could someone please help me to write this code for VB?

Your help is greatly needed!!!example.xlsx

Hi all, sorry for the length of this post, I'll try to keep things as
simple as possible. I've been given the task of maintaining a
spreadsheet with all of the data showing in pivot tables from a single
source which would be updated every month.
The problem arises when it comes to the shear scale of the worksheet
required, in simple terms the main worksheet shows the following -

* Total sales by company types, regions and product types. (4 pivot
tables in total)
* Average sales by company types, regions and product types. (3 pivot
tables in total)
* Total sales by product name sorted by name and total (2 x 3 pivot
tables)

I then have 9 more copies of this sheet which filter the results as
such -

* Region. (3 regions so 3 sheets)
* Region + company type (3 regions, 2 company types so 6 sheets).

The lazy way that I could produce this is by creating the first sheet
and then just make 9 copies of it and change the Page Fields according
to the relevant region and/or company type but this is arduous and
doesn't really allow much flexibility.

Also the first time I created this report I based all of the pivots
off of a single pivot whose data source was fixed coordinates and of
course now I want to add data I've had to alter them, in fact now to a
data name. The problem is that all of the pivots 'lost' the fact that
they refer to the original pivot so I've had to go through and change
their source manually.

Can anyone think of a way in which I could automatically create all of
my worksheets either through VB or another way?

Thanks in advance.

I am attempting to put together a database with various different information in one of the columns. Generally this is text based which isn't a problem, howevr some of it is in the form of tables. What I require is a method of putting one or in some cases multiple tables into a single cell to allow other criteria in my spreadsheet to be used to search / sort this info and it all be found in the one area.Also these tables will have different numbers of columns and rows. They are only to be used as a reference and not used in any other excel formulas etc.I'm sure that there must be a simple way of doing this but for some reason I can't work it out.If anyone could help me on this it would be greatly appreciated.

It would be nice if the Chart Wizard had the option to plot 'Series in
Tables' as well as in columns or in rows. I frequently need to plot data
that are in tabular format (e.g., months in columns and years in rows), but
what I really want to do is plot all of the data in the table as a single
time series. In many cases, the data I am plotting are imported into the
spreadsheet (output from other programs), and I don't necessarily have the
option of easily changing the format to put all the data in a single row or
column.

Does anyone know of a way to accomplish this?

Thanks!
Hugh John Cook

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...excel.charting

It would be nice if the Chart Wizard had the option to plot 'Series in
Tables' as well as in columns or in rows. I frequently need to plot data
that are in tabular format (e.g., months in columns and years in rows), but
what I really want to do is plot all of the data in the table as a single
time series. In many cases, the data I am plotting are imported into the
spreadsheet (output from other programs), and I don't necessarily have the
option of easily changing the format to put all the data in a single row or
column.

Does anyone know of a way to accomplish this?

Thanks!
Hugh John Cook

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...excel.charting

Hello,

I have an excel sheet that has data in multiple rows for a single individual. There may be 1 row for an individual and there may be many rows for a single individual. The first column is a unique id. The columns are as follows:

FILENBR; DOB; RACE; SEX; INCIDENTDATE; LONGDESCRIPTION; SEVERITY; SEVERITYCLASS

I am attaching an example of the data.

I found the following code online (forums.techguy.org) and it worked, it seemed, but upon further inspection it combined individuals who were unrelated.

Thank you for your help!

*******************************************************************

Insert a new sheet, rename it New. Then with your original sheet selected, try this macro:


	VB:
	
 test() 
    MainSheetRows = Range("B" & Rows.Count).End(xlUp).Row 
    For Each Cell In Range("B1:B" & MainSheetRows) 
        x = WorksheetFunction.CountIf(Range("New!B:B"), Cell) 
        If x = 0 Then 
            y = WorksheetFunction.CountA(Rows(Cell.Row)) 
            Cell.Offset(, -1).Resize(, y).Copy Sheets("New").Range("A" & Rows.Count).End(xlUp).Offset(1) 
        Else 
            y = WorksheetFunction.CountA(Rows(Cell.Row)) - 2 
            z1 = Application.Match(Cell, Range("New!B:B"), 0) 
            z2 = WorksheetFunction.CountA(Sheets("New").Rows(z1)) 
            Cell.Offset(, 1).Resize(, y).Copy Sheets("New").Cells(z1, z2 + 1) 
        End If 
    Next Cell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I would like to add a new line but the field was not in CUSTOMER table but in ISSUE table in the same database. How can I add the "Custom_4" field under " ISSUE " table in this query. Thanks in advanced.

yhb.4u

I summarise my data (more than 5000 records) which is compiled daily from different sources using a pivot table, comprising of more than 20 unique items.

On a daily basis, I have to break the database into 20 files (i.e. the number of unique items in a particular field in the file) and circulate the file to different persons

I was looking for a way wherein I could automatically segregate the database into 20 different files with the filename and sheet names be having the name of the unique item plus the number of records in the file in brackets.

Help will be greatly appreciated.

Hi Friends !!!
Can anyone help me out in solving this problem where I have been paused? I have Work Book containing several sheets i.e. 80+ or more sheets. In that, there are 66 sheets(Sheet11 to Sheet76) records same pattern of data from a user form. In each of the 66 Sheets, a calculation row is set for the entered data for a number of columns(Auto Sum type), This calculated data from the 66 sheets is required to be duplicated in the summary sheet in a specific column. In the summary sheet, each row is set for each sheet starting from Sheet 11 to sheet 76 (already named). An example may help you in understanding better.
(a) Let the Work Book be MyProject1.
(b) Same pattern of Data is sent from a User Form ( say UserForm1) to 66 Sheets.
(c) Row No. 64 in each sheet (of the 66 Sheets) calculates some value in time and numerical value.
(d) I have a summary sheet in which I specified Columns to get the data from the 66 sheets.
(e) Column ‘N’ will store the data of Z64 cells of the 66 sheets. Similarly, Column ‘O’ will have AB64 and Column ‘P’ will have the data of AD64 cells of the 66 sheets.
It may be noted that, the sheets are not serially named or numbered. Secondly the work book also
contains other sheets having formulas for different occasions. My need is that can a VBA code be placed for the same so that, the Summary Sheet can be updated as and when the data is entered through the user form simultaneously while updating the 66 work sheets. These 66sheets are defined for a heads used in a combo-box and against these 66 heads. The Userform holds the command button to transfer the user input data to the Summary sheet.

I am very new to excel and do not know how to proceed. Also I do not want to copy and paste link from all 66 pages which is a tedious job. Request help from all to have a code in shorter length to cater for the need. This should happen when I click the Update button that I have placed on the user form for the Summary sheet. Also the followings may be considered while writing the code.
(a) The cells (Z64, AB64 and AD64 from each sheet of the 66 sheets) I want to copy to the summary sheet, are holding the data that is the calculated values in the respective sheets. Will it change while getting transferred to the summary sheet?

(b) Secondly, what I need is to display the cell values ofthe 66 sheets same to same in the summary sheet in their designated cells. What I mean to say is will it transfer the formula along with the cell values to the summary sheet?

(c) The summary sheet starts recording the data from Row No. 2 onwards. The First row contains the Headings that is manually done. So data to be recorded in N2 to N67, O2 to O67 and P2 to P67 for the Z64, AB64 and AD 64 cells of the 66 sheets.

Awaiting response.

Thanks a lot .

Regards,

nm766

I'm trying to update a table with a picture (of a map) whose location is inputed via a user form. Within the form there is a place for the user to input the path to the map and then a place to enter the actual map name (with its .bmp extension). I'm trying to get this into a table called tblMaps with a field called Image that has been given a data type as OLE Object.

The code I'm using looks like this:


	VB:
	
 
Dim map As String 
 
map = "'" & Me.Location & Me.MapName & "'" 
mySQL = "UPDATE [tblMaps] set [Image] = " & map & " Where [ID] = " & CInt(Me.ID) 
CurrentProject.Connection.Execute mySQL 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I run the code, in the image field within tblMaps instead of seeing the phrase 'Bitmap Image' it puts in 'Long binary data'

Any ideas on how to fix this?

Thanks in advance

Dear Sir and All my Dear collegues,
Can I transfer the data from a user form to 65 work shets (named differently) in the same work book ? Though I have succeeded to do so only for two to three sheets, I don't know whether it is possible to do for 65 sheets/ Secondly, It is becoming possible when the same set of code is duplicated for each sheet by changing only the sheet name. so for 65 sheets, I have to write the same code for 65 times. is there any alternative method to do so ? kindly help me out. What I have followed is : I have created a command button on the User form and controlled the user input to get it written to individual work sheets. Each Item from a combo Box list (the first selection item of the form), the user form inputs go to a prenamed worksheet. I have tried for three items and I have to repeat the codes three times, one time each for each item.

Hi All,

i need to paste multiple table from one sheet(source sheet) to another sheet(Destination sheet) of the same workbook. The problem is the table needs to be arranged one below the other with a space of 2 rows, and the length of these table varies eveytime. So I need to write a macro which counts the number of line items of the preceeding table and keep a gap of 2 rows and then paste the next table. The table headers remains same everytime. Can i refer to the table names to count line items for each table. there are four table i need to paste one below the other.

let me know if I am clear.

Is it possible to create a pivot table from a form in Access? The reason I
need to use the form is because I need one of the formulas that is in the
form? If yes please send me the steps.

Thanks.

nazzoli

Hi,
I have a multiple names in sheet 2 column 1 (approx 12)

I want to be able to select multiple items from a drop down list in sheet 1 and populate them all in one cell, seperated by comma, or semi colon.
The drop list will also auto update if any name is later added in the sheet 2.
Please help.
Thanks and regards

Hello,
I am looking at some past postings:

http://www.excelforum.com/excel-work...ng-values.html

http://www.excelforum.com/excel-work...ng-values.html

I am looking to return multiple values from a table.

I do the CTRL+SHIFT+ENTER and get the {}

However how do you get more than 1 cell of values?
Should it automatically spread to more than 1 cell?
do you copy and paste the formula?

neither works for me.

thanks

Referring to the post http://www.ozgrid.com/forum/showthread.php?t=69644

I would like to go little bit further and see is there a way to find and match not just in multiple columns but in multiple tables (see the example)

Hi Friends,
Can anyone help me on the following issue :

(a) I have a work book say MY PROJECT 1.

(b) In that I have a work sheet say DAILY SCORE DATA. This Data is stored in the Cell Block of E2 to M67. M Column is the sum-total of Column ‘E’ to Column ‘L’ for each row. Means E2+F2+G2+H2+I2+J2+K2+L2 = M2 (and similar for other rows till 67th row). These data are fed from a User form and get stored when OK button on the form is clicked.These data input from the user form is also reflected in another worksheet Say BULL SCORE DATA (Y2:AF67) for Columns E : L. The data of Column M(sum total) is reflected in Column Q of the sheet simultaneously on clicking the OK button.

(c) I need to reset the data in the above said cell blocks (E2:M67 in Sheet DAILY SCORE DATA and Y2:AF67 and R2:R67 in Sheet BULL SCORE DATA) on next day (assuming the data was entered today evening) by clicking a command button (named RESET) on the same user form. As a result the data in cells should reset to ZERO. This should be conditioned with the computer’s date calculation so that if I want to reset on the same day , it won’t reset. It will be reset only on the next day when the computer date changes to next day.

(d) It may be noted that, some rows in random, might contain ZERO data previously. So while resetting, it should not create any problem.

Your precious help will be highly appreciated.
Thanks in advance.

With regards,
Nm766

I want to import multiple tables from the same web page
that I find with a google search. Is there an easy way to
do that?

This is what I did (sigh). Click on Data > Import External
Data > New Web Query, go to google.com and search for
"1926 s&p 500" without quotes. Click on the link for "AIM
Investments: How to Survive the Stock Market's Ups and
Downs" at
http://www.aiminvestments.com/portal...0026b4bf0aRCRD .
Page down to the several tables under the titles "The Stock
Market: The Good Years" and "... The Bad Years". Select one
table and click Import.

I have to repeat the whole thing, including the Google search,
for each table :-(.

I'm trying to get a function that will allow me to pull up multiple entries
from a single vlookup (or multiple lookups perhaps that would pull sequential
records)

So lets say I have a worksheet with:

A B C
Date Name Invoice Number

I need the last 5 invoices from name X to display on another worksheet in
seperate cells.

Ideas? I've tried the User Defined vlookups tool I found online and couldn't
get it to work (and yes I've Cntrl-Shift enter to make it an array).

I need to pull values from multiple cells in a single row and place
them into another worksheet in specified cells. I have setup a pull
down in Worksheet2 to show all of the values in a column on Worksheet1.
When I select one of these values in the pull down I need to grab 6
other cells in Worksheet1 (from the same row) and place them into
Worksheet2 in different rows/columns.
Is this possible, and do I need to use a macro to accomplish this?

I appreciate any help.

Thanks,

Chad

Hi

I have another stick in the road. I have created my database with Excel as front end. It is coded so that user enters info in userform and then it gets downloaded to an access database as new record.

Problem I need to solve is how can I update records in access table from excel spreadsheet.
I currently can pull table info from access into a userform which user updates and then it is saved to spreadsheet I just need to be able to update whatever has been changed to the access table.??

If someone could help with some code that would be great......

Transpose Multiple Rows into a Single Column - EXAMPLE.xlsxI need a Lifesaver! Can someone assist with providing the quickest way to perform the following functions within excel across thousands of rows? Also, see table examples below.

1. Insert 3 blank rows between each row with data.
2. Transpose multiple rows into a single column and displayed independently

STARTING FORMAT:
A B C D E F G 1 DATA DATA UL UR LR LL 2 XXXX XXXX ABC DEF GHI JKL 3 XXXX XXXX MNO PQR STU VWX 4 XXXX XXXX YZ1 234 567 890 5 XXXX XXXX ABC DEF GHI JKL

NEED THE DATA TO LOOK LIKE THE TABLE BELOW WITH SPECIFIC DATA IN EACH ROW TRANSPOSED IN ONE COLUMN (SEE COLUMN H):
A B C D E F G H I 1 DATA DATA UL UR LR LL ALL 2 XXXX XXXX ABC DEF GHI JKL ABC 3 DEF 4 GHI 5 JKL 6 XXXX XXXX MNO PQR STU VWX MNO 7 PQR 8 STU 9 VWX 10 XXXX XXXX YZ1 234 567 890 YZ1 11 234 12 567 13 890 14 XXXX XXXX ABC DEF GHI JKL

Note: The data that I need to transpose to display in the single column is always preceded by additional columns with data.

Note 2: If you are providing a MACRO and/or VB, please tell me how/where I need to insert those codes.

Thank you in advance!


No luck finding an answer? You could always try Google.