Compile Error - type Mismatch

[Locopete99]

Hi Guys,

I'm having a compile error message come up for the below code.

The code is good, and has worked for many months, but I've come to build a mark 2 of my database to slim it down after months of changes and improvements.

The code has now stopped working

Private Sub Label33_Click()
Dim Location As String

Dim DirName As String
Dim Response As String



DirName = "B:\" & fOSUserName & "\EMC Export\"


StrPath = DirName & "NPPR EMC BLANK.xls"




If Dir(DirName, vbDirectory) = "" Then
MkDir DirName
Else
End If

Dim FSO
Dim sFile As String
Dim sSFolder As String
Dim sDFolder As String
sFile = "NPPR EMC BLANK.xls"
sSFolder = "P:\CCT Hub\NPPR - CCT\"
sDFolder = StrPath
Set FSO = CreateObject("Scripting.FileSystemObject")
If Not FSO.FileExists(sSFolder & sFile) Then
MsgBox "Specified File Not Found", vbInformation, "Not Found"
ElseIf Not FSO.FileExists(sDFolder & sFile) Then
FSO.CopyFile (sSFolder & sFile), sDFolder, True
End If


DoCmd.TransferSpreadsheet acExport, 9, "Qry Template Export", StrPath, True, "export"

End Sub

The strange thing is, its the Private Sub Label33_Click() that is being highlighted as the error??

 

[MarkK]

If I copy this code into my VBA IDE, I get a 'variable not defined' compile error for 'fOSUserName' at this line...

DirName = "B:\" & fOSUserName & "\EMC Export\"

If I declare that variable, I then get the same error for 'StrPath' at this line...

StrPath = DirName & "NPPR EMC BLANK.xls"

If I resolve those two problems I cannot cause a type mismatch using your code as posted.
hth
Mark

 

[arnelgp]

one thing i noticed is the way you make directory:
you have to start from root folder down
to the sub sub folder when creating it:

If Dir(DirName, vbDirectory) = "" Then
subMkDir DirName
Else
End If
...
...

public sub subMkDir(Path as string)
dim newpath as string
dim arrPath, var
on error resume next
arrpath=split(path, "\")
for each var in arrpath
newpath= newpath & replace(var & "\", "\\", "\")
mkdir newpath
next var
end sub