[VBA Related] issue when Create a sub path & export a cell data to the sub path based on the folder name

[Daisy_PENG]

Hello everyone,

I am trying to use VBA to create & export some data of the Excel file to some shared profiles.
Below is what I have done so far:

Sub ExportAllSQL()


Dim wsData As Variant
Dim myFileName As String
Dim FN As Integer
Dim p As Integer, q As Integer
Dim path_parent As String, path_real As String
Dim myString As String
Dim company_name As String
Dim lastrow As Long, lastcolumn As Long
Dim weeknum As Variant
Dim company_abbr As String


lastrow = Worksheets("Source_Data").Range("A" & Rows.Count).End(xlUp).Row
lastcolumn = Worksheets("Source_Data").Cells(1, Columns.Count).End(xlToLeft).Column
weeknum = Format(Now(), "ww")
p = lastcolumn
'MsgBox myFileName
For q = 5 To lastrow
company_abbr = Left$(Worksheets("Source_Data").Cells(q, "A"), 5)
company_name = Application.WorksheetFunction.vlookup(company_abbr, Worksheets("Abbriviation").Range("A:B"), 2, False)
path_parent = "\\datasrv\devpt\Web\Bases\Emailings\Anciens mailings\2017" & company_name
MkDir path_parent & "\Week " & weeknum & "" >>> ERROR WHEN ACCESS TO THE PATH/FOLDER
wsData = ActiveSheet.Cells(q, "A").Value
myFileName = wsData
myFileName = myFileName & ".sql"
myFileName = path_parent & "\Week " & weeknum & "" & myFileName
myString = Cells(q, p)
FN = FreeFile
Open myFileName For Output As #FN
Print #FN, myString
Close #FN
Next q
myString = ""
End Sub


The error occurs in the step MkDir path_parent & "\Week " & weeknum & "" and the message shows as cannot find the path. However, by checking the location, it can be seen that the folder of week number under the company's folder is created.

For your information, before I added mkDir part(i.e. when I am using an exist & fixed path for the value of "path"), it works.

It would be perfect if you could kindly share some insight.

Thanks in advance!!!!

Daisy

 

[stumac]

If the folder exists then you will get an error if you try to create a folder with that name. try checking if it exists first:

If Dir(path_parent & "\Week " & weeknum & "" ,vbDirectory) = vbNullString Then
    MkDir (path_parent & "\Week " & weeknum & "")
End If

 

[Daisy_PENG]

It worked !! Thank you !!