Overwrite error saving file and folder in vba

bcselect

New Member
Joined
May 13, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
This is a section of code that creates a folder named based on user input. It creates a backup file based on same input and saves in folder. It then proceeds with the rest of the code. I need to open backup file, edit, then re-run macro and allow it to save but it errors where indicated below.

'Sub CreateFolder()
Dim rootDirectory As String
Dim folderToBeCreated As String
Dim path As String
' Set the root directory path
rootDirectory = "C:\"
' give a valid name for folder
folderToBeCreated = "Program"

path = rootDirectory & folderToBeCreated

If Len(Dir(rootDirectory, vbDirectory)) <> 0 Then 'check if RootDirectory Exists?
If Len(Dir(path, vbDirectory)) = 0 Then ' full path should not exist already
VBA.MkDir ("C:\Program")
MsgBox "Folder created successfully"
Else
MsgBox "Folder already exists"
End If
Else
MsgBox "Root directory does not exist"
End If


'Create Input Box Function for User input of name for naming backup file

'Sub TournSaveBox()

'declare variable to hold value returned by InputBox
Dim myInputBoxVariable As String

Dim subjectVariable As String
subjectVariable = " ones input pre-Reg A"

'(1) create InputBox, and (2) assign value returned by InputBox function to variable
myInputBoxVariable = InputBox(Prompt:="Naming backup file", Title:="Saving ones ", Default:="Enter Name")

'display message box with value held by variable
MsgBox "name: " & myInputBoxVariable

VBA.MkDir ("C:\Program\") & myInputBoxVariable & "" **** ERRORS HERE *******************************************************

MsgBox "Folder created successfully"


ActiveWorkbook.SaveAs Filename:="C:\Program\" & myInputBoxVariable & "\" & myInputBoxVariable & subjectVariable & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

'ActiveWindow.SmallScroll Down:=-15
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,277
Office Version
  1. 2010
Platform
  1. Windows
According to VBA help - a must read ! - try MkDir "C:\Program\" & myInputBoxVariable …​
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,277
Office Version
  1. 2010
Platform
  1. Windows
So bad content in the variable …​
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,277
Office Version
  1. 2010
Platform
  1. Windows
When 'ok' no error occurs …​
 

Forum statistics

Threads
1,141,770
Messages
5,708,432
Members
421,568
Latest member
Huxley

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
Top