Excel open-close from Access (1 Viewer)

dmgg · Jan 31, 2019

With Access 2003(and 2007), I send data to an excel file. The excel file does some calcs and sends results back to Access. In order to get excel to export the results, the excel file must be opened and closed after receiving data from Access. My code works fine in A 2003 and 2007, but the excel file won't open-close with Access 2016. Te user has to manually open and close the excel file.

Here is the the code for open-close

Dim xl As Excel.Application, WB As Excel.Workbook
Set xl = CreateObject("Excel.Application")
Set WB = xl.Workbooks.Open(CurrentProject.Path & "" & .ExcelFile)

WB.close True
xl.Quit

Note ".excelFile" is defined in a WITH statement that refers to the excel file name posted on a form.

Any help much appreciated.

Micron · Jan 31, 2019

Not sure what the issue is, but it might not matter if you can simplify this. Can you not just link to the sheet(s) as Access tables? Any changes to the sheet would be reflected on the Access side. It sounds like you've already figured out the data export from Access to Excel, so linking the sheets could be a straight forward solution.
As for the reason, my guess is that different versions of references (specifically Office) are to blame. In these cases, usually declaring As Object rather than As Exel.Application (for example) takes care of the issue. That's called Late Binding.

dmgg · Jan 31, 2019

I tried the link, it works, but is ridiculously slow, about 20 seconds or more. I am finding that leaving access and excel 2003 for later versions is loaded with problems. I will try to find out why code doesn't work in 2016 version. Do you know where I could find info. I have tried and tried.

Micron · Jan 31, 2019

Did you reply before seeing the comments about late binding? I might have been editing my post when you replied.

dmgg · Jan 31, 2019

My goof. I will try As Object as soon as I get to my friend's Access 2016. I will also investigate what "binding" means. I much appreciate the time you are giving me on this.

Micron · Jan 31, 2019

Early binding means you explicitly type (not keyboard typing) variables, be they objects or otherwise, then Access refers to the specific library (reference) according to the type. If you declare as Object you are late binding. Access figures out which library reference to use at run time, not before (at compile time). On pc's with different versions of a reference because of different Office versions, Access chooses the applicable file that provides that reference.

sxschech · Jan 31, 2019

Based on what I read, if

The excel file does some calcs and sends results back to Access.

Is there a reason why you could not perform the calculations within Access either with queries and/or vba and eliminate the export/import step?

The_Doc_Man · Jan 31, 2019

Be careful about linking Access to the spreadsheets to use as though they were tables. You cannot update a spreadsheet this way. You would have to export.

I'm with sxschech about trying to do some sort of query within Access and skip the step of the pull/push - if that is possible in your business model.

dmgg · Feb 1, 2019

sxschech said:
Based on what I read, if
Is there a reason why you could not perform the calculations within Access either with queries and/or vba and eliminate the export/import step?

Long story, but what the excel file provides is only a small part of the Access program. The excel file is complicated and is developed, updated, modified and maintained by someone who only works with excel. It is easier for us just to ship data and retrieve results. (at least with Acesss 2007)

dmgg · Feb 1, 2019

The_Doc_Man said:
Be careful about linking Access to the spreadsheets to use as though they were tables. You cannot update a spreadsheet this way. You would have to export.

I'm with sxschech about trying to do some sort of query within Access and skip the step of the pull/push - if that is possible in your business model.

Thanks for reply. See my reply to sxcheck

The_Doc_Man · Feb 1, 2019

OK, my next question has to do with how much data you are moving around. Is this something where you load a whole bunch of data (many rows, many columns) or just a piece here and there to load or store?

If it is a bunch, your best bet is importing and exporting. If we are talking about a handful of isolated pieces, the selectivity might be better served with an Excel Application Object and directly fetching/storing data in the sheet in question.

dmgg · Feb 1, 2019

I export a single row of 51 items and import a single row with 16 columns.

dmgg · Feb 2, 2019

Micron said:
Early binding means you explicitly type (not keyboard typing) variables, be they objects or otherwise, then Access refers to the specific library (reference) according to the type. If you declare as Object you are late binding. Access figures out which library reference to use at run time, not before (at compile time). On pc's with different versions of a reference because of different Office versions, Access chooses the applicable file that provides that reference.

Reply:
I finally got to try "Object" in Access 2016. Didn't work, excel receives and provides new results, but won't export because excel doesn't open close. this is how I modified my code:

Dim xl As Object, WB As Object '

Set xl = CreateObject("Excel.Application")
Set WB = xl.Workbooks.Open(CurrentProject.Path & "" & .ExcelFile)

WB.close True
xl.Quit

Correct use of "Object"? Works fine with A2007

Gasman · Feb 2, 2019

You really need to paste code within code snippets.

The site has removed what I believe would be

Code:

"\"

from the file path

Micron · Feb 2, 2019

what you show looks right with respect to the declaration and setting of objects, but so far you've shown so little of your code that pinpointing a problem is not really possible. F'rinstance, I'm left wondering if if really looks like what you posted in it's entirety. I mean, I see no opening of any workbook or manipulation of one, and no saving. What you have shown seems to serve no purpose. The binding suggestion was made on the assumption that there's a lot more code, and was for getting around issues that exist when different users have different versions of Office. Since that didn't seem to help, seeing the full code and understanding what it is supposed to do should come next. Or you could upload a zipped copy of your db and workbook as I have 2016, which you indicate is part of the problem. If you do post the code, please do use code tags as requested.

dmgg · Feb 2, 2019

The code preceding this is export to excel and the code following is import from excel It all works except for the opening and closing procedure that I show.. The exported data gets to the excel file perfectly. The import from excel doesn't work because excel has to be opened and closed first.. If I manually open and close the excel file, the import code works fine. The code I show is a simple open close command that works fine in acesss 2007 and 2003, but not in 2016. If you still would like to see the rest of the code, I will send it.

The_Doc_Man · Feb 3, 2019

Next question: Regarding errors, you could have a handler that "eats" errors OR you could have disabled warnings. When you attempt to do this open and close of the Excel file, do you get any errors or have you blocked the handlers?

I will further add that this is probably an Access error rather than an Excel error because if the process got as far as trying to open Excel, you would have no error handler in place to prevent the natural progression of error event processing. Which is why I am asking whether you have warnings disabled. P.S. - if you DO, then enable them, at least for testing.

Dreamweaver · Feb 3, 2019

Just tested this it's worked for me in every access version since 2001

Code:

Private Sub CmdRunExcel_Click()
Dim objActiveWkb As Object
On Error GoTo HandleErr

    Dim oApp As Object
        If fIsAppRunning("Excel") Then
        Set oApp = GetObject(, "Excel.Application")
    Else
        Set oApp = CreateObject("Excel.Application")
    End If
    oApp.Application.workbooks.Add
    oApp.Visible = True
    oApp.Caption = "Database Dreams - Developer - Excel"
    
HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case 2501 'Cancel = True
            Exit Sub
        Case Else
            Call GlobalErrs(Err.Number, Err.Description, Err.Source, "ZfrmCustomersDocumentSubform", "CmdRunExcel_Click")
            Resume HandleExit
        Resume
    End Select
End Sub

dmgg · Feb 3, 2019

Thanks.
I don't get an error message In Access 2016, it just imports data derived from the previous export unless manually open/close the excel file.

Looking at your code, I don't see anything that defines the excel file that I want to open and close. or a command to open close. I'm sure my inexperience is showing.

Thanks much.

Micron · Feb 3, 2019

This is the way I see it. You're here because you're stuck with a problem and I/we have reasons for wanting to see what code looks like so that several things can be considered at once (TDM has raised one possibility). This wouldn't be the first time someone professed that their code wasn't the issue yet it turned out to be. In your case, maybe it's perfect, but after 16 posts and not much progress, I'm not going to beg. Sorry for being so blunt, and good luck. I hope you solve it.

Excel open-close from Access (1 Viewer)

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Immoderate Moderator

Registered User.

Registered User.

Immoderate Moderator

Registered User.

Registered User.

Enthusiastic Amateur

AWF VIP

Registered User.

Immoderate Moderator

Well-known member

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread