Access VBA Issue

--amadeus--

Hi folks,

I have a transient error that is driving me demented.

As part of a set of actions a table in Access is being exported to Excel and some simple formatting is being done on it there. Sometimes it works, othertimes it wigs out when attempting to activate the Excel app window.

Code is:

varname = Me.Combo3.Value

varname = Replace(varname, "/", "-")
varname2 = "Agenda for " & varname & ".xls"
varname = "\\blahpath\Agenda for " & varname & ".xls"

Dim wordapp As Object

Set wordapp = CreateObject("excel.application")
DoCmd.OutputTo acOutputTable, "Temp", acFormatXLS, varname, True
'ReturnValue = Shell("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE", 1)
'AppActivate ReturnValue

    Windows(varname2).Activate
        ''####### excel code
      

The various varnames are for later, naming and saving files to the network, etc. I think it's bombing if the Excel application doesn't have focus - if it does it can activate the window, if not it can't. As you can see I have tried the AppActivate command but that won't work either. Error is "Subscript out of range"

Help!

RangeR

I'm not understanding your problem. Can you post code that shows the problem, and not pieces of code that isn't used. Remove your comments and unused variables.

Than describe what happens when you run the code and what errors you get.

As an aside, Excel doesn't have to be the active window for you to do anything. In fact, Excel can be invisible while you do your updating. Then show Excel when finished.

--amadeus--

RangeR wrote: »

I'm not understanding your problem. Can you post code that shows the problem, and not pieces of code that isn't used. Remove your comments and unused variables.

Than describe what happens when you run the code and what errors you get.

As an aside, Excel doesn't have to be the active window for you to do anything. In fact, Excel can be invisible while you do your updating. Then show Excel when finished.

Errr, that is the code that shows the problem. The entire project has several hundred lines of code, I've taken out the parts that are specifically relevant:

varname = Me.Combo3.Value

- Populating the variable used in the piece of code that crashes

varname = Replace(varname, "/", "-")
varname2 = "Agenda for " & varname & ".xls"
varname = "\\blahpath\Agenda for " & varname & ".xls"


- Small changes to the variable that is used. The second variable was in between so no real gain in deleting it.

Dim wordapp As Object

Set wordapp = CreateObject("excel.application")
DoCmd.OutputTo acOutputTable, "Temp", acFormatXLS, varname, True


- Working code that creates the output file. I know that you can update without an application showing but that's not the case here.

'ReturnValue = Shell("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE", 1)
'AppActivate ReturnValue


- One potential solution left in to show what I had attempted that didn't work

Windows(varname2).Activate
''####### excel code



The actual line of code that generates the error, which is - as I said - "Subscript out of range".

So when I run the code sometimes it works - it runs through fully. Other times it gets to the indicated line and generates the error.

Does anyone have any bright ideas?

RangeR

It's been a while since I did this sort of stuff but can't you do something like

wordapp.visible = true

rather than windows(x).Activate?

For example

Dim wordapp As Object
Dim wrkbk As Excel.Workbook

Set wordapp = CreateObject("excel.application")
Set wrkbk = wordapp.Workbooks.Add()
[B]wordapp.Visible = True[/B]

'do your Excel stuff here

[B]wrkbk.SaveAs sFileNAme, FileFormat, blah, blah, blah[/B]

Works for me. I'm using Office 2007 but the principle is the same for older versions.

RangeR

Just wondering if my suggestion worked for you?

--amadeus--

I haven't tested it yet!

I'm working across four biggish projects as well as a few smaller ones and I'm not due to go back to this until Tue at teh earliest - it's on a client site and I don't have access to the full files here. Once I test it I'll let you know!