Wits end trying to get VBA code to work

[Locopete99]

Hi,

Can someone help me. I cant get this code to work!

Literally all i want it to do is run different export queries based on what area the person running it is from.

Can anyone see where the glaring issues are that are stopping this from running.

Dim Holon1 As String
Dim Location As String
Dim DirName As String
Dim Response As String
DirName = "B:\" & fOSUserName & "\Register Report\Outstanding Emails\"
If Dir(DirName, vbDirectory) = "" Then
MkDir DirName
Else
End If

Holon1 = DLookup("Holon", "tbl_Holonsecurity", "[User]= '" & fOSUserName() & "'")

If Holon1 = "AA" Then
file = "AA.xlsx"
strpath = DirName & file
DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAA", strpath, True
Else

If Holon1 = "AB" Then
file = "AB.xlsx"
strpath = DirName & file
DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAB", strpath, True
Else

If Holon1 = "AC" Then
file = "AC.xlsx"
strpath = DirName & file
DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAC", strpath, True
Else

If Holon1 = "CDT" Then
file = "CDT.xlsx"
strpath = DirName & file
DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonCDT", strpath, True
Else

If Holon1 = "A0" Then
file = "AA.xlsx"
file2 = "AB.xlsx"
file3 = "AC.xlsx"
file4 = "CDT.xlsx"
strpath = DirName & file
strpath2 = DirName & file2
strpath3 = DirName & file3
strpath4 = DirName & file4
DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAA", strpath, True
DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAB", strpath2, True
DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAC", strpath3, True
DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonCDT", strpath4, True
Else
End If
End If
End If
End If
End If

 

[Gasman]

First question would be, what is it not doing?
Why not walk through the code with F8 ?

Sub Nothing1()


    Dim Holon1 As String
    Dim Location As String
    Dim DirName As String
    Dim Response As String
    DirName = "B:" & fOSUserName & "\Register Report\Outstanding Emails"
    If Dir(DirName, vbDirectory) = "" Then
        MkDir DirName
    Else
    End If

    Holon1 = DLookup("Holon", "tbl_Holonsecurity", "[User]= '" & fOSUserName() & "'")

    If Holon1 = "AA" Then
        File = "AA.xlsx"
        strPath = DirName & File
        DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAA", strPath, True
    Else

        If Holon1 = "AB" Then
            File = "AB.xlsx"
            strPath = DirName & File
            DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAB", strPath, True
        Else

            If Holon1 = "AC" Then
                File = "AC.xlsx"
                strPath = DirName & File
                DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAC", strPath, True
            Else

                If Holon1 = "CDT" Then
                    File = "CDT.xlsx"
                    strPath = DirName & File
                    DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonCDT", strPath, True
                Else

                    If Holon1 = "A0" Then
                        File = "AA.xlsx"
                        file2 = "AB.xlsx"
                        file3 = "AC.xlsx"
                        file4 = "CDT.xlsx"
                        strPath = DirName & File
                        strpath2 = DirName & file2
                        strpath3 = DirName & file3
                        strpath4 = DirName & file4
                        DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAA", strPath, True
                        DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAB", strpath2, True
                        DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonAC", strpath3, True
                        DoCmd.TransferSpreadsheet acExport, 9, "Qry_OutHolonCDT", strpath4, True
                    Else
                    End If
                End If
            End If
        End If
    End If

End Sub

 

[pbaldy]

What exactly goes wrong? Where is the code (what event)? Have you set a breakpoint to see if the correct value is pulled with the DLookup?

 

[Locopete99]

Hi Gasman,
Its not doing anything!

Its supposed to create a directory and then run an export.

plus my f8 has never done anything, so that'd help if i knew how to fix that too

 

[Locopete99]

Pbaldy,

I've used a msgbox to confirm that its pulling back the right data, but then its not running any of the exports or creating the directory.

 

[pbaldy]

Do you really have a "B" drive? That would be pretty unusual in my experience. More here on debugging:

http://www.baldyweb.com/Debugging.htm

 

[Gasman]

Well the dirname will probably need to be

 "B:\" to start with?

Site removes backslash when not in code tags

I'm not sure you can create sub folders if the parent is not created.?

You need to set a breakpoint before you can use F8 ?