Excel open-close from Access

[The_Doc_Man]

Based on post 19, you get no error but you also see no updates?

OK, that means that there are formulas in the Excel file and you have to open & close same for the formulas to update. But that leads to another question.

How does the new information get into the Excel file? I'm asking because, you see, if YOU don't put that info into the file, then someone else had to open & save/close the file to enter that new data. The file was opened for the update, so any formulas in that file should have triggered. Therefore, this does not make any sense. There is more to this than meets the eye.

OR are you saying that you are using the export from the last cycle as the only input data and that the formulas do all the work in Excel? Because if that is what you are saying, then it makes even LESS sense.

Somehow I am having trouble wrapping my mind around this entire procedure and therefore will have one helluva time understanding it enough to give better advice. The key to any problem is understanding it, and at the moment I can't claim that.

 

[Deleted member 147267]

There is more to this than meets the eye.

You're not the only one who isn't grasping the flow of things. I was hoping that reading the 'irrelevant' code would divulge the data export/update, whether or not the file was saved, was ever opened/saved from Access (posted code appears odd in that respect) and other things. I hope you can take it from here as I'm going to unsubscribe now as there's too many posts and not enough cooperation for me.

 

[The_Doc_Man]

True that, Micron. I think at the moment the ball is not in my court.

 

[dmgg]

I found some open/close excel code that works in Access 2016 (also with 2003 and 2007) on StackOverflow. Not that different, but works. Here it is.

Dim xlapp As Object 'had to add this-my thanks to you.
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = False
xlapp.Workbooks.Open exfile, True, False 'true/false not needed
xlapp.Quit
Set xlapp = Nothing

I hope to research and understand this better. One other confusing thing is that Access 2003 & 2007 allow you to export data to a specific range on any sheet in the excel file. Access 2016 does not. With 2016, you must export without a range name and allow Access to create a new sheet (with export table name). Why is old better than new?

 

[The_Doc_Man]

Why is old better than new?

If we could answer questions like that, we would be on the board of directors at Microsoft and not sitting up at odd hours answering questions on a forum. Not trying to be snarky about it, but this is a time for a religious pronouncement: We are not meant to know the mind of Microsoft developers.

And yes, there are SOME developers who think they ARE gods, so my pronouncement would merely stoke their egos.

 

[dmgg]

I thought that I had posted this already, but don't see it. I found some code that opens/closes excel in Access 2016 (also works with 2003 and 2007). Not sure why other code didn't work with Access 2016. Sometimes I hate Microsoft.

Dim xlapp As Object 'had to add this-thanks you
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = False
xlapp.Workbooks.Open exfile, True, False 'exfile is exce filel defined with Dim statement
xlapp.Quit
Set xlapp = Nothing

 

[Gasman]

You did post it, it's post 24

I thought that I had posted this already, but don't see it. I found some code that opens/closes excel in Access 2016 (also works with 2003 and 2007). Not sure why other code didn't work with Access 2016. Sometimes I hate Microsoft.

Dim xlapp As Object 'had to add this-thanks you
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = False
xlapp.Workbooks.Open exfile, True, False 'exfile is exce filel defined with Dim statement
xlapp.Quit
Set xlapp = Nothing