Automation Access to Excel

[Paradigm]

Hi Guys,

I am a real newbie, so please bare with my ignorance :). Also this is a long set of questions and answers for any of them would be greatly appreciated.

1)

I am working on this project where i am looking to pick up filtered data from a table (in Access) and insert this into an already existing excel file. The problem is that the number of rows depending on the filter will be different everytime.

I was wondering how exactly i could insert them in the middle of two rows already in the excel file. Also if this can be done line by line through a loop.

The reason i would want to do it line by line is since the values might be in % or an absolute value, so i would need to treat them differently.

I really hope i have explained this properly. please do let me know if i can explain anything in more detail.

2)

This is also automation of excel through Access VBA.

What is the syntax for checking if an excel file is already open, and how can i edit that particular file. I have been using code to set the autofilter on an excel file but it only works when 1 excel file is open, if more than one is open then it gives an error.

Thanks again for your time.
Cheers!
Paradigm

[FishVal]

Just for starters: Application Automation

[NeoPa]

It's not possible to open a spreadsheet if it's already open.

I got around that by renaming the file first (to itself). An error code of 75 indicates the file's locked.

Name strName As strName

[NeoPa]

I suppose I should clarify that last remark.

When an open is requested of a spreadsheet that is open, instead of failing the call, it opens it in ReadOnly mode with a notification when the file becomes available for full ReadWrite access. This is pretty well useless in a code-controlled environment, so I treated it as a failure. I did try all sorts of things to open it (specifically asking for ReadWrite access in the open for instance) but nothing gave me what was required. The rename tweak gave me the closest thing to what was required.

[FishVal]

Maybe the workbook could be set to be shared and used as such.

[NeoPa]

I'm not familiar with that. What do you mean?

[FishVal]

I'm not familiar with that too. I know about this option but didn't use it.
MainMenu -> Tools -> Share WorkBook ..

[NeoPa]

Ah. I see.

I would imagine that using that option is likely to trigger prompts to the operator as to what should be maintained and what discarded in certain circumstances. Not good for when controlling by code.

It may be possible to make it work, but I'd be very careful before implementing a solution based on that.

[Paradigm]

Thank you both very much for your inputs.

Let me explain in a bit more detail, i actually dont want to open the excel file again, i just want to know how i can check if the file is open.
If its not then i want to open it and add an autofilter on it, if its already open then all i need to is to add an autofilter on it. ( all of this through access ofcourse).

Hope that clears it up just a tad bit.

Also i understand the first question was a bit complicated but maybe i didnt explain it properly.
It weould probably be best to take step by step advice.

What would be the process of inserting a query into excel where the query may vary in terms of the number of rows. Insert such that the other rows in the excel file are adjusted accordingly.

Thanks again.
Paradigm

[NeoPa]

@Paradigm
Please read through the article linked in post #2. It should answer all or most of this. If there are still issues you're unsure of, please explain what they are in particular and we will do what we can to help.
@Paradigm
I suggest you take this question and ask it again in it's own thread (as per the site guidelines). It would be quite messy to have two subjects going on in this thread, especially as it's not clear at this time exactly what you're after with this.

When reposting, please bear in mind that Excel doesn't have queries (as such - it has Get/Import External Data). It can have data pasted in from elsewhere. It can have the data from an Access recordset transferred directly. It would be helpful if, in your new thread, you made it clear what you envision happening.

[Paradigm]

Ok this is the code that i am using currently

Sub OPenMRZFile()
Dim oexcelApp As Excel.Application
Dim oexcelwb As Excel.Workbook
Dim strExcelFile As String
Dim Country As String
 
strExcelFile = "C:\Documents and Settings\abc\Desktop\ChangeTool\Resources\Data and Templates\MRZ.xls"
 
 
Set oexcelApp = New Excel.Application
 
oexcelApp.Visible = True
 
'input box on form
 
Country = Me.cmbcountrymrz 
Set oexcelwb = oexcelApp.Workbooks.Open(strExcelFile)
 
With oexcelwb
    Range("F4").AutoFilter Field:=6, Criteria1:=Country
End With
 
Set oexcelApp = Nothing
 
End Sub

The code is executed through a command button in access.
The problem i am facing is that if someone clicks it a first time it works perfectly, but once the excel file is open if the button is clicked again then it opens up the file in a read only format and doesnt change the autofilter.

What i want to be able to do is
if the file is open, to only change the filter
and
if its not open to open it and execute the autofilter

As for the other question ill make another thread and try to explain it better.

Thanks again for your time and patience guys, much appreciated!

Regards
Paradigm

[NeoPa]

@NeoPa
I tried to make this clear in my last post. Please reread and pay particular attention to the highlighted text. It was worded specifically to ensure that you didn't post a simple rewording of your problem.

I'm not trying to be unsympathetic, but we are here to help you understand and fix your problems, not to do that for you.

Please check out the article and respond with reference to that, if there are still issues you don't understand.

[Stewart Ross]

Hi. You are making a fairly fundamental mistake in your approach to automation here. Access communicates with Excel through the Excel application object you instantiate in your code (object variable oexcelapp in your case). For Access to be able to interact with Excel on multiple command button presses the application object has to be persistent between calls to your subroutine. But as the object is in scope solely within your subroutine it cannot persist between calls, even if you had not set it to nothing in line 23 (resetting the object variable to its undefined state). Excel itself will still run and the workbook will still be open, as it has not been explicitly closed before the application object was destroyed. It is the communication path used by Access which is removed when the application object goes out of scope like this.

If you click the button again, another instance of Excel is opened - but as the workbook concerned is still open, running in the unlinked Excel instance you first opened, an attempt to open it again leads to the 'read-only' copy you mention, which NeoPa also discussed in his earlier posts.

Access cannot communicate with Excel directly unless it has established the link through an active application object instance. It has no way of establishing contact with another open instance of Excel running in parallel but sitting outside the scope of any of its application objects.

To be able to re-use the same Excel application object between button presses on a persistent basis you will need to have the application object global in scope to the form concerned. That is, it should be defined as a public variable in the header code of the form, not in a sub within it.

If you let the application object go out of scope you lose it and cannot re-establish communication. It is like cutting a tow rope between vehicles, then trying to continue pulling one from the other as if nothing had happened - somewhat futile.

I cannot imagine why you need to make autofiltering some kind of user-dependent choice from Access however. If you are going to automate Excel this way you need to recognise that Excel should either be running entirely within Access's control, creating, opening and closing workbooks using Access VBA commands, or you use Access to open the workbook then let the user do the rest in normal interaction with Excel - cutting the link in the process. Trying to do both at the same time - using Access for automation whilst the user interacts with Excel in realtime - is asking for object scope conflicts, occuring for example if the user closes the newly-open Excel workbook manually, conflicting with any further programmed attempts by Access to refer to that workbook.

-Stewart

[FishVal]

Hello, Paradigm.

You could use DDE to make your application behave as you've described.
However, DDE is well outdated and the only advantage is that it allows to find running application and communicate with it.

Example:

    Dim lngCh As Long
 
    On Error GoTo DDEInitFailed
    lngCh = DDEInitiate("Excel", "<..path to desired workbook..>")
    On Error GoTo 0
 
    ' write something in A1 cell
    DDEPoke lngCh, "R1C1", "qqq"
    DDETerminate lngCh
 
    Exit Sub
 
DDEInitFailed:
    MsgBox "Workbook is not opened"