Solved

Manipulating Data held in an Excel Spreadsheet from within Access

Posted on 2006-07-05
12
384 Views
Last Modified: 2008-03-06

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
0
Comment
Question by:oogooglies
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17043605
u can try this approach


Public Sub WorkExcelWB()

    Dim sSql As String
    Dim objXL As Object
    Dim objWB As Object
    Dim rs As DAO.Recordset
    Dim sFile As String
    Dim i As Integer
   
   
    'To open existing workbook
    'Set objWB = objXL.Workbooks.Open("C:\EE\MyFile.XLS")
   
    'Modify a cell
    objWB.Worksheets(1).Cells(2, 10) = "Hello Sir"
   
    'Set name of file to save to
    sFile = "C:\EE\CopyXX.XLS"

    'To save as a new file, delete existing file if it exists
    If Dir$(sFile) <> "" Then Kill sFile
    objWB.SaveAs sFile
   
    'CLOSE DOWN

    objWB.Close
    objXL.Quit
   
    Set objWB = Nothing
    Set objXL = Nothing

End Sub

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17043619
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17043630
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")
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:oogooglies
ID: 17043670
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17043812
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

0
 
LVL 44

Expert Comment

by:GRayL
ID: 17044483
Don't forget you cannot modify data in a spreadsheet from Access when the cell is a formula.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17044930
<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>
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17045597
Damn, my secret has been leaked out :)
0
 

Author Comment

by:oogooglies
ID: 17048891
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'
0
 

Author Comment

by:oogooglies
ID: 17048899
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
0
 

Author Comment

by:oogooglies
ID: 17048950
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17052419
try this to clear

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

0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question