CODEWITHSUNDEEP
excelvba
sati_space
sati_space

Reputation: 77

Subscript out of range when declaring variable with filepath

I am having issues when calling the name of the variable to save the new file as xlsb.

This is for a new master file I'm working for in Excel. I already tried with the real name of the file I'm using, but when I declare it as a variable, an error keeps popping up

Option Explicit
Dim roster As String


Sub DisplayRegion()

Dim SpecificArea As String

SpecificArea = ActiveCell.CurrentRegion.Select

With Selection
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With


End Sub

Sub roster_conversion(roster As String)
'
' roster_conversion Macro
'

'

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Sheets("Sheet1").Select
    Range("A1").Select
    Workbooks.Open Filename:=roster
    Sheets("Sheet1").Select
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp

    DisplayRegion

    Workbooks(roster).SaveAs Filename:="Roster", FileFormat:=50
    Workbooks(roster).Close SaveChanges:=True

    Windows("Control.xlsm").Activate
    Sheets("Sheet1").Select
    Range("A1").Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Sub roster_calling()

roster = "C:\Users\ricardo.delgado\Downloads\Global L&D Roster Report.xlsx"

roster_conversion roster

End Sub

I expect the code to run smoothly as it was before. Now I am declaring the file inside a variable, and it keeps giving me the Subscript out of range error. Could you guys help me?

Upvotes: 2

Views: 187

Answers (1)

Vityata
Vityata

Reputation: 43593

The variable roster could be passed to roster_conversion as parameter. There it could be used:

Sub RosterCalling()

    Dim roster As String
    roster = "C:\Users\gropc\Desktop\1.xlsx"
    RosterConversion roster

End Sub

Sub RosterConversion(roster As String)

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim wkb As Workbook
    Set wkb = Workbooks.Open(Filename:=roster)
    wkb.Worksheets(1).Rows("1:1").Delete Shift:=xlUp
    wkb.SaveAs Filename:="Roster", FileFormat:=50
    wkb.Close SaveChanges:=True

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Additionallly - How to avoid using Select in Excel VBA

Upvotes: 2

Related Questions