PDA

View Full Version : [SOLVED] Vba how to check workbook name first before opening it



baralus
03-09-2018, 09:52 PM
So i have this code that can open any workbook by opening the document browser, and i set a condition that if its not the right filename the process wont continue. But on my code i can only open the workbook first before checking if it has the right filename. Is their a way to check the filename first before opening it? i just want to make it look more dynamic by placing the process in order.


Sub myfile()
Dim wb As Workbook
Dim ws As Worksheet
Dim twb As Workbook




Set twb = ThisWorkbook


file = Application.GetOpenFilename("Excel Files (*.xlsm; *xlx; *xlsx), *.xlsm; *.xlx; *xlsx")




Set wb = Workbooks.Open(file)


If wb.Name = "Book1.xlsm" Then
For Each ws In wb.Sheets
ws.Columns(1).Copy
twb.Sheets("sheet6").Columns(1).PasteSpecial
wb.Close
Next
Else
MsgBox "Not the right Workbook"
wb.Close
End If


End Sub

werafa
03-10-2018, 12:59 AM
use 'dir'
myString is the file name and path to be tested



If Dir(myString) <> "" Then 'workbook name exists at location
Set dataWB = Workbooks.Open(fileName:=myString)
Else
myWB.Worksheets("Admin").Cells(myMonth + 6, 4).Value = "File Missing"
End If

baralus
03-10-2018, 10:06 AM
Thanks Sir, really great help