Can't Create VBA Directory/Folder on Desktop: Pulling Data from Multiple Sheets - Everything Debugs, No Issues, Confused?

In this article, we will discuss a common issue that arises when trying to create a VBA directory or folder in the Desktop, despite the code running without any errors or issues. We will cover the key concepts and provide solutions to this problem.

Prerequisites

Before we dive into the issue, let's make sure we have the following prerequisites:

• A basic understanding of VBA (Visual Basic for Applications)
• Microsoft Excel installed on your computer

The Issue: Trying to Create a Directory in Desktop using VBA

Consider the following VBA code snippet:

 Sub SubWO_SaveUpdate()
 Dim WORowLong As Long, WOColLong
 Dim AssignedToString As String, SharedFolderString, FileNameString, FilePathString

 SharedFolderString = "C:\Users\Username\Desktop\SharedFolder\"
 FileNameString = "FileToBeSaved.xlsx"
 Set FilePathString = CreateObject("WScript.Shell")

 If Dir(SharedFolderString, vbDirectory) = "" Then
 MkDir SharedFolderString
 End If

 If Dir(SharedFolderString & FileNameString, vbTextFile) = "" Then
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set objFile = objFSO.CreateTextFile(SharedFolderString & FileNameString, True)
 objFile.Write "Some text to be written in the file"
 objFile.Close
 End If

 ' Your code here

 Set objFile = Nothing
 Set objFSO = Nothing
 Set FilePathString = Nothing
 End Sub
 
 

The code above attempts to create a new directory named "SharedFolder" in the Desktop and then creates a new file within that directory.

The Problem: Creating a Directory in Desktop using VBA

The problem arises when trying to create a directory in the Desktop using VBA. By default, VBA does not have the necessary permissions to create or modify directories in the Desktop or other protected folders. To work around this, you need to run your VBA code as an administrator.

Solution: Running VBA Code as an Administrator

To run your VBA code as an administrator, follow these steps:

1. Press the "Windows key + R" to open the Run dialog box.
2. Type "excelfile:.xlsm" (replace with the name of your Excel file) and press Enter.
3. A dialog box will appear asking for permission to run the file as an administrator. Click "Yes" to proceed.
4. Once Excel opens, press "Alt + F11" to open the Visual Basic for Applications (VBA) editor.
5. Run your VBA code as usual.

Pulling Data from Multiple Sheets

Now that we have solved the issue of creating a directory in the Desktop using VBA, let's move on to the next part of the article: pulling data from multiple sheets.

Code Snippet: Pulling Data from Multiple Sheets

Consider the following code snippet:

 Sub GetDataFromMultipleSheets()
 Dim SourceSheet As Worksheet, DestinationSheet As Worksheet, i As Long

 Set SourceSheet = ThisWorkbook.Sheets("Sheet1")
 Set DestinationSheet = ThisWorkbook.Sheets("Sheet2")

 For i = 1 To SourceSheet.UsedRange.Rows.Count
 DestinationSheet.Cells(i, 1).Value = SourceSheet.Cells(i, 1).Value
 Next i

 Set SourceSheet = Nothing
 Set DestinationSheet = Nothing
 End Sub
 
 

The code above copies the data from the first column of Sheet1 to the first column of Sheet2.

In this article, we discussed the issue of creating a directory in the Desktop using VBA and provided a solution to this problem. We also covered how to pull data from multiple sheets using VBA.

References

• MkDir Method (Excel)
• Running VBA Macros as an Administrator