Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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