Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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
0
oogooglies
Asked:
oogooglies
1 Solution
 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
oogoogliesAuthor Commented:
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
 
rockiroadsCommented:
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
 
GRayLCommented:
Don't forget you cannot modify data in a spreadsheet from Access when the cell is a formula.
0
 
jefftwilleyCommented:
<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
 
rockiroadsCommented:
Damn, my secret has been leaked out :)
0
 
oogoogliesAuthor Commented:
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
 
oogoogliesAuthor Commented:
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
 
oogoogliesAuthor Commented:
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
 
rockiroadsCommented:
try this to clear

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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now