CODEWITHSUNDEEP
excelvba
M.Ustun
M.Ustun

Reputation: 289

Workbook not taking in variable

I'm trying to create a few macros on Excel I have tried everything but I believe I'm missing something. I'm trying to create two Macros that do the following:

Macro 1 = Create a new workbook with the name specified and store the path for the created file

Macro 2 = populate the created workbook with the data in a text file.

So far Macro1 is working fine and the workbook is created it is even able to pass the path of the file into the second macro. The second Macro halts halfway through with the message

Subscript out of range (Error 9)

displayed at 

With Workbooks(nameOfFile)

Below are my Macros

Global nameOfFile As String

Sub createNewSheet()

Dim version As String

version = InputBox("What version is being tested?")
Workbooks.Add
ActiveWorkbook.SaveAs _
Filename:="C:\Users\Uk000827\Documents\RegimeProject\RTD_Regime " & version
nameOfFile = "C:\Users\Uk000827\Documents\RegimeProject\RTD_Regime " & version
End Sub

Sub addValueFromList()

Dim strFilename As String: strFilename = "C:\Users\Uk000827\Documents\RegimeProject\IntelArchive.txt"
Dim strTextLine As String
Dim iFile As Integer: iFile = FreeFile
Dim r As Integer

r = 1
Open strFilename For Input As #iFile

MsgBox nameOfFile 
With Workbooks(nameOfFile)
    Do Until EOF(1)
        Line Input #1, strTextLine
        Cells(r, 1) = strTextLine
        r = r + 1

    Loop
End With
Close #iFile

End Sub

Upvotes: 0

Views: 39

Answers (2)

Franz
Franz

Reputation: 360

You are trying to access a workbook by its full path. You just can do it via the filename (stripped of the path).

But there is a real problem with this code: If another workbook gets activated between the two macros being executed the second one just writes to that other workbook instead of the one named nameOfFile. That is because your with-Statement in fact doesnt do anything.

You may want to fill the cells using something like .Sheets(1).Cells(r, 1) = strTextLine. The With-Statement only refers to fields starting with a dot!

Upvotes: 1

user4039065
user4039065

Reputation:

Try this adjustment to createNewSheet (createNewBook...?)

Sub createNewSheet()

    Dim version As String

    version = InputBox("What version is being tested?")

    Workbooks.Add
    with activeworkbook
        .SaveAs Filename:=environ("USERPROFILE") & "\Documents\RegimeProject\RTD_Regime " & version, _
                FileFormat:=xlOpenXMLWorkbook
        nameOfFile = .name
    end with
End Sub

Upvotes: 1

Related Questions