Hi, I use the following code from access to update certain cells in an existing excel sheet.



Dim xl As Excel.Application
Dim xlsht As Excel.workSheet
Dim xlWrkBk As Excel.workBook
Dim ExcelFile As String

ExcelFile = "C:\" & PrtlFile

Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject(ExcelFile)
Set xlsht = xlWrkBk.Worksheets("Sheet1")

xlsht.Cells(12, 2) = PrtlFile
xlsht.Cells(13, 2) = PrtlID
xlsht.Cells(14, 2) = IDNo
xlsht.Cells(15, 2) = IDName
xlsht.Cells(15, 3) = IDAddress

xlWrkBk.Close



My problem is that each time this procedure is run, the warning to run macros appears when it is about to write to excel and then asks for confirmation to save changes to the sheet when closing.

Is it possible to do this in a seamless way, such that the user in access will not notice that access is processing the excel sheet and he does not have to keep confirming message boxes shown by excel.

The excel macro warning is because the excel sheet you are opening contains macros...
No way to change that unles you sign your macro's or set the security to low (you definatly dont want the latter).

To prevent the save message you can automaticaly save the sheet before closing it.
xlwrkbk.save will save it for you

Thanks Mailman,

Sorry I'm still quite new at VBA. How do you sign your macros?

Some googling will find you that answer... but here is a good place to start:
OFF2000: Using SelfCert to Create a Digital Certificate for VBA Projects (http://support.microsoft.com/kb/q217221/)

Thanks.

I added the line xlwrkbk.save, but why does the workbook hide itself?

After running the subroutine. I tried opening the excel file and had to unhide the sheet to see it. Which part of my code is incorrect? What should I do so that it won't hide itself?

Thanks.

You are not closing and cleaning up anything...
xl.close
Set xl = Nothing
Set xlWrkBk = Nothing
Set xlsht = Nothing

That should help

Sorry...

xl.close gives me an error... i also tried xl.quit then xlWrkBk.close, but both still keep hiding the worksheet

I guess it should be XL.Quit, yes...

Did you add the = nothing stuff?

yes. arrrggghhh, I'm really getting frustrated.

did you try closing the sheet?

I've tried the ff:

xlWrkBk.Save
xlWrkBk.Close
xl.Quit

Set xl = Nothing
Set xlWrkBk = Nothing
Set xlsht = Nothing

though, it still gets the same result... as for xlsht, there is no close method.

I'm really sorry Mailman for wasting your time, it's just I can't really figure this thing out...

I have had trouble with this in the past as well. It was caused by inproperly closing and using the excel objects.... but you seem to use/declare/close them now properly, So I am at a loss for now... :(

Sorry :(

Then again i allways believed in making a new xl object..
Maybe if you add something like
xlsht.visible = true

Not sure that excists tho... If it doesnt, maybe:
xlwrkbk.visible = true

*grasping straws here*

It's kinda weird though, I noticed that if I close it without saving like:

xlWrkBk.Close False

then the workbook stays ok. but once it is saved like using xlWrkBk.Save, then the workbook becomes hidden the next time it is opened.