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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,157
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,157
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,157
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,084
Messages
5,545,870
Members
410,711
Latest member
Josh324
Top