Not getting exact result while creating directory in vba

Gautham A

Board Regular
Joined
May 25, 2020
Messages
89
Office Version
  1. 2016
Platform
  1. Windows
Im writing a VBA code to check if a folder exists and if the folder exists just paste the Excel sheet. If the folder doesn't exist create a new folder in run time and save a sheet in that folder.

Code:
If(Len(Dir("C:\Users\Acer\Desktop\Report",vbdirectory))=0 Then
MkDir "C:\Users\Acer\Desktop\Report"
Activeworkbook.saveas filename:="C:\Users\Acer\Desktop\Report\file.xlsx", File format:=51
Else
filename:="C:\Users\Acer\Desktop\Report\file.xlsx", File format:=51
End If

The code works fine for the first time to create a new folder and save the Excel sheet. But on other instances it is not saving the file in the path.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,258
Office Version
  1. 365
Platform
  1. Windows
Can you copy paste your exact code, when doing so please use code tags, the <vba/> icon in the reply window.
 

Gautham A

Board Regular
Joined
May 25, 2020
Messages
89
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub Test()
ThisWorkbook.Activate
Dim wbo As Workbook, wbn As Workbook
Dim dtdate As Date
Dim sdate As Variant
Dim folder As String
If Weekday(Now) = vbMonday Then
dtdate = DateValue(Now) - 3
Else
dtdate = DateValue(Now) - 1
End If
sdate = Format(dtdate, "ddmmyyyy")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Set wbo = ActiveWorkbook
Set wbn = Workbooks.Add

wbo.Sheets("Data").Copy wbn.Sheets(1)

wbn.Sheets("Sheet1").Delete
'wbn.Sheets("Raised").Activate

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Set wbn = ActiveWorkbook
Application.DisplayAlerts = False
If Len(Dir("C:\Users\" & Environ("Username") & "\Desktop\EOD Extract Report", vbDirectory)) = 0 Then
'On Error Resume Next
MkDir "C:\Users\" & Environ("Username") & "\Desktop\EOD Extract Job Report"
ActiveWorkbook.SaveAs Filename:="C:\Users\" & Environ("Username") & "\Desktop\EOD Extract Job Report\EOD Extract Job Tracker " & sdate & ".xlsx", FileFormat:=51
End If
ThisWorkbook.Activate
Application.DisplayAlerts = True

End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,258
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, the problem is the folder you check for, is not the folder you create.
Therefore that folder never exists.
 

Gautham A

Board Regular
Joined
May 25, 2020
Messages
89
Office Version
  1. 2016
Platform
  1. Windows
Yes. It is a silly mistake. Thank you so much fluff for correcting me.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,258
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,280
Messages
5,546,948
Members
410,764
Latest member
Dedeke
Top