CODEWITHSUNDEEP
vba
chandra sekhar and associates
chandra sekhar and associates

Reputation: 3

vba need to create new workbook at a specific location with a specific name but my code is showing errors

I am trying to add a excel workbook in a specific workbook with a specific name through vba but my code is showing errors Here is my code

Private Sub CREATE_WORKBOOK()
activepath = ThisWorkbook.Path
MkDir activepath & "\Reports"
Dim seldir As String
seldir = Dir(activepath & "\Reports\Srikakulam.xlsx")
If seldir = "" Then
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename = activepath & "\Reports\Srikakulam.xlsx"
Else
End If
Set booktoopen = Workbooks.Open(activepath & "\Reports\Srikakulam.xlsx")
Application.DisplayAlerts = False
ThisWorkbook.Sheets(C_Code.Text).Copy Before:=booktoopen.Sheets("Sheet1")
booktoopen.Close savechanges:=True
Application.DisplayAlerts = True
End Sub

Upvotes: 0

Views: 244

Answers (1)

Bharat
Bharat

Reputation: 1205

The reason it was not working is because few errors. The code that is trying to save the file here the syntax was incorrect

ActiveWorkbook.SaveAs Filename = activepath & "\Reports\Srikakulam.xlsx"

Here the correct syntax is

ActiveWorkbook.SaveAs  Filename:= activepath & "\Reports\Srikakulam.xlsx"

In your case "Filename = activepath" would evaluate as boolen expression and result would false and the file would be created with FALSE as name. And later when the below codes gets executed it would result is file not found exception as the code would look for file "\Reports\Srikakulam.xlsx". There were other issues, such as the check was wrong, check for directory if exists was not there etc.

Private Sub CREATE_WORKBOOK()
    Dim directoryName As String
    Dim isDirectoryExists As String
    Dim fileName As String
    Dim isFileExists As String
    
    activepath = ThisWorkbook.Path
    
    directoryName = activepath & "\Reports\"
    
    isDirectoryExists = Dir(directoryName, vbDirectory)
    
    If isDirectoryExists = "" Then
        MkDir directoryName
    End If
    
    fileName = directoryName & "Srikakulam.xlsx"
    
    isFileExists = Dir(fileName)
    
    If isFileExists = "" Then
        Workbooks.Add
        ActiveWorkbook.SaveAs fileName
    End If
    
    Set booktoopen = Workbooks.Open(fileName)
    Application.DisplayAlerts = False
    
    'Below line I have commented as C_Code.Text is not there
    'ThisWorkbook.Sheets(C_Code.Text).Copy Before:=booktoopen.Sheets("Sheet1")
    booktoopen.Close savechanges:=True
    Application.DisplayAlerts = True
End Sub

Upvotes: 1

Related Questions