1

I am using the below script to open an Excel file. The script works fine and have never ran into any issues, except now.

So this is what I have: I open Excel using VBScript. Within the Excel app I click to open another Excel file, write some data, then close it and continue working away in the first workbook I opened. The problem is when I run the macro, Excel just crashes and closes it. I know that if I open Excel on its own, I have no issues.

Dim xlApp 
Set xlApp = CreateObject("Excel.Application") 
xlApp.Workbooks.Open "H:\APPLICATIONS\SEAT AUDIT\APP\SEAT AUDIT.xlsm"
Set xlApp = Nothing

and this is the macro that causes Excel to crash:

Sub log()
  Dim rng As Range
  Dim nwb As Workbook
  Dim FileName As String
  Dim var
  Dim var1
  Dim var2
  Dim var3
  Dim var4  
  Dim var5
  var1 = frmsetup.cmbauditor.Text
  var2 = frmsetup.lblsequence.Caption 
  var3 = frmsetup.cmbtrimstyle.Text
  var4 = Format(Now, "yyyy-mm-dd")
  var5 = "SEQ-" & frmsetup.lblsequence.Caption & " "
  FileName = var5 & var4

  Set nwb = Workbooks.Open("H:\APPLICATIONS\SEAT AUDIT\LOG FILES\Seat Audit Log.xlsx")

  Dim emptyRow As Long
  emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  Set rng = Cells(emptyRow, 5)
  rng.Parent.Hyperlinks.Add Anchor:=rng, Address:="H:\APPLICATIONS\SEAT AUDIT\QUERY RESULTS\SEAT AUDIT - PDF\" & FileName & ".pdf",    TextToDisplay:="CLICK HERE!"
  Cells(emptyRow, 1).Value = var1
  Cells(emptyRow, 2).Value = var2
  Cells(emptyRow, 3).Value = var3
  Cells(emptyRow, 4).Value = var4
  ActiveWorkbook.Save
  ActiveWindow.Close
End Sub
Community
  • 1
  • 1
INOH
  • 365
  • 2
  • 9
  • 27
  • What if you `F8` your way through `log`? It looks like `WorksheetFunction.CountA` is working against the active workbook...are you sure the workbook you want it to work against is ALWAYS the active workbook? I'd personally use a better method of finding the last row (maybe like `nwb.sheets("Sheet1").UsedRange.Rows.Count + 1` because it tells Excel specifically: find the last row on sheet 1 in the nwb book). The next lines (`Cells(emptyRow, n)`) might be problematic for the exact same reason. Cells(r,c) of which sheet and workbook? [Specifics Bob](http://www.youtube.com/watch?v=qHDSNs9wBpU) ;) – Tim Aug 04 '15 at 19:42
  • Let me explain, I have created a program in which will collect data, what i then do is open up the log workbook, create the a log name and a hyperlink to a pdf file, from which i wiill create from the data entered into the program after they are finished entering data. So it seems that when i try to open up another existance of excel it crashes. for some reason if i run program straight from excel xlsm file it runs without a hitch. – INOH Aug 04 '15 at 20:12
  • The program (which is the VBScript?) collects data, and you open a log workbook, and you create a link to a pdf file that you created based on data that a user enters into your VBScript program? I guess I'm confused about your code then. And placing a `stop` in your VBA code and pressing `F8` to go one line at a time always works if you don't have an existing instance of Excel open first? If that's true, then go back and read my first comment about referencing activeworkbook/cell. If it only works when Excel is already open, what happens when you `stop` execution and go line by line (`F8`)? – Tim Aug 04 '15 at 20:47
  • Sorry I must of confused you, I have created a program using excel in which a userform collects various data. I used notepad to create a .vbs to run my excel program as it has macros and i didnot want to have each user have to enable, since i use a userform as splashscreen on open. So i used the .vbs to open the excel program. When i am in the excel program after a few userforms after the user has entered a few keys data items, i then run the macro "log" to open up an existing xlsm spreadsheet log and populate the log list to keep track, it then closes that spreadsheet. – INOH Aug 04 '15 at 21:00
  • I think it has something to do with the activewindow.close because if i remove it it runs but the excel existance still is open in background, sorry if i confused you – INOH Aug 04 '15 at 21:01
  • 1
    Read this: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros I still suspect your issue is with assuming the items you want to work with (ie, `WorksheetFunction.CountA` or `ActiveWindow.Close`) are actually active. However, if it works up until `ActiveWindow.Close`, then try using `Application.Quit` instead. Remember to save the workbook (or at least set the workbook's `.Saved` property to `True`) before quiting or you will get a warning about closing an unsaved workbook. – Tim Aug 04 '15 at 21:14
  • Tim, thanks for the help, You were right CountA i believe did create havoc i changed the code to this and added windowstate=xlmaximized and all seems to work smoothly now here is the adjusted code – INOH Aug 05 '15 at 11:54
  • The VBScript you posted can't possibly be what you're actually running, because the Excel instance isn't visible when started that way, so you wouldn't be able to click anywhere. Please show your actual code, not something you made up or typed from memory. – Ansgar Wiechers Aug 05 '15 at 15:35
  • To explain, I created a xlsm file, when open shows a userform. I used notepad created the vbs file above to open the xlsm file.(this way the user is not prompted to enable macros) after data is entered it runs the log script to add file name, date, user and a hyperlink, and then closes that file, then user continues through the form and does various other inputs. – INOH Aug 05 '15 at 18:16
  • 1
    Make the Excel instance visible during debugging, so you get to see whatever error pops up. Also try adding popups throughout the problematic function so you can find out at which instruction exactly the thing crashes. – Ansgar Wiechers Aug 05 '15 at 19:05

0 Answers0