Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Manipulating Data held in an Excel Spreadsheet from within Access

Posted on 2006-07-05
12
Medium Priority
?
385 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 2000 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 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