VBA: Using a variable as a Workbook name

[dha17]

Hi,

I currently use a variable to open a filename (which works fine):

Dim FileName As Variant
FileName = Application.GetOpenFilename()
Workbooks.Open filename:=FileName

I would like to use that same variable as the filename of a workbook, but it does not work:

Workbooks(FileName).Worksheets("Sheet1").Range("A:T").ClearContents

Any ideas of how to use this variable as the filename?

Denny

 

[neonangel]

Dim FilePath As String, FileName As String

FilePath = Application.GetOpenFilename()
FileName = Mid(FilePath, InStrRev(FilePath, "\") + 1)
Workbooks.Open FileName:=FilePath
Workbooks(FileName).Worksheets("Sheet1").Range("A1") = "hello"


Note you still need some error checking to ensure the person has actually selected a file and not simply closed the GetOpenFilename() dialog. Replace the "hello" bit with your clear contents code.

Regards,

Alex

 

[Zack Barresse]

FilePath = Application.GetOpenFilename()
If FilePath <> False Then
    'Is Acceptable
Else
    'Is NOT Acceptable - pressed Cancel
End If

 

[Juan Pablo González]

I prefer to use

If TypeName(FilePath) = "Boolean" Then
'Canceled
Else

End If

to avoid mismatch errors.

 

[Zack Barresse]

Hi Juan,

Can you give an example? I've been using that for some months mow without consequence. (If I need to switch, I'd like to do it sooner rather than later!)

I prefer to use

If TypeName(FilePath) = "Boolean" Then
'Canceled
Else

End If

to avoid mismatch errors.

 

[dha17]

I tried both methods and was unsuccessful. I could also use an example for Juan's method.

However, I was able to make it work by placing quotations around the word False:

If FilePath = "False" Then
MsgBox "Cancelled"
Exit Sub
End If

 

[Juan Pablo González]

Zack,

Try these 3 examples. The most "notorious" one is Test3.

Sub Test1()
   Dim Ans As String
   
   Ans = Application.GetOpenFilename
   If Ans = False Then
      MsgBox "Cancelled"
   End If
End Sub

Sub Test2()
   Dim Ans As Variant
   
   Ans = Application.GetOpenFilename
   If Ans = False Then
      MsgBox "Cancelled"
   End If
End Sub

Sub Test3()
   Dim Ans As Variant
   
   Ans = Application.GetOpenFilename(MultiSelect:=True)
   If Ans = False Then
      MsgBox "Cancelled"
   End If
End Sub

In all of them, try clicking on 'Cancel' or selecting a file (or various files, in Test3)

 

[Zack Barresse]

Thanks Juan!!

Always love a good lesson!!