asked on

Manipulating Data held in an Excel Spreadsheet from within Access

Hi

Is there anyway data within an excel spreadsheet can be amended and then re-saved with a different filename and in a different location, all from within Access.

Stages

Load Data from spreadsheet into table in Access - DONE
Save the spreadsheet with a different name in a different location - NOT DONE
Clear the data from the original spreadsheet that was loaded into Access - NOT DONE
Save the original spreadsheet (same name and location) - NOT DONE

Any help would be greatly appreciated

Thanks

Anthony

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockiroads

load data from Access to Spreadsheet

If u had a query, u can open a recordset
e.g

'Open a recordset for your query
sSql = "SELECT * FROM Table3"
Set rs = CurrentDb.OpenRecordset("qryExcel")

then paste the results into the spreadsheet e.g.

'Paste the values from your query starting from A2
objWB.Sheets(1).Range("A2").CopyFromRecordset rs

rockiroads

If u didnt want to open an existing spreadsheet but create one from scratch, u do this

'To create new workbook
Set objWB = objXL.Workbooks.Add

instead of

'To open existing workbook
Set objWB = objXL.Workbooks.Open("C:\EE\MyFile.XLS")

Note, the first post had that line commented, sorry

Also one thing I forgot to add

Before u open/create workbook, u have to create object

do this as the first line after declarations

'Create a new excel document
Set objXL = CreateObject("Excel.Application")

oogooglies

ASKER

ROCKIROADS

Is there anything you don't know???? I have taken a print out of the above and will try it this evening. I do not have the net where i am staying so, i will let you know how i get on tomorrow Morning. Many Thanks again

Anthony

rockiroads

Urm I havent discovered how to stop my wife nagging me.
If u know a way pls let me know!!!

Ok, my code in bits and bobs, but hopefully the different posts mean something

Good luck

GRayL

Don't forget you cannot modify data in a spreadsheet from Access when the cell is a formula.

jefftwilley

<off topic>
Don't swell poor Rocki's head anymore!! It barely fits through the door now!! hehe
He only knows this stuff cause he's a long time geek.
J:o)
<end off topic>

rockiroads

Damn, my secret has been leaked out :)

oogooglies

ASKER

Hiya,

I tried the code in your first post, but it kept erroring for "Object variable or With block variable not set" run time '91'

oogooglies

ASKER

ignore that last comment, i have only just noticed something you said in one of your later posts! only printed out after the first two of your posts! I will try again

oogooglies

ASKER

Hiya

I proper comment here! Ok i have done everything i need.... except what i need to do with the original spreadsheet is clear all rows apart from the header... how would i go about doing that? there is only one worksheet in it.

Thanks

rockiroads

try this to clear

objWB.Worksheets(1).Rows("2:65336").Select
objWB.Worksheets(1).Selection.ClearContents
objWB.Worksheets(1).Range("A1").Select