Variable for workbook name 1

[UnsolvedCoding]

I am having a difficult time trying to get a workbook with a variable name to close. It will open but closing it is proving to be difficult.

The reason the name is a variable is because it changes as the season progress.

Test is the variable that holds the season and two digit year

This code will open the workbook just fine.
Workbook_Name = Drive & ":\" & Season & "\" & Folder & "\" & Test & " " & "Top100.xlsx"

' Open main workbook
Workbooks.Open Filename:=Workbook_Name

But to get the workbook to close I am running out of ideas and have tried everything I can think of.

The following (and a few variations) have all failed:

Workbooks(Workbook_Name).Close SaveChanges:=False

Workbook_Name.Activate
ActiveWorkbook.Close False

Workbook_Name.Activate
ActiveWorkbook.Close SaveChanges:=False

Workbook.(Test & " " & "Top100.xlsx").close

Workbook.(Test & " " & "Top100.xlsx").close SaveChanges:=False

Any ideas?

 

[SkipVought]

hi,

Dim Workbook_Name As String, wbNEW As Workbook

Set wbNEW = Workbooks.Open(Filename:=Workbook_Name)

'But to get the workbook to close I am running out of ideas and have tried everything I can think of.
'
'The following (and a few variations) have all failed:

wbNEW.Close SaveChanges:=False

set wbNEW = nothing

Since Workbook_Name is a STRING variable, you cannot use the Activate method on a string variable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Set WbNew = nothing and Set wbNEW = Workbooks.Open(Filename:=Workbook_Name)

both give "Complie Error: Invalid Outside Procedure

 

[SkipVought]

What application are you coding in?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Excel.

 

[SkipVought]

each must be in a procedure.

Please post ALL your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

There are over a dozen modules that hold information for the procedure being created. This code is used to create the path for the workbook to be opened

' Create name for first workbook
Workbook_Name = Drive & ":\" & Season & "\" & Folder & "\" & Document & " " & Season & " " & Alpha_Box_Array(K) & ".xlsx"


This is the sub that deals the most with the workbooks. The other subs open the workbook or put information into the workbook but do not close it.


Private Sub Open_Workbook_A()

' Open the required workbook
Workbooks.Open Filename:=Workbook_Name

' Fit workbook on right side of screen so user can see whats happening
Size_Workbooks_Right

' Must recreate Ilastrow for each new workbook
Ilastrow = 0

' Get the last row
Ilastrow = Range("D2").End(xlDown).Row

' Check info and place as needed
Put_Name

' Go back to report workbook
'ActiveWorkbook.Close SaveChanges:=True
Windows(Document & " " & Season & " " & Alpha_Box_Array(K) & ".xlsx").Close

' Reset the cell number
J = 2

'Clear variable
Workbook_Name = Empty

' Create name for next workbook
Workbook_Name = Drive & ":\" & Season & "\" & Folder & "\" & Document & " " & Season _
& " " & Alpha_Box_Array(K) & ".xlsx"



Exit Sub

Error_Handler:

If HighK = K Then
''' wbNEW.Close SaveChanges:=True
''' Set wbNEW = Nothing
Exit Sub
End If

' Let the user know something is wrong
MsgBox Err.Description & " See Status Bar"

'Tell the user what went wrong
Application.StatusBar = "Open_Workbook_A failed. " & Err.Description

' Set flag to indicate problem
Warning_Flag = 1

Exit Sub

End Sub

 

[UnsolvedCoding]

Would it be easier to do something like this?

sFileName = ActiveWorkbook.Name

Workbooks(sFileName).Activate

Workbooks(sFileName).Close False

 

[SkipVought]

Where is the error occurring?

Where did you assign wbNEW to a workbook?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

I figured it out.

Based on what you said I had to make some changes. Typically all the variables and constants are public and housed in one module to make it easier to track. Comments for each are kept so it is clear what does what.

Given your comment about needing to have them in each procedure changes were made and now it works.

Thanks much.