Not getting exact result while creating directory in vba

Gautham A

Board Regular
Joined
May 25, 2020
Messages
107
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Can you copy paste your exact code, when doing so please use code tags, the <vba/> icon in the reply window.
 
Upvote 0
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
 
Upvote 0
Thanks for that, the problem is the folder you check for, is not the folder you create.
Therefore that folder never exists.
 
Upvote 0
Yes. It is a silly mistake. Thank you so much fluff for correcting me.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top