Paste Special, Values, Transpose

Is there a way in Access 2007 to use the paste special features available in 
Excel?  I have data in Excel that I will be copying and pasting into a form 
in Access.  Importing would be tricky because not all tickets on the 
spreadsheet need to be copied.

The issue is I receive the spreadsheet from another workgroup and they've 
merged cells and have the data going down one column, 19 rows.  I can copy, 
paste special within Excel to get the data formatted right, then copy it into 
Access.  I'm hoping there's an easier way.

Any suggestions?

Thanks!
Jim


0
Utf
1/20/2010 2:05:02 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
1924 Views

Similar Articles

[PageSpeed] 54

Jim -

A copy/paste special in Excel sounds easy enough.  You don't have that 
option in Access.  If this is a repetetive process, then I would suggest a 
macro in Excel to clean up the data, putting it into an empty worksheet.  
Then you could import or copy/paste from that into Access.

-- 
Daryl S


"Jim" wrote:

> Is there a way in Access 2007 to use the paste special features available in 
> Excel?  I have data in Excel that I will be copying and pasting into a form 
> in Access.  Importing would be tricky because not all tickets on the 
> spreadsheet need to be copied.
> 
> The issue is I receive the spreadsheet from another workgroup and they've 
> merged cells and have the data going down one column, 19 rows.  I can copy, 
> paste special within Excel to get the data formatted right, then copy it into 
> Access.  I'm hoping there's an easier way.
> 
> Any suggestions?
> 
> Thanks!
> Jim
> 
> 
0
Utf
1/20/2010 6:48:04 PM
Reply:

Similar Artilces:

Can't Copy/Paste Journal Info in GP 7.50
Hi, I'm trying to help someone new to GP who'd like to be able to copy and paste journal information into a financial batch (Transactions > Financials > Batches). They're attempting to copy journal details from an Excel spreadsheet and paste into GP, but despite this working in every other application, it fails to work in GP. I'm aware it's a fairly old version (7.50g10), and please forgive my lack of knowledge when it comes to GP, but it seemed like a fairly reasonable question that experienced users may have come across before. Thanks for reading. Best reg...

join columns, keep both values
I have a multicolumn database with text entries. I want to join the cells in Col. B with the cells in Col.C and keep all the text. For instance, B3 may be "1N", C3 may be "4W". I want the combined column to read "1N 4W". How do I do this?!? TNX. -- FourPenny ------------------------------------------------------------------------ FourPenny's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23864 View this thread: http://www.excelforum.com/showthread.php?threadid=375062 Try this: =B3&" "&C3 -->"1N 4W&q...

automatically assign value to query
Hello, Ive got a database where staff login just using their name, and the login button has a macro assigned as follows Condition : Not IsNull([cboCurrentEmployee]) Action : SetTempVar Arguments : CurrentUserID, [cboCurrentEmployee] Certain contacts in my database are assigned to a member of staff and call sheets are set up using a query that requests that member of staffs name. Is there a way of automatically setting the criteria of the query to only show clients that are assigned to the member of staff that has logged in? Thanks, please let me know if i need to explain th...

Get special folder path
Hello, I'm developping an app with VC++ 6.0 and MFC, and I need to get the path of Windows special folders (in particular, folder "My Music", wherever the user may have moved it). I was told there is a possibility for apps running under Windows XP to get this information ? Does somebody know how ? Thank for any help -- Ceci est une signature automatique de MesNews. Site : http://www.mesnews.net ByB wrote: > Hello, > > I'm developping an app with VC++ 6.0 and MFC, and I need to get the path > of Windows special folders (in particular, folder "My M...

Pass value to combo box
I have an unbound text box (txtSaveVal), an unbound combo box (cboName), and a string variable strSaveVal. me.txtSaveVal=strSaveVal .............. Works fine me.cboName=strSaveVal ............... Does Not Work I simply want to put the value of strSaveVal into the combo box, as though I had typed it in, BEFORE pressing the enter key. Why isn't this working? esee, Why are you doing this? A combo box is not intended to accept a value from another control, but provide a means to select a value from a list of values either from a table, a query, or a manually ...

Using cell value in VBA sub
I'm using the following sub Sub FindRow() Dim rngFound As Range Set rngFound = Range("A:A").Cells.Find(What:="test", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry ""test"" was not found" Else rngFound.EntireRow.Select End If End Sub My problem is that if I excange Test with a1 - wanting to look the value in cell a1 I always get the first blank a-cel...

Syncronise zero values
I have seven columns of data to chart. Columns A & B have values aroun 500,000 to 600,000 and I wish to show them against the right hand axe as a line chart. The remaining columns have values from -50,000 t +200,000 and I wish to show them against the left hand axes as colum chart. Is it possible to get their zero values on the same level? Is this making sense to anyone? Thanks for any hel -- Brisbane Ro ----------------------------------------------------------------------- Brisbane Rob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2509 View this thread...

Comparing column values
Hello, I have an worksheet with over 1000 rows. For each row, there are numbers in 4 columns. Column A should be the lowest number for each row, with each subsequent column being a higher number as you read across for each row. I've stumbled across a few errors and now I'm worried there are more errors. Is there a way to automatically check and see if cell A1 is a smaller number than B1, and B1 is smaller than C1, and C1 is smaller than D1 (and continue this for all rows)? Having to do this manually for as many rows as I have would take forever and I'm sure I could easil...

Things that have gone wrong with my Money file in the past 2 days
1. My two bank accounts that download statements via the MSN Money website (which in turn uses the Yodlee service) stopped updating both on the website and within Money. The error message said to fix it I needed to stop using the "online services" then reenable it, but when I did, I received an "Account setup is unavaiable" message on one of the two accounts. I did this 4 or 5 times, for kicks. Finally today I got them both to work.. for now. 2. The starting balances of two other bank accounts conveniently reset themeslves from one value to another, seemingly randomly, ...

Excel range truncates when Pasted as Picture to PPT & Word
Hi... I have been trying to copy an Excel Spreadsheet into PPT, but have had problems. In order to solve it, I created new .xls and .ppt files to create a test, but got the same problems... The following steps recreate the problem: 1) In a blank spreadsheet, I placed a single number in each cell, starting at A1 and going across to AS, until there are 1 through 45 across. Format them in some way... say Red text with an underline. 2) Set the width of all the columns to 2.00 (0.11 inches). 3) Select A1:AS 4) Copy (or Add to Scrapbook) -- The result is the same with both. 5) From Scrapb...

cell value goes up by one count
,Hello Again Could i get some help with this. How does one get an activecell , that has a value of 1, that is copied from sheet1 to sheet2 so that the value changes from 1 to a 2 and so on depending on how many times I copy a sheet. I hope this makes sense Thanks in Advance Allan =sheet1!(a1). I think you are asking for this formula. It will change the value of current cell (doesn't matter on which sheet you are currently working) to value of Sheet 1's A1 cell. Means if Sheet 1 A1 = 100 and you are currently working on Sheet 2 F1, then this formula will show the F1 value ...

How do I record this special transaction: Vodaphone
Hello I own some Vodaphone ADRs. In July they made a mandatory exchange - sadly using the same stock symbol. They exchanged .87 shares for every 1 share. I am using Money 2004 (standard) - how do I record this transaction? -- Leslie ---------------- 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 th...

FrontEnd OWA for special users
Hi, I just want a special users group can through ISA connect to our Front end OWA over Internet. Other people only check email on BackEnd OWA in Intranet.So, disable OWA won't work. for Exchange 2003, how can setup this way. Thanks. Jeffrey On Wed, 19 Apr 2006 14:53:51 -0400, "Jeffrey" <tiefu@email.com> wrote: >Hi, >I just want a special users group can through ISA connect to our Front end >OWA over Internet. Other people only check email on BackEnd OWA in >Intranet.So, disable OWA won't work. for Exchange 2003, how can setup this >way. >Th...

Value list
i need help with making value lists in excel so that i can make my data base consistant with the data i am entering That's not much to go on. What is a value list? And what constitutes making your database consistent with the data you are entering? Post back and explain in detail what you have, what you want to do, and what you want to happen. Some examples are always good. HTH Otto "Maint Girl" <Maint Girl@discussions.microsoft.com> wrote in message news:4EDE3701-B704-4BE8-B449-58662FDCB495@microsoft.com... > i need help with making value lists in ...

How to look up a value in a list and return multiple corresponding
I followed the instructions on the excel help page of how look up a value in a list and return multiple corresponding values but somethings not right. I have tried this numerous times in my excel spreadsheet and it does not return any values let alone numerous values. No values will appear unless I go into the insert menu and click function, but then here it will only give me the smallest value, even when there is more than one. Please help asap! Thank you. How about giving us some details? -- Biff Microsoft Excel MVP "123456789" <123456789@discussions.m...

concatenate more column value
Hi All Any function can be simply below function , No VBA? Next time, I will try add 26 column value. =IF(TRIM(H2)&TRIM(I2)&TRIM(J2)&TRIM(K2)&TRIM(L2)&TRIM(M2)&TRIM(N2)&TRIM (P2)&TRIM(Q2)&TRIM(R2)&TRIM(S2)&TRIM(T2)<> "", "Y","N") moonhkt Hi This formula should do it: =IF(SUMPRODUCT(--(TRIM(J2:T2)>""))>0,"Y","N") or =IF(SUMPRODUCT(--(LEN(TRIM(J2:T2))>0))>0,"Y","N") Regards, Per "moonhkt" <moonhkt@gmail.com> ...

Copy and paste problem #2
I am using Excel 2000. I have made a W2 form that I need to copy and paste for a total of 80 copies. ( I made the form just through using cell borders and resizing cells etc.) All of this is being done on the same worksheet. Through cell protection the user will tab from one cell to another filling out the appropriate information. THE PROBLEM. When I made this I had to decrease the height of some cells and increase the height of other cells. When I copy this down the sheet for a total of 80 copies the cells are set to the original default settings of 17 pixels in height. It copies the form...

Copy & Pasting Worksheets
Is there a secret to maintaining the size of the selected worksheet cells when copying and pasting into Word or PPT? I am using Office 2003. It seems that the size of the selected cells explode when I paste them into other Office documents. Thanks! Try using "Paste Special" and selecting Microsoft Excel Worksheet Object. It will usually keep the formating you were using in Excel. If I just want to select data from an excel spreadsheet and know I won't want to modify the data in the Powerpoint or Word document I will copy the data from excel as a picture. This makes resi...

Formula for counting cells with value 'x' provided Col U = 'y'
Hi guys, Need some help with a formula please. Have tried myself and know that the answer is straightforward but going through a mental block! Col Q Col R A01 58 A01 62 P02 62 Both columns data starts in cell 14 and runs down to cell 10000. I need to count the entries in Column Q that = A01 ONLY IF Col R = 62. Then do the same for Q = A01 ONLY IF R = 58, and so on. The worksheet is a year-to-date file and will be updated weekly with many entries in both columns with varying values. Many thanks. Hi, =SUMPRODUCT((Q1:Q10000="A01")*(R1:R1000...

Formula?
Hi, I have my data set up the following way - the columns refer to th month and the rows are the different projects I'm tracking. Projec "A" has data in months Jan-Oct (10 values), while Project "B" has dat in months Jan, March, July, August & Sept (5 values)-the missing month are blank. Is there a way get the average of the last 4 values fo each project? Maybe using a count function? thank -- Message posted from http://www.ExcelForum.com Hi if your values are in A1:J1 use the following array formula (entered with CTRL+SHIFT+ENTER) =AVERAGE(OFFSET(J1,0,0,1,-...

Problem Pasting
I have a spreadsheet that has 4 worksheets. I can copy and paste to 3 of the sheets by using either "Edit/Copy Edit/Paste" or the shortcuts "Ctrl C Ctrl V", however, on one of the sheets the "Edit/Copy or Ctrl C" does mark the data I want to copy but when I click on the cell where I want to start pasting the data the "Edit/Paste" is not available from the menu and Ctrl V does absolutely nothing. What is causing this problem ? Most likely your worksheet is protected? Click on Tools, and see if you see the option Protect Worksheet, or whether you se...

Create line in chart by filling the serie collection with only 1 value?
Hi everybody, :) Is it possible to create a line in a chart based on ONE value? (to occupy the Series Collections) I�m creating an application that created dynamic charts of the sourc data in an Excel database. This Data is filtered on several criteri and has dynamic name ranges to fill the Charts Series Collections an so create the specific charts. Everything works like a charm but I have a lot of Constants of only ON value. (these are Specifications to make the chart readable) Normally I would fill a column with these values en the have my lin anyway. But because I have more than 150 ...

how to find the sum of certain values in a column.
I want to be able to find the sum of all values in a column that have a particular value in the previous column, sush as follows. A B 1 TYPE VALUE 2 a 10 3 b 10 4 a 10 5 c 10 6 b 10 7 a 10 8 c 10 9 b 10 10 c 10 11 a 10 12 TOTAL 100 I'm able to total the column fine but what I want is the following: A) I want is a formula that will total only those VALUES that are TYPE "a" so that I c...

transpose function creating zeros
I have a named area where I am copying data from and some of it has data , some is empty. It is a mixture of textual, numeric and date values.. otherwise the cells in a column are empty BUT when I use transpose to copy this area it creates lots of zeros in the area not being written to. How can I get it to copy properly. Thanks. --- Message posted from http://www.ExcelForum.com/ One way: =IF(TRANSPOSE(namedrange)<>0,TRANSPOSE(namedrange),"") In article <busyman5_au.12483o@excelforum-nospam.com>, busyman5_au <<busyman5_au.12483o@excelforum-nospam.com&g...

Adding comma seperated values within a single cell
I need to come up with a custom function that will add up comma seperated numerals contained within a single cell. For example if the the cell A1 contains: 4,5,3 I want the result of the fuction (say in a2) to display the result of 12 Any help greatly appreciated. Regards Mike On Tue, 07 Oct 2008 20:39:01 +1100, Michael Toal <M.Toal@bom.gov.au> wrote: >I need to come up with a custom function that will add up comma seperated >numerals contained within a single cell. > >For example if the the cell A1 contains: > >4,5,3 > >I want the result of the fuction (sa...