Source
r/excel•
Siendra

(VB) Closing Workbook with Dynamic Name?

unsolved

Hi,

I'm working on a sheet where a user specifies a source file to be used to generate additional formatted sheets. I have a macro that generates said sheets and opens the source file to allow them to populate, but I can't figure out how to get the source workbook to close.

Here's how I open the source workbook:

Dim wSh As Excel.Worksheet
Set wSh = ActiveWorkbook.Sheets("Lists Formulas")
Workbooks.Open Filename:=wSh.Range("'Lists Formulas'!E3") & wSh.Range("'Lists Formulas'!E6")

Lists Formulas'!E3 is the filepath and Lists Formulas'!E6 is the source file the user selects. I've tried a few methods of closing it, for example:

Workbooks(wSh.Range("'Lists Formulas'!E6")).Close SaveChanges:=False

or

Workbooks.Close Filename:=wSh.Range("'Lists Formulas'!E3") & wSh.Range("'Lists Formulas'!E6") SaveChanges:=False

I tried a few other things, like defining the Filename for the Open as a variable and string, but haven't gotten anywhere. The overall macro is working just fine right now except that I can't close that source workbook.

Edit: I'm using Excel on an Office 365 sub.

Posted Jan 28, 2022, 4:18 PM UTC · edited

16
AutoModeratorMOD•

u/Siendra - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1
ItsJustAnotherDay-95•

The workbook path should be the full path. Have you tried:

Workbooks(wSh.Range("'Lists Formulas'!E3") & wSh.Range("'Lists Formulas'!E6")).Close SaveChanges:=False
1
SiendraOP•

Yes, it gives me a subscript out of range error.

1
ZavraD53•
Dim FN as String
FN=wSh.e("'Lists Formulas'!E3") & wSh.Range("'Lists Formulas'!E6")
Workbooks(FN).Open
.
.
.
Workbooks(FN).Close
1
SiendraOP•

Is wSh.e a typo? It gives me a method not found error.

Using:

Dim FN As String
Set FN = wSh.Range("'Lists Formulas'!E3") & wSh.Range("'Lists Formulas'!E6")

instead results in an error asking for an object for the first .Range.

1
ZavraD53•

wSh is Sheet "Lists Formulas". I didn't look at that line when I copied it.

Try

FN = wSh.Range("E3") & wSh.Range("E6")

Or

With wSh
   FN = .Range("E3") & .Range("E6")
End With

Or even

With wSh.Columns(5)
   FN = .Cells(3) & .Cells(6)
End With

Ugly, but works

FN = wSh.Range("E3")
FN = FN & wSh.Range("E6")
1