Solved

Manipulating Data held in an Excel Spreadsheet from within Access

Posted on 2006-07-05
12
380 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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