Run-time error 1004 when opening Excel from Access upon second execution

[newbieX]

I wasn't sure whether to post to the Access or Excel forum since I am using Access to call Excel. Went with Excel since this forum has way more viewers. Sorry if I got it wrong.

The following script runs fine the first time I click the command button on an Access form but after I close the Excel workbook and click the command button again to edit a new spreadsheet, I get the follow error message:

Run-time error '1004': Method "Columns' of object'_Global failed.

It fails here:

Columns("H:H").Select

The script is being run from Access 2007 and is opening a spreadsheet in Excel 2007 (but using xls extension)

Private Sub IdahotoExcel_Click()

Dim dlg As FileDialog
Dim idahofile As String
Dim xlApp As Object

Set dlg = Application.FileDialog(msoFileDialogFilePicker)
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

With dlg
    .Title = "Select the WADDL Excel file to import"
    .InitialFileName = "S:\WildlifeHealth\Idaho\Incoming\"
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "XLS", "*.xls*", 1
    If .Show = True Then
        idahofile = .SelectedItems(1)
    End If
End With

xlApp.Workbooks.Open idahofile, True, False

Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1").Select
ActiveCell.FormulaR1C1 = "WHNO"
Range("G1").Select
ActiveCell.FormulaR1C1 = "ClientID"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Element"

'Set xlApp = Nothing

End Sub

Been monkeying with code all morning and still no solution. Ideas anyone?

 

[CodeNinja]

newbieX,
I ran your code, and had no problem the second time through. I am assuming you have the office 14.0 object library and the excel 14.0 object library added to your references as that is what I needed to run your code.
Although your code ran fine for me, I have played with similar code and found that one of two things could cause an issue. First, if I was not doing a proper clean up, I would sometimes get the same problem. Basically, I think it is something like the code is trying to create a new handle that already exists when you set xlApp. Try adding the following lines of code before you set xlApp = nothing

xlApp.saved = true 'This prevents the code from saving over the file
xlApp.close
set xlApp = nothing

The other problem I had was when I was trying to load massive data sets. Sometimes excel would crash while trying to open the file. This only happened to me when I was dealing with files that had hundreds of thousands of rows. I put in an error handler routine to inform the user a problem had occurred, do a little clean up, and ask them to try and re-load the file. Usually they could get through it the second time without a problem.

Hope this helps,

CN.

 

[newbieX]

I have office 12 and excel 12 object libraries selected. No 14 available.

Can't "clean up" files by closing the spreadsheet as user needs to do some editing before closing the file. The user has to close the file from excel. I suspect this is the problem but cannot figure out the workaround.

Files are not overly large.

 

[CodeNinja]

NewbieX,
So your user has to close the excel spreadsheet themselves? That is unfortunate. It seems to me you loose control there, and open yourself up to a lot of potential bugs. I would first evaluate if that is really the case... Can you somehow direct the user through any changes needed? If the answer is absolutely no, then I have an idea.
First, I would not create the xlApp as an object. Instead, I would include the Excel object and dim it as xlApp as excel.Application. Then, if you must, you can create it as an array (1 to 1000) and choose a random number and instantiate that random xlApp() instead. This way you are pointing to a new, un-used one (most likely).
I am sure someone else has a better answer, but at the moment it is all I can think of.

good luck,

CN.

 

[newbieX]

I wonder if it would work (if even possible) to create a stand alone script that is called from the Access form? Theorectically, the script could accept the passed idahofile variable (the name of the file to open) and open the file in excel from within the stand alone executable file. That way Access does not hold onto the excel file since it never really opened it. Hopefully the stand along script would no longer have a hold on the file after executing.

If possible, what program might I want to use? I am somewhat familiar with python. What about VBScript? I have never used it but am willing to give it a try if a viable solution. Coding suggestions to get me going greatly appreciated.


</pre>

 

[newbieX]

I wonder if it would work (if even possible) to create a stand alone script that is called from the Access form? Theorectically, the script could accept the passed idahofile variable (the name of the file to open) and open the file in excel from within the stand alone executable file. That way Access does not hold onto the excel file since it never really opened it. Hopefully the stand along script would no longer have a hold on the file after executing.

If possible, what program might I want to use? I am somewhat familiar with python. What about VBScript? I have never used it but am willing to give it a try if a viable solution. Coding suggestions to get me going greatly appreciated.

This is exactly what I ended up doing. It appears that when I open an Excel spreadsheet from Access and close it from Excel, Access still maintains a hold on it. From what I could tell the only work around was to run the Excel formatting requirements from Access and then close Excel once it was formatted the way I wanted. From there I created a batch file that opened the Excel file for user data entry and opened it from Access.

Also, the script was changed to reference specific workbook rather then "ActiveWorkbook" which was also causing the 1004 error message.

In case someone else wants to use this as an example, code that works is now as follows.

[COLOR=#1F497D]Private Sub OpenWB(var1 As String)[/COLOR]

  [COLOR=#1F497D]    Dim RetVal[/COLOR]
  [COLOR=#1F497D]    RetVal = Shell("S:\WildlifeHealth\Idaho\Incoming\test.bat " & var1, vbNormalFocus)  'variables go on outside of quotes, the actual value inside[/COLOR]

  [COLOR=#1F497D]End Sub[/COLOR]

  
  [COLOR=#1F497D]Private Sub IdahotoExcel_Click()[/COLOR]
  
  [COLOR=#1F497D]Dim dlg As FileDialog[/COLOR]
  [COLOR=#1F497D]Dim idahofile As String[/COLOR]
  [COLOR=#1F497D]Dim xlapp As Object[/COLOR]
  
  [COLOR=#1F497D]Set dlg = Application.FileDialog(msoFileDialogFilePicker)[/COLOR]
  [COLOR=#1F497D]With dlg[/COLOR]
  [COLOR=#1F497D]    .Title = "Select the WADDL Excel file to import"[/COLOR]
  [COLOR=#1F497D]    .InitialFileName = "S:\WildlifeHealth\Idaho\Incoming\"[/COLOR]
  [COLOR=#1F497D]    .AllowMultiSelect = False[/COLOR]
  [COLOR=#1F497D]    .Filters.Clear[/COLOR]
  [COLOR=#1F497D]    .Filters.Add "XLS", "*.xls*", 1[/COLOR]
  [COLOR=#1F497D]    If .Show = True Then[/COLOR]
  [COLOR=#1F497D]        idahofile = .SelectedItems(1)[/COLOR]
  [COLOR=#1F497D]    End If[/COLOR]
  [COLOR=#1F497D]End With[/COLOR]
  
  [COLOR=#1F497D]Set xlapp = New Excel.Application[/COLOR]
  [COLOR=#1F497D]Set xlwb = xlapp.Workbooks.Open(idahofile)[/COLOR]
  [COLOR=#1F497D]Set xlsh = xlwb.Sheets("UI_Crosstab_Report_NDOW2")[/COLOR]
  
  [COLOR=#1F497D]With xlsh.Range("G1")[/COLOR]
  [COLOR=#1F497D]    .Value = "ClientID"[/COLOR]
  [COLOR=#1F497D]End With[/COLOR]
  [COLOR=#1F497D]With xlsh.Range("H1")[/COLOR]
  [COLOR=#1F497D]    .Value = "Element"[/COLOR]
  [COLOR=#1F497D]End With[/COLOR]
  [COLOR=#1F497D]With xlsh.Range("H:H")[/COLOR]
  [COLOR=#1F497D]    .Select[/COLOR]
  [COLOR=#1F497D]    .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove[/COLOR]
  [COLOR=#1F497D]End With[/COLOR]
  
  [COLOR=#1F497D]xlwb.Save[/COLOR]
  [COLOR=#1F497D]xlwb.Close[/COLOR]
  [COLOR=#1F497D]xlapp.Quit[/COLOR]
  
  [COLOR=#1F497D]Set xlapp = Nothing[/COLOR]
  
  [COLOR=#1F497D]Call OpenWB(idahofile)[/COLOR]

  
  [COLOR=#1F497D]End Sub[/COLOR]

Test.bat has just two words.

start %1