Runtime error 9 1

[Rybrookar]

Hello All - first post so please be gentle if I'm not clear enough on question.

I have a macro that runs from a fix file. (Users have identical files but may have different names for them).

The basic idea is they have their file open and then open the Fix File. An automatic macro then goes to their file and sorts errors on the spreadsheet.

This works for me and quite a foew of the users (there are about 60 users) but some get a Run Time error 9.

They are crashing out on first line "Activewindow.activatenext"

This means the code is not recognising their own file.

Is this a version issue - I'm using excel 2003- and is their code that will work in every version?

Thanks in advance

 

[SkipVought]

Hi,

Hard to say without looking at all the code.

I perfer to stay away from ActiveWhatever, as you can never be absolutely sure, exactly WHAT is active.

Skip,
[sub]
[red][/red]
[/sub]

 

[Rybrookar]

Code looks like this


ActiveWindow.ActivateNext
Sheets("CMA").Select

Columns("K:K").Select
Selection.Replace What:="!j", Replacement:="!k", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Works for many users but crashes out on first line for some. If there is an alternative way of picking up the correct file to "fix" then I'd be happy to use it. Remember the files to be fixed could have any name.

Thanks

 

[SkipVought]

The FIX workbook should be OPEN before this code runs. The code must run in [blue]the workbook being fixed[/blue] by way of a button or Tools>Macro>Macros>...

'[s]ActiveWindow.ActivateNext[/s]
    [blue]Activeworkbook[/blue].Sheets("CMA").Columns("K:K").Replace _
        What:="!j", _
        Replacement:="!k", _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        MatchCase:=False, _
        SearchFormat:=False, _
        ReplaceFormat:=False

Skip,
[sub]
[red][/red]
[/sub]

 

[Rybrookar]

The users are mixed in ability so the fix macro opens automatically.

The user has their file open. They open the workbook containing the fix macro and it auto runs the macro. Will the code you've noted do this without having to get them to choose to run a macro?

PS - thanks for the help so far, much appreciated.

 

[SkipVought]

Maybe ther are TWO or THREE workbooks open. WHICH ONE is the macro to reference?????

Due to these uncertanties and ambiguities is why SOME have problems.

Skip,
[sub]
[red][/red]
[/sub]

 

[Rybrookar]

I ran through it over the phone with one of the "some" and ensured they only had one file open before opening the fix file.

Incidentally, same issue happened with the original fix whereby they stated the name of their file in a cell (on the fix workbook) and the code used the stated name. I thought maybe they typed the name incorrectly hence the reason for removing this part.

I have ran this code on my pc and it works as intended.

While you're thinking about this maybe you could give some direction on what I originally wanted to do which was check for open workbooks and put the name in a section on the fix workbook automatically. I've seen this done but can't figure it. So if they had more than one workbook open it would show the names in cells on the fix workbook.

Again - thanks for the help.

 

[SkipVought]

dim wb as workbook
for each wb in workbooks
  msgbox wb.name
next

CAVEAT: if you have more than ONE instance of Excel running, it only references the workbooks in the active instance.

Skip,
[sub]
[red][/red]
[/sub]

 

[Rybrookar]

Thanks Skip - that works well by giving me a message box with each open file.

If I wanted to put the name in (example) a1 for first workbook, a2 for second workbook, a3 for third workbook etc, how would I add this code.

I gave it a try but failed..

Thanks

 

[SkipVought]

On what sheet of what workbook?

Skip,
[sub]
[red][/red]
[/sub]

 

[Rybrookar]

Sheet 1 of Fix workbook

That means the auto macro can run and log the workbboks open. Noone can then tell me they only have one file open if more than one workbook is noted.

I could also then use the file name from A1 (assuming that is where it puts their file name) instead of using activewindow.activatenext. So my code would use the file name from cell A1.

Thanks.

 

[SkipVought]

Sub RecordOpenWorkbooks()
  Dim wb as workbook, lRow as long
  sheet1.[A1].currentregion.clear
  lrow = 1
  for each wb in workbooks
    if wb.name <> ThisWorkbook.Name
      sheet1.cells(lrow, "A").value = wb.name
      lrow = lrow + 1
    end if
  next
  Thisworkbook.save
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[Rybrookar]

Excellent Skip - very much appreciated.

The Pesonal Workbbok name comes up first but I think I can eliminate this.

Thanks a lot

R

 

[SkipVought]

Ahhhhhhhh, see what I mean?

"...and ensured they only had one file open before opening the fix file..."

Oh, I COMPLETELY forgot about the PERSONAL.XLS workbook!!!!!

Some have one. Some do not.

Seems you need MORE logic in your Workbook_Open procedure to accomodate ALL the possibilities!!!!!!


Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

Maybe something like this...

Sub RecordOpenWorkbooks()
  Dim wb as workbook, iCnt as integer, ThisWB as workbook
  sheet1.[A1].currentregion.clear
  icnt = 0
  for each wb in workbooks
    if wb.name <> ThisWorkbook.Name and ucase(wb.name) <> "PERSONAL.XLS" Then
      set thiswb = wb
      icnt = icnt + 1
    end if
  next
  if icnt = 1 then
     thiswb.Sheets("CMA").Columns("K:K").Replace _
        What:="!j", _
        Replacement:="!k", _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        MatchCase:=False, _
        SearchFormat:=False, _
        ReplaceFormat:=False

  else
    msgbox "Please close other workbooks"
  end if
  set thiswb = nothing
End Sub

Skip,
[sub]
[red][/red]
[/sub]