Edit Access table in Excel and save back to access (1 Viewer)

frispee · Jul 11, 2012

Hi,

This is what I want to do:

1. I have a split form with a button. When I click the button, the table should open in a newly created excel file.

2. The user will make some changes to the data.

3. At this time, a pop-up box will appear in the access window saying something like, "Do you want to save the changes you have made in excel?"

4. If the user says OK, the data in the existing table will be deleted and the table that is open in excel will be copied over to access.

I have pieced together how to do steps 1 and 3. In step 4, I know how to delete the data and how to bring the excel data into access. But my code depends on me knowing the name and location of that excel file. This is where I am stuck. Can you help me with this? Like do you know where the temporary file is stored? Or any other ideas? Which is the best approach?

Thanks in advance

Beetle · Jul 11, 2012

I can't help but wonder why you don't just have the user edit the data in Access, but in regards to your question, why don't you just specify the file path when you create the spreadsheet in the first place?

frispee · Jul 12, 2012

Hi,

I have got the code to work as I want to for now. I am posting this for anybody else who wants to do the same thing (Provided you are not offended by the way it works

). This is the code I have come up with:

Code:

Private Sub ExcelEdit_btn_Click()

'**********Transfer the table to a newly created Excel file********

Dim TempName As String
TempName = CurrentProject.Path & "\Temp" & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Projects_tbl", TempName, True

'*********Open that file*********

Dim oExcel As Excel.Application
Dim oWB As Workbook
Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks.Open("D:\Database Project\Project Files\Temp.xls")
With oExcel
.Visible = True
End With

'The user can make edits in the opened excel file.

'*********Replace existing table with "edited" table from Excel*******

sResponse = MsgBox("Do you want to save the changes you made in the Excel file?", vbYesNo, "Save Changes?")

If sResponse = vbYes Then

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Projects_tbl"
DoCmd.SetWarnings True

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Projects_tbl", TempName, True
ActiveWorkbook.Close False 'Cleanup
oExcel.Quit
Kill TempName

Else


ActiveWorkbook.Close False
oExcel.Quit
Kill TempName
Exit Sub
End If

End Sub

I know for sure that there must be a prettier way to do this. And I would love to find what it is. But this gets the job done for now.

Comments, if any, will be appreciated.

Thanks

- Ashwin

frispee · Jul 12, 2012

@Beetle: Thanks for pointing me in the right direction

Beetle · Jul 12, 2012

Happy to help

A few comments on your code;

1) You should really consider adding proper error handling.

2) When running action queries, if you use the Execute method of CurrentDb (instead of the RunSQL method of the DoCmd object) you won't need to worry about turning SetWarnings off and on. Example;

CurrentDb.Execute "DELETE * FROM Projects_tbl", dbFailOnError

You should avoid manipulating SetWarnings unless you have to. If you turn warnings off and then your code falls over, it may not get turned back on. If you are going to turn warnings off, then you definitely should have error handling in your code that will set the warnings back to True even if there is an error.

3) Your Exit Sub (at the end of the If...Then statement) doesn't really serve any purpose because it comes just before you're going to bail out of the procedure anyway.

Edit Access table in Excel and save back to access (1 Viewer)

frispee

Registered User.

Beetle

Duly Registered Boozer

frispee

Registered User.

frispee

Registered User.

Beetle

Duly Registered Boozer

Similar threads

Users who are viewing this thread