1

I have a Project that i have to finish soon but i get error when i try to Activate an Excel file with a variable inside of its Name.I get a runtime error 9 all the time even if I tried almost every Solutions that People suggested me.Thatswhy i send you the whole link, where it can be another Problem which causes this error.

Sub M01_Neue_Maßnahme()
 'Variablen definieren
  Dim Ord As String
  Dim mNummer As String
  Dim Jahr As String
  Dim Welle As String
  Dim Name As String
  Dim mNummerGanz As String
  Dim Exportart As Integer
  Dim strOrdner As String
  Dim meldung As String
  Dim AlterLinkKurz As String
  Dim verknuepfungsname_ist As String
  Dim verknuepfungsname_soll As String
  Dim verknuepfungsname_soll_teil As String



 Exportart = Worksheets("Vorgaben").Range("C5").Value
 Ord = Worksheets("Vorgaben").Range("C4").Value

User has been asked to fill out two Input Box, which is for documenting the Excel file while saving it.

  mNummer = InputBox("Bitte Maßnahmennummer eingeben")
  Welle = InputBox("Bitte Welle auswählen", , "0" & Worksheets("Vorgaben").Range("B15").Value)

mNummerGanz = mNummer & "" & "" & Welle

 Dim a As String
 Dim b As String


AlterLinkKurz = Worksheets("Eingabefeld").Range("AO47").Value

 aLinks = ActiveWorkbook.LinkSources()
   If Not IsEmpty(aLinks) Then
      For i = 1 To UBound(aLinks)
         verknuepfungsname_ist = Mid(aLinks(i), InStrRev(aLinks(i), "\") + 1, Len(aLinks(i)) - InStrRev(aLinks(i), "\"))

            verknuepfungsname_soll_teil = Mid(AlterLinkKurz, InStrRev(AlterLinkKurz, "\") + 1, Len(AlterLinkKurz) - InStrRev(AlterLinkKurz, "\"))
                If verknuepfungsname_ist = verknuepfungsname_soll_teil Then
                    'Durch kopieren der xlsx modifizierte Links werden zurückgesetzt
                        If aLinks(i) <> AlterLinkKurz Then
                            AlterLinkKurz = aLinks(i)

                        End If


                End If

    Next i
End If
   NeuerLink = Worksheets("Vorgaben").Range("C10").Value



 For Each link In ActiveWorkbook.LinkSources(xlExcelLinks)
If InStr(link, AlterLinkKurz) > 0 Then

Application.DisplayAlerts = False
ActiveWorkbook.ChangeLink link, _
NeuerLink, xlLinkTypeExcelLinks
End If
Next

Saving the file with the a variable Name under "Dateiname"

If Exportart = 1 Then


  If Dir(Ord, vbDirectory) <> "" Then


  Else
  MsgBox ("Standardpfad nicht vorhanden." & vbCr & "Datei wird im folgenden Verzeichnis abgelegt:" & vbCr & vbCr & Ord)
  MkDir Ord
  End If

Dateiname = Ord & mNummerGanz & "_" & Name & ".xlsm"
ThisWorkbook.SaveAs Filename:=Dateiname

Now i open a file called 1.xlsm, i want to copy a Content from this file and then activate the variable Named file and paste it on that file. But i get an error.

'Opening 1.xlsm
   ChDir _
          "Z:\1000_Entwicklung\05_PROJECT\0558000_CFT\055800L_CFT_Projektleitung\99_Arbeitsordner PL\Tanverdi\SummaryPPT"
Workbooks.Open Filename:= _
    "Z:\1000_Entwicklung\05_PROJECT\0558000_CFT\055800L_CFT_Projektleitung\99_Arbeitsordner PL\Tanverdi\SummaryPPT\1.xlsm"

Range("G5:P41").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

I wanted to paste the content in the file which i have saved under variable Dateiname, i get runtime error 9. 

Windows(Dateiname).Activate

I am sorry this could be easy to ask but i am new at VBA and Need so much your help.

Yigit Tanverdi
  • 161
  • 6
  • 18
  • The array you are trying to access contains fewer elements than you expect. For example, attempting to access the 11th element of an array dimensioned for ten elements. To correct this error Make sure your code contains bounds checking that ensures elements beyond the scope of the array are not referenced. Re-dimension the array to a larger size that meets your needs. –  Feb 19 '16 at 09:08
  • i did not understand your solution. I have Problem on activating the file named Dateiname – Yigit Tanverdi Feb 19 '16 at 09:40
  • for you (german:) https://msdn.microsoft.com/de-de/library/w8k3cys2.aspx (or english: https://msdn.microsoft.com/en-us/library/w8k3cys2.aspx) – Kathara Feb 19 '16 at 10:25
  • try activating the worksheet after you have activated the workbook, or use with activesheet / end with tags around your range selection. also, you dont appear to have activated your workbook after you have opened it. (This last part may not be an issue, but i find it better safe than sorry) – DDuffy Feb 19 '16 at 11:17
  • and maybe instead of windows rather use workbooks().activate – Kathara Feb 19 '16 at 15:10
  • Set a workbook object to be the workbook you open and reference that. Somethingnlike set WB=application.workbooks.open.... WB.range..... Etc – Nathan_Sav Feb 19 '16 at 16:54

0 Answers0