Using Access to open and close Excel

[murfeezlaw]

Hi all, i'm trying run a piece of code in Access to open up an Excel spreadsheet and make some updates. I've been working on this for an eternity and running out of hair. See below for a snippet of my code. I'm having a problem with the instance of Excel not closing after all the code runs(I see the Excel.exe in Task Manager). i know the problem w my code is when i actually do the update(Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False). i think it opens another instance of Excel bc it's not tied to an instantiated object and this instance does not close. I think i need to tie it to an instantiated object...something like WS.Cells.Replace but i'm not sure how to do that. I keep getting errors. i'm a little new to Excel VBA. Can someone help me w that piece of code?




strFileName = txtFileToBeImported
strTableName = "KISClaimsLossRun_Admin"




'Open Loss Run in Excel, delete column and close, saving changes
Dim app As New Excel.Application
app.Visible = False 'Visible is False by default, so this isn't necessary
Dim book As Excel.Workbook
Set book = app.Workbooks.Open(txtFileToBeImported, , False)

'Excel.Application.DisplayAlerts = False
app.DisplayAlerts = False



Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False



book.Save


Set book = Nothing
app.Quit
Set app = Nothing


Any help would be greatly appreciated
Ted

[ssanfu]

When I was using Excel to transfer data, I also had a hard time getting Excel to close (if it wasn't open) before I did the transfer. I found several sites that helped me. It's been several years - I don't recall the code I used......
Look at these:

Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

Sample Excel Automation
http://access.mvps.org/access/modules/mdl0006.htm

Find out if an application is currently running
http://access.mvps.org/access/api/api0007.htm

Automation object doesn't close automatically
http://access.mvps.org/access/general/gen0017.htm

[June7]

And another discussion about clearing Excel http://forums.aspfree.com/microsoft-...el-413629.html

Need a worksheet object. Try this:

Dim app As New Excel.Application
Dim book As Excel.Workbook
Dim wsht As Excel.Worksheet
Set book = app.Workbooks.Open(txtFileToBeImported, False)
Set wsht = book.Worksheets("Sheet1")
wsht.Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
book.Save
app.Quit
Set app = Nothing
Set book = Nothing
Set wsht = Nothing

If you don't know the worksheet name and just want to hit all in the workbook:
For Each wsht In Worksheets
wsht.Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next

[murfeezlaw]

And another discussion about clearing Excel http://forums.aspfree.com/microsoft-...el-413629.html

Need a worksheet object. Try this:

Dim app As New Excel.Application
Dim book As Excel.Workbook
Dim wsht As Excel.Worksheet
Set book = app.Workbooks.Open(txtFileToBeImported, False)
Set wsht = book.Worksheets("Sheet1")
wsht.Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
book.Save
app.Quit
Set app = Nothing
Set book = Nothing
Set wsht = Nothing

If you don't know the worksheet name and just want to hit all in the workbook:
For Each wsht In Worksheets
wsht.Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next

thank you Thank You THANK YOU!! THAT did the trick! i only had to disable the alerts so Excel didn't ask me if i wanted to save the changes. Other than that it's working perfectly. i owe my sanity to you!! I really appreciate your help!

[murfeezlaw]

When I was using Excel to transfer data, I also had a hard time getting Excel to close (if it wasn't open) before I did the transfer. I found several sites that helped me. It's been several years - I don't recall the code I used......
Look at these:

Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

Sample Excel Automation
http://access.mvps.org/access/modules/mdl0006.htm

Find out if an application is currently running
http://access.mvps.org/access/api/api0007.htm

Automation object doesn't close automatically
http://access.mvps.org/access/general/gen0017.htm

Thank you Steve. I've bookmarked these as references. I appreciate your help as well.