Problems using VB to run Excel as hidden

[omahadivision]

Hello All,

I have created a macro in my Access form that opens Excel, runs a Macro in Excel (Autorun), copies some data over, and then closes Excel.

My program works about 90% of the time. When it does, the Excel Macro asks me to open a text file, then analyzes it an pastes it in Access. Sometimes, however, it simply never loads. In this case, I open up the task manager and find Excel.exe and end it, breaking the macro chain. Oddly enough, this never happens when OpenExcel.Visible = True.

I've tried hard to think of what might be causing this, but I've seen no pattern of when it does work and doesn't. Whenever I open Excel after the macro didn't work, there's always several autosaved versions of it.

Here is the Access code:

Private Sub FTIR_Spectrum_KeyPress(KeyAscii As Integer)
  'http://www.mrexcel.com/forum/microsoft-access/210364-opening-excel-file-run-macro-access.html
  
   
Dim OpenExcel As Object
Set OpenExcel = CreateObject("Excel.Application")
OpenExcel.Workbooks.Open CurrentProject.Path & "\FTIR\FTIRConverter2.1AccessExport.xlsm"
OpenExcel.Visible = False
OpenExcel.Run ("Autorun")

DoCmd.RunCommand acCmdPaste

'Get JustName value from Excel sheet, will be linked to label box which is linked to open function
JustName = OpenExcel.Worksheets(1).Range("H2").Value
OpenExcel.Workbooks.Close
Vial_Label.Value = JustName 'Stores value from Excel into Access

End Sub

And just in case it helps, here is "AutoRun" in Excel. Note the save command on the bottom, might this be part of my problem?:

Sub AutoRun()
'This section clears all values from column B (A remains contant)
'http://answers.yahoo.com/question/index?qid=20070813144212AAP9nGe
Range("B1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value <> Target And ActiveCell.Value <> Target2 Then
ActiveCell.EntireColumn.ClearContents
Else
ActiveCell.Offset(0, 1).Activate
End If
Loop

'This section runs the Load TBL File prompt
'http://en.allexperts.com/q/Excel-1059/Importing-Text-Files-Excel.htm

  Dim A    As Single, B  As Single
  Dim iRow    As Long
  Dim Fname   As Variant
  Fname = Application.GetOpenFilename("TBL Files (*.TBL),*.TBL", , _
           "Select Spectra File")
  If Fname = False Then Exit Sub
  Open Fname For Input As #1
  iRow = 1
  Do While Not EOF(1)
     Input #1, A, B
     Cells(iRow, 2) = B
     iRow = iRow + 1
  Loop
  Close 1
  
'This section places the input filename in the spreadsheet
'http://www.excel-vba-easy.com/vba-programming-excel-vba-variable.html
Range("H1").Value = Fname

'Takes off file extension from input file, stores as ShortName
'http://www.ozgrid.com/forum/showthread.php?t=31204
'http://www.excelforum.com/excel-programming-vba-macros/376333-how-to-split-filename-from-filepath.html

    Dim strFPath As String
    strFName = Fname
    ShortName = Replace(strFName, ".TBL", "")
    
    ShortNameb = ShortName
    
     'Split the rest of filename for just the name of the file without extension
     SpectraName = Right(ShortNameb, Len(ShortNameb) - InStrRev(ShortNameb, "\"))

    Range("H2").Value = SpectraName
    
'Saves as Excel File

ActiveWorkbook.SaveCopyAs Filename:="" & ShortName & ".xlsm"
'http://excelusergroup.org/forums/p/1933/5532.aspx
'http://www.ozgrid.com/forum/showthread.php?t=19555


'This section copies as BMP
'http://peltiertech.com/Excel/XL_PPT.html

    Dim objChart As ChartObject
       
    On Error Resume Next
    Set objChart = ActiveSheet.ChartObjects(1).Select
    
    Selection.CopyPicture _
    Appearance:=xlScreen, Format:=xlBitmap
    
'Completion message (hidden)
'MsgBox "Excel file saved as " & ShortName & ".xlsm"

ActiveWorkbook.Save

End Sub

[Missinglinq]

I never do this sort with Excel, but in general terms, it would appear that for some reason, Excel is sometimes popping a dialog box that requires user interaction, and you can't see it, of course, because Excel is in hiding.

I believe Excel automatically Saves a copy of worksheets, when things go terribly awry, and going awry may include the popup being ignored, or possibly actions taken, such as <Enter> or <Esc> being hit when the thing obviously stops responding. This may be where your autosaved copies are coming from.

Excel Help does advise that you should always use ActiveWorkbook.SaveAs, with the appropriate parameters, rather than ActiveWorkbook.Save, the first time you do a Save in code.

Linq ;0)>

[June7]

Have you step debugged? Refer to link at bottom of my post for debug techniques.

[help_me_with_access]

I believe that office objects are invisible by default until they are spec'd to be visible by the code exec.

are you sure it's worth it to say:

xl.visible = false

it seems to be redundant. get rid of it if that's the case.

also:

app.getOpenFileName

.....doesn't that produce a dialog by default?? I think it does...if so that is trouble for you. I would find a different way.

[omahadivision]

[QUOTE=help_me_with_access;137142]I believe that office objects are invisible by default until they are spec'd to be visible by the code exec.

are you sure it's worth it to say:

xl.visible = false

Taking out the force hide part and changing the other line to SaveAs instead of Save seemed to help, but now it asks every time if I want to overwrite the file. What's the best way to avoid that?

[help_me_with_access]

the best way to avoid that whole thing is probably to turn the notifications off??

something like "setwarnings"?? or is that in access? hell I forget by now!

I know there is equivalent in xl but I forget what the function is. by look at the saveas() args. there's probably an overwrite option in there somewhere anyway. there has to be.

[June7]

In Access/VBA it is:
DoCmd.SetWarnings False
'code here
DoCmd.SetWarnings True

However, might not apply because don't think that is really a warning popup. Review:

http://www.mrexcel.com/forum/excel-q...-question.html
http://answers.microsoft.com/en-us/o...d-59979179da3f

[omahadivision]

Thanks guys! I am marking this as solved.