98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel to Access Data Exchange
Excel to Access Data Exchange
Resolved · Medium Priority · Version 2003
Fri 14 Jan 2011, 15:27Delegate Craig said...
Craig has attended:
Excel VBA Advanced course
Access VBA course
Excel to Access Data Exchange
What is the easiest was to move data from an excel spreadsheet to Access.
Currently I am moving cell by cell to field by field, by indivitual rows.
Is there away of a row of data in excel to a row in access.
If you need to see the original code I am using then please email me at craig.edwards77@hotmail.co.uk
Kind regards,
Craig
Wed 19 Jan 2011, 10:44Trainer Anthony said...
RE: Excel to Access Data Exchange
Hi Craig, thanks for your query. There's a much easier way of doing this than via code. Simply use the Get External Data function in Access to link to the spreadsheet. The spreadsheet will then be dynamically linked and exactly mirrored in Access.
Hope this helps,
Anthony
Wed 19 Jan 2011, 11:00Delegate Craig said...
RE: Excel to Access Data Exchange
Hi,
The spreadsheet is used by multiple users, when trying to do the above it couldn't cope with all the changes and the spreadsheet is read only because again Excel has the issue with the shared workbook option.
I guess it's the age old thing, if it aint broken then don't try and fix it, justy wanted to write all the information in one go rather than cell by cell to speed up the process.
Kind regards,
Craig
Wed 19 Jan 2011, 11:08Trainer Anthony said...
RE: Excel to Access Data Exchange
Hi Craig, you still have to write cell by cell but have you considered copying the data from the worksheet into an array:
Dim myarray as variant
myarray = Range("A1").currentregion
...then looping through the array and sending each value to the Access table? That will certainly be quicker!
Anthony
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Sorting List SubtotalsIf you find that you would prefer to show the items in a subtotalled list in a different order, eg ascending rather than descending, you can sort your list. To sort a subtotalled list, hide the detail rows and then sort the subtotal rows. When you sort a subtotalled list, the hidden detail rows are automatically moved with the subtotal rows. |
