Close a particular Workbook.

[tony.reynolds]

I have a form that the user browses to a workbook and imports all data from columns and then once the workbook that was opend is finished with it closes.

however if the user closes the userform with the red X id like the workbook that was opened to close.

I want to exclude the workbook that we are importing to from closing and make sure we only close the import one.

Hence i have the string of the pathname and workbook.name in the TextBoxImportDirectory textbox. ("C/Users/TonyXX/Documents/ImportFile.xlsx")

I am trying to remove all the characters before the file name string and also the extension of the file (all after the "." from the string "C/Users/TonyXX/Documents/ImportFile.xlsx"

Can someone help me with code to remove these characters from the sting.

I have placed the code in the userform_Queryclose Event

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim WorkbookToCloseString

WorkbookToCloseString = Replace(TextBoxActiveFileName, ".xlsx", "")
Workbooks(WorkbookToClose).Close SaveChanges:=False

End Sub

the above Code removes the extension but not all before the file name i.e. "C/Users/TonyXX/Documents/" and also if it was not identical extension it wont remove it.

there are probably other ways to do it but any ideas a welcome.

 

[Norie]

You can get the name using InStrRev.

WorkbookToCloseString = Mid(TextBoxActiveFileName, InStrRev(TextBoxActiveFilename, "/")+1

If you are looking for another way of dealing with this why not create a reference to the workbook you open?

Then you can use that when you want to close the workbook.

 

[tony.reynolds]

Thanks for your help in my question.... now relating to the suggestion you had

I need to understand how to reference a workbook..

the difficulty i had was i needed to refer to it in another procedure and if I simply declared that

Dim WorkbooktoClose = ActiveWorkbook

in the procedure when the work book opened then later i could not refer to

Workbooks(WorkbooktoClose).Close SaveChanges:=False

Actually probably very elementry but im learning every day...

How do you make a statment that all procedures can refer to?

 

[Norie]

You would need to declare the variable outside any other sub as public and this wouldn't work.

Dim WorkbooktoClose = ActiveWorkbook

It should be something like this.

Dim WorkbooktoClose As Workbook
...
Set WorkbooktoClose = ActiveWorkbook
...
WorkbooktoClose.Close SaveChanges:=False