batch update

If you understand in those terms, is there a way to batch update an Excel 
spreadsheet to an already populated Access table?

Basically, I routinely get spreadsheets of data that I need to amend to an 
Access table. It is very time consuming to copy and paste line by line.

Thanks in advance. 

0
DM
3/12/2008 5:42:51 PM
access 16762 articles. 3 followers. Follow

8 Replies
904 Views

Similar Articles

[PageSpeed] 47

On Mar 12, 12:42=A0pm, "DM" <themusgravefam...@gmail.com> wrote:
> If you understand in those terms, is there a way to batch update an Excel
> spreadsheet to an already populated Access table?
>
> Basically, I routinely get spreadsheets of data that I need to amend to an=

> Access table. It is very time consuming to copy and paste line by line.
>
> Thanks in advance.

Well, If all the columns line up, you could just highlight all the
lines, copy and do one big paste.

There are ways to automate this, but you might want to come up with a
better definition of "routinely".  Four times a day, weekly, or twice
a year?  That will help you decide how much time and energy you want
to put into automation.  Anyway, you might try linking the Excel sheet
as a table, and then running an append query to your destination
table.  You'll want to do your testing on a backup copy of your
database.

0
mcescher
3/12/2008 5:57:21 PM
follow up info:

I've though about linking an Excel spreadsheet to the Access table but when 
I'm not around and it's time for records management and clean up the file 
server, the not so computer savvy co-workers delete or move and break the 
links. Then I get fussed at for not having a reliable product.



"DM" <themusgravefamily@gmail.com> wrote in message 
news:u3tKSiGhIHA.3352@TK2MSFTNGP04.phx.gbl...
> If you understand in those terms, is there a way to batch update an Excel 
> spreadsheet to an already populated Access table?
>
> Basically, I routinely get spreadsheets of data that I need to amend to an 
> Access table. It is very time consuming to copy and paste line by line.
>
> Thanks in advance. 

0
DM
3/12/2008 5:57:37 PM
Thanks,

It could be as often as weekly. The problem, the way I do it anyway, is the 
one big copy/paste either by one roll at a time or add enough new blank 
rolls to account for the one big paste.

Do you have a better way to eliminate the extra task of pre-creating the 
needed blank rolls?

"mcescher" <chris.meinders@gmail.com> wrote in message 
news:1c335da1-c0a2-480a-b0c4-ce1b78d32be0@n75g2000hsh.googlegroups.com...
On Mar 12, 12:42 pm, "DM" <themusgravefam...@gmail.com> wrote:
> If you understand in those terms, is there a way to batch update an Excel
> spreadsheet to an already populated Access table?
>
> Basically, I routinely get spreadsheets of data that I need to amend to an
> Access table. It is very time consuming to copy and paste line by line.
>
> Thanks in advance.

Well, If all the columns line up, you could just highlight all the
lines, copy and do one big paste.

There are ways to automate this, but you might want to come up with a
better definition of "routinely".  Four times a day, weekly, or twice
a year?  That will help you decide how much time and energy you want
to put into automation.  Anyway, you might try linking the Excel sheet
as a table, and then running an append query to your destination
table.  You'll want to do your testing on a backup copy of your
database.

0
DM
3/12/2008 6:16:37 PM
My results with linking to spreadsheets have been mixed, so I always
use importing.

1) I have a set directory where they are to put the spreadsheed that
needs to be imported.
2) If the spreadsheet always has the same name then I supply it in a
txtbox, In some apps if it is always named via a certain convention
(like always "xxxxx 20080315.xls" with the date being a Saturday, and
I supply that name. But in all cases I allow them to modify the name
to whatever they saved it as.

3) Clicking an Import button, checks if the files is there, imports
it, Appends the import to existing tables, copies the imported
spreadsheet to another directory with -yyyymmdd added to the name, and
erases the just imported spreadsheet. One app keeps track of what
sheets have been imported and if it has already been done once it
warns the user and they can abort the import - that option is not
foolproof if they are supplying the name but mostly they don't supply
the name.

Ron
0
Ron2006
3/12/2008 7:36:42 PM
On Wed, 12 Mar 2008 18:42:51 +0100, "DM" <themusgravefamily@gmail.com> wrote:

>If you understand in those terms, is there a way to batch update an Excel 
>spreadsheet to an already populated Access table?
>
>Basically, I routinely get spreadsheets of data that I need to amend to an 
>Access table. It is very time consuming to copy and paste line by line.
>
>Thanks in advance. 

File... Get External Data... Import; select .xls as files of type; 

OR

VBA code using the TransferSpreadsheet method.

Pasting line by line!? Ow. Not *quite* as bad as printing out and retyping but
close!
-- 

             John W. Vinson [MVP]
0
John
3/13/2008 5:17:41 AM
Wow, I thought I knew MS Access. What is VBA?

Also, I didn't quite understand your closing comment. I must be having a bad 
comprehensive day. Thanks a million for the help to all.


"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:m5eht3tb5jp5lc9m7orlpih3ubit72rmr8@4ax.com...
> On Wed, 12 Mar 2008 18:42:51 +0100, "DM" <themusgravefamily@gmail.com> 
> wrote:
>
>>If you understand in those terms, is there a way to batch update an Excel
>>spreadsheet to an already populated Access table?
>>
>>Basically, I routinely get spreadsheets of data that I need to amend to an
>>Access table. It is very time consuming to copy and paste line by line.
>>
>>Thanks in advance.
>
> File... Get External Data... Import; select .xls as files of type;
>
> OR
>
> VBA code using the TransferSpreadsheet method.
>
> Pasting line by line!? Ow. Not *quite* as bad as printing out and retyping 
> but
> close!
> -- 
>
>             John W. Vinson [MVP] 

0
DM
3/13/2008 8:50:24 PM
On Thu, 13 Mar 2008 21:50:24 +0100, "DM" <themusgravefamily@gmail.com> wrote:

>Wow, I thought I knew MS Access. What is VBA?

Visual Basic for Applications, also known as a Module. One of the things you
can do in VBA code is use the "TransferSpreadsheet" method to open an Excel
spreadsheet and transfer its contents into a table.

>Also, I didn't quite understand your closing comment. I must be having a bad 
>comprehensive day. Thanks a million for the help to all.

About pasting line by line? Just expressing sympathy for your onerous chore.

>
>"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
>news:m5eht3tb5jp5lc9m7orlpih3ubit72rmr8@4ax.com...
>> On Wed, 12 Mar 2008 18:42:51 +0100, "DM" <themusgravefamily@gmail.com> 
>> wrote:
>>
>>>If you understand in those terms, is there a way to batch update an Excel
>>>spreadsheet to an already populated Access table?
>>>
>>>Basically, I routinely get spreadsheets of data that I need to amend to an
>>>Access table. It is very time consuming to copy and paste line by line.
>>>
>>>Thanks in advance.
>>
>> File... Get External Data... Import; select .xls as files of type;
>>
>> OR
>>
>> VBA code using the TransferSpreadsheet method.
>>
>> Pasting line by line!? Ow. Not *quite* as bad as printing out and retyping 
>> but
>> close!
>> -- 
>>
>>             John W. Vinson [MVP] 
-- 

             John W. Vinson [MVP]
0
John
3/14/2008 3:13:40 AM
Thanks for the help and the lesson on VBA.



"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:k8rjt3da89j042oun63tlpulpdkcj9t9j0@4ax.com...
> On Thu, 13 Mar 2008 21:50:24 +0100, "DM" <themusgravefamily@gmail.com> 
> wrote:
>
>>Wow, I thought I knew MS Access. What is VBA?
>
> Visual Basic for Applications, also known as a Module. One of the things 
> you
> can do in VBA code is use the "TransferSpreadsheet" method to open an 
> Excel
> spreadsheet and transfer its contents into a table.
>
>>Also, I didn't quite understand your closing comment. I must be having a 
>>bad
>>comprehensive day. Thanks a million for the help to all.
>
> About pasting line by line? Just expressing sympathy for your onerous 
> chore.
>
>>
>>"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
>>news:m5eht3tb5jp5lc9m7orlpih3ubit72rmr8@4ax.com...
>>> On Wed, 12 Mar 2008 18:42:51 +0100, "DM" <themusgravefamily@gmail.com>
>>> wrote:
>>>
>>>>If you understand in those terms, is there a way to batch update an 
>>>>Excel
>>>>spreadsheet to an already populated Access table?
>>>>
>>>>Basically, I routinely get spreadsheets of data that I need to amend to 
>>>>an
>>>>Access table. It is very time consuming to copy and paste line by line.
>>>>
>>>>Thanks in advance.
>>>
>>> File... Get External Data... Import; select .xls as files of type;
>>>
>>> OR
>>>
>>> VBA code using the TransferSpreadsheet method.
>>>
>>> Pasting line by line!? Ow. Not *quite* as bad as printing out and 
>>> retyping
>>> but
>>> close!
>>> -- 
>>>
>>>             John W. Vinson [MVP]
> -- 
>
>             John W. Vinson [MVP] 

0
DM
3/14/2008 5:59:13 PM
Reply:

Similar Artilces:

Payables batch entry problem
Hi everyone, Our payables dept has a problem with Payables Batch Entry where when they edit an existing or enter a new batch and then select computer check as the origin the post option gets grayed out. They're claiming it never was like that and that happened just recently. What could be the cause of it? I've checked the posting options under setup and nothing sticks out at me. I've also checked for stuck batches or anything that might be suspect. Thanks, -- Jim Bourque Jim, Cheque Batches do not get Posted from the Batch Entry Window, they need ...

Update History
Hello, Is there an update history in MS Access database, so that i can retrieve information about inserted, deleted or updated records? In other words any way to read the transaction log. Thank you. No. JET (the database engine in Access) does not give you transaction logs. If all entries are made through a form, you can simulate it with the events of the form. Details in: Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply...

Info For All
In case this issue applies to you .... I posted a query last week about why my Junk email was no longer working and all emails were going to my Inbox. I appear to have solved the problem by uninstalling the junk mail filter update of 11th Oct 2006. Outlook is now filtering junk mail correctly again. ...

does vista installed on virtual machine 2007 get wsus updates ?
It is searching for updates but it is not finding anything and saying that Windows is up to date. I have set the updates to install from the wsus server and assigned the updates to the same Vista virtual machine .. Thank you -- aconti ------------------------------------------------------------------------ aconti's Profile: http://forums.techarena.in/members/73272.htm View this thread: http://forums.techarena.in/active-directory/1290161.htm http://forums.techarena.in Hello aconti, If the machine is getting the correct GPO for the WSUS settings, check with rsop...

Windows Security Update
http://www.informationweek.com/story/showArticle.jhtml?articleID=18901284 This does not apply to Publisher directly but is of interest to anyone running Windows 98SE or newer. -- JoAnn Paules - MVP Microsoft Publisher Is this now a bloody news service!!! -- "If you don't know where you are going, any road will take you there!" In news:uU8F1UaIEHA.2576@TK2MSFTNGP09.phx.gbl, ���MS�Publisher��� <donotknock@nobodyhome.com> posted: > Is this now a bloody news service!!! i thought msNEWs.microsoft.com gave it away? -- Brian Kvalheim Microsoft Publisher MVP http:...

Payroll Year End Update problem
We Installed the 2009 U.S. Payroll Year End Update for GP 10.0 on a workstation first (not on the server yet). Installed 2010 Payroll Tax Update and latest version is installed. We ran Payroll for 2010: it posted to payroll fine but not through GL (posting set up is fine). We installed the Year End Update in the server after all that but problem still remains and GP actually crashes when posting a transaction. We ran check links and reconcile - which gives one error message to check account format which looks fine. Also, TB from financial or any report that uses distributions pri...

[ANN] Office 11.3.8 update
Hi All, A new security update just came out. I suspect it requires 11.3.7 to install. Anyway, time to run the Microsoft Updater or to grab it from <http://www.microsoft.com/mac/downloads.aspx> Corentin -- --- Mac:MS MVP http://www.cortig.net/wordpress/ --- http://www.mvps.org - http://mvp.support.microsoft.com MVPs are not MS employees - Les MVP ne travaillent pas pour MS Remove "NoSpam" to e-mail me - Retirez "NoSpam" pour m'�crire I just let the Microsoft AutoUpdate do its thing. The only Office...

Batch processing
I'm trying to program batch order processing for our company, but can't find info on how to do that with RMS. What would be helpful is diagram of the tables and fields that are updated when a transaction is closed. Or some documentation! Anyone know? ...

W2003, batch converter wizard: call from cmd (batch)
Hi, Working with Word 2003. I have installed the batch converter wizard and all is well. I would like to create a batch file that opens Word, calls the wizard and runs with a defined configuration, eg convert Word doc to xml, predefined source and target folders, select all. I can run a batch file to open the wizard, thus.. echo off "C:\Program Files\Microsoft Office\Templates\1033\Batch Conversion Wizard.Wiz" .... but I am still faced with manually selecting conversion type, sourc e and target folders, select all. I tried recording a macro calling the wizard a...

Auto update fields on selection
Hi, I am looking to have a form, where when I select one field in a combo box, it automatically updates all the other fields to match that list so for example.. I have a table with 3 fields telephone first name last name So I want a drop down box on the form, and when I choose the telephone number i want, it automatically shows the first and last name with it... Additionally I would want to be able to choose a last name, and it show the telephone and first name associated Access 2003 Thanks in advance ...

Sum range updating
On worksheet 2 I am trying to use the sum function to calculate a range of cells in a column on worksheet 1. My problem is that when I insert a row within that sum range it expandes the formula. For example the formula before the inserted row would be =SUM('worksheet 1'!H8:H88), but after I insert the row it changes the formula to =SUM('worksheet 1'!H8:H89). I don't want it to do that. I want it to keep the exact range that I define in the first place regardless wether I insert or delete rows on worksheet 1. Is there a way to do this? TIA! Chad Wethington =SUM...

updating linked cells
I've recently updated to office 2003, and I'm having trouble with Excel. the problem is with worksheets that do not automatically update the results of new cell entries. For example, If I add a new value in a cell that is in a range that calculates the average of that range, the new value is not automatically included in the calculation, although the formula indicates that it should be. the only fix that I've been able to come up with is to go to every cell and update it manually by double-clicking it. This is extremely irritating! Any suggestions? This was not a problem in Of...

Missing Check Batch (Batch not hitting GL)
My client is on GP 8 and this happened 2 times already. Basically they post a batch of computer checks, they are set to post through to GL. They got an error about glpostcleanup and the batch became stuck in Batch Recovery. We 'freed' up the batch but afterwards it is nowhere to be found. The batch is not in Post Checks, not in GL Batches, not in SY00500. In both instances we had to post the checks in Bank Rec to GL. The checks did not hit BR either. This is starting to worry me. GP said to run the special upgrade procedure in GP Utilities and I ran this before my client ...

Updateable Query
Why can't access just read my mind. It would be easier. I have 2 table tblStudents-Stores Name, ID#s, Ethnicity, Etc tblTests - Stores which particular tests the students should take Math, Reading, Science Etc. Currently tblTests is empty. I would like to set up a query that links the tblStudents with tblTests via the LocID number with all records from tblStudent showing. LocIDis the primary key for both tables. When I tried to link them it makes the query uneditable. I looked on the site and tried to include all the fields from tblTests and only last and first name ...

Cannot use update query to update records in another query
I need to update a field in one table with data from another table. I have joined the two tables in a query, but when I try to update the field using an update query or using VBA, I get an error saying that the query is not updatable. I have checked several other queries that join multiple tables and none of them will allow me to update any of the fields. Any thoughts? Thanks in advance for your assistance, Dan WarEagle90 wrote: > I need to update a field in one table with data from another table. > I have joined the two tables in a query, but when I try to update the > fiel...

Update Field Data Type
I have a checkbox field for which I want to change the data type to a Number (Integer or Long Integer). I searched the discussion board and found the following suggested code: Dim strSql As String strSql = "ALTER TABLE MyTable ALTER MyField LONG;" DBEngine(0)(0).Execute strSql, dbFailOnError I ran the code and while it does successfully change the data type to LONG INTEGER, the Display Control property remains set at 106 (Check Box) and therefore the appearance of the field in datasheet view does not change. Could someone let me know how I would go a...

Previously Closed Bills in Quickbooks Reappear during RMS Batch Po
I am seeing previously posted and closed bills in Quickbooks Premier 2008 reappear during later posts of new batches from RMS. Anybody seen this one? Thanks, Richard ...

Creating a batch file for Inventory Adjustments
I'd like to import the results of our physical inventory into an Inventory Adjustment batch. Can someone explain how to do this? The batch will consist of approx 1,000 records. Are you using a stock count schedule? if so, there is no way to do this directly. You could directly import the data into the SQL table - Ask your partner for assistance in this if you are not a DBA. HS "MrMark" <MrMark@discussions.microsoft.com> wrote in message news:B88E26CD-2503-4844-9AA9-94003208556A@microsoft.com... > I'd like to import the results of our physical inventory into...

I lost Windows Update on Windows Vista Home Premium
I click Start, then Windows Update - nothing, it just locks up as a blank screen. It's not listed as a Program on Control Panel I've tried to download it, but it won't download. I've searched other sites, nothing helps. I restored the PC to a about month previous (5/12/2010) - nothing. I have Comcast and they replaced McAfee with the Norton Suite and that is what I'm using, not Windows protection software. Could that affect the use of Windows Update? What would you recommend? Al wrote: > I click Start, then Windows Update - nothing, it just locks up as a ...

automatically update data validation selections
I've restricted user enter for certain cells to a named range. Fo example, the named range includes: Apples, Oranges, Bananas. Let's say a user selects "Apples". Then, the list is updated so tha "Apples" becomes "Green Apples". I want what the user selecte ("Apples") to now automatically reflect "Green Apples". I canno figure out how to accomplish this without using a combo box - but really don't want to use a combo box - just keep the user entry area a cells. Any suggestions with no code or a minimum of code are much appr...

No updating of Excel chart
Hi. Is there any way to prevent excel from updating charts? I'm having some problems with one chart when I try to unhide or delete some rows that ARE NOT inside the series data, but the chart allways keep wrongly changing the data rows. When i try to unhide the rows, the series expands to the number os rows that was unhided. When i try to delete the rows, the series all became #REF's. Repeating: the rows that i need to delete are CONTIGUOUS to the data series and ARE NOT inside the series. I've checked this thoroughly. If someone have any ideas, I would be immense...

Re-number credit card batch for settling
Had a problem settling a credit card batch due to duplicate batch number. Credit card company (paymentech) keeps saying we have to re-number our batch. Batch is already gone as "settled" on my side. No error came back as "duplicate". Didn't know till we called processor. \ Also, would like duplicate receipt for debit card without a signature line and without pressing reprint. How to? Thanks Roxanne, Make a backup first. Do it again and store it somewhere else just to be safe. Contact Paymentech and find out what batch number they are looking for. *** CAUTION ...

Exchange updates and patches
I'd like to be notified about latest security patches and updates whenever they are released for my Exchange 2000 server. Is there a place I can sign up for this? Can you please provide me with a link? Thank you. "mtler" <mtler@discussions.microsoft.com> wrote in message news:A114091D-45D2-4146-BA37-EF054B0712DF@microsoft.com... > I'd like to be notified about latest security patches and updates whenever > they are released for my Exchange 2000 server. Is there a place I can sign > up > for this? Can you please provide me with a link? > Thank you. h...

Making Non-Updateable Query Updatable?
I think I've painted myself into a corner on this one. Got a screen that's based on work tables. Screen't query, however, is joining to production tables to retrieve certain fields - which I'm guessing is making the query non-updatable. I *could* just denodrmalize the derived fields to the work table - which would permit a Form.RecordSource query that points only to the work table and would be slam-dunk updatable. Problem is that would denormalize several fields and add greatly to the management of the screen's contents if/when the user changes various fields that the d...

What is the difference between "last updated" and "modified"?
Hi I'm using Sharepoint Server 2007 and I have document libraries that contain columns labelled "last updated" and "modified". They both contain dates, but what is the difference between the two? Thanks The "Last updated" field must be a custom column added to the library. Sharepoint only tracks Created and Modified date fields. It must have been added by the site owner (or someone else with the right permissions) or the library itself may be based on a custom definition. Greg Osimowicz -- http://SharepointResourceCenter.com ....you...