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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,718
Perhaps it is saving the file ... but it is overwriting the previous file ?
 

Gautham A

Board Regular
Joined
May 25, 2020
Messages
89
Office Version
  1. 2016
Platform
  1. Windows
I deleted all the files in that folder and also tried it. But still I'm not able to save again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,255
Office Version
  1. 365
Platform
  1. Windows
You aren't saving the file inside the else, it should be
VBA Code:
Else
ActiveWorkbook.Saveas filename:="C:\Users\Acer\Desktop\Report\file.xlsx", File format:=51
End If
 

Gautham A

Board Regular
Joined
May 25, 2020
Messages
89
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Apologies fluff this is a typing mistake by me here. Actually I saved it inside the else as well. But every time the loop doesn't come to the else part. The if condition becomes true even if the folder exists already
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,255
Office Version
  1. 365
Platform
  1. Windows
If the folder exists & it still went to create the folder you would get an error.
 

Gautham A

Board Regular
Joined
May 25, 2020
Messages
89
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

How should I write code for this?
If the folder exists just save the Excel file in there and if the folder doesn't exist create a folder and save the file in the created folder
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,255
Office Version
  1. 365
Platform
  1. Windows
That's what your code does.
 

Gautham A

Board Regular
Joined
May 25, 2020
Messages
89
Office Version
  1. 2016
Platform
  1. Windows
Let us say the folder exists, then if part of the code should become false, but Everytime it becomes true. Any reason?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,255
Office Version
  1. 365
Platform
  1. Windows
Let us say the folder exists, then if part of the code should become false
That's correct & it's what happens when I run your code.
If the folder exists do you get any error messages?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,276
Messages
5,546,928
Members
410,763
Latest member
TSVIVI
Top