If then else, not sure what the else should be 1

[khosseini]

I am trying to run an "IF-THEN-ELSE" statement, that will either pick up the workbook or if it doesn't exist move on to the next workbook, here is the start I have but I am not sure what the ELSE command I need.

If "C:\GROUP1" = True Then
DoCmd.TransferSpreadsheet acImport, , "Students", "C:\GROUP1", True, "Students1!A1:I23"
DoCmd.TransferSpreadsheet acImport, , "FA03", "C:\GROUP1", True, "FA03!A123"
DoCmd.TransferSpreadsheet acImport, , "SP04", "C:\GROUP1", True, "SP04!A123"
DoCmd.TransferSpreadsheet acImport, , "FA04", "C:\GROUP1", True, "FA04!A123"
DoCmd.TransferSpreadsheet acImport, , "SP05", "C:\GROUP1", True, "SP05!A123"
Else

End If


THANKS!

 

[IGPCS]

what do you want the else to do if nothing then you dont need a else just if and end if but if you have a code after the if and it only needs to run if the if is true then type there exit sub

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[khosseini]

This is the first of 10 Groups I need to cycle through, so if the file doesn't exist then I can just put End If? Or End Sub and start Group 2 as a new Sub? Ideally at the end it will say "import complete" if it downloaded at least 1 group and "nothing to import" to notify the user that there were no files to import. I am reaching back to my undergrad classes and dusting off my vba, thanks for your patience and help!

 

[IGPCS]

or just continue with else if ....... =true then
your code
else if..... = true then
and by the end of all end if

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[khosseini]

Thanks that helps so much!

 

[IGPCS]

your welcome

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[JoeAtWork]

If "C:\GROUP1" = True Then

I think you will get a type mismatch error from that code, as strings by themselves do not evaluate to True or False.

Also, I think if your workbooks have a predictable naming pattern (e.g. GROUP1, GROUP2, GROUP3,....), you can avoid having 10 different subroutines and condense it to one.

    Dim strPath As String
    Dim intGroup As Integer
    
    For intGroup = 1 To 10
        strPath = "C:\GROUP" & intGroup & ".XLS"
        
        If Dir(strPath) <> "" Then
           DoCmd.TransferSpreadsheet acImport, , "Students", strPath, True, "Students1!A1:I23"
           DoCmd.TransferSpreadsheet acImport, , "FA03", strPath, True, "FA03!A1:D23"
           DoCmd.TransferSpreadsheet acImport, , "SP04", strPath, True, "SP04!A1:D23"
           DoCmd.TransferSpreadsheet acImport, , "FA04", strPath, True, "FA04!A1:D23"
           DoCmd.TransferSpreadsheet acImport, , "SP05", strPath, True, "SP05!A1:D23"

        End If
    Next intGroup

 

[khosseini]

Thanks that is exactly the problem I am having with the mismatch. However each of the workbooks 1-10 discuss different terms for each graduating group of nursing students so group 2 will have different spreadsheets than group4. For example here is sample code including two groups:

If "C:\GROUP2" = True Then
DoCmd.TransferSpreadsheet acImport, , "Students", "C:\GROUP2", True, "Students2!A1:I23"
DoCmd.TransferSpreadsheet acImport, , "FA04", "C:\GROUP2", True, "FA04!A123"
DoCmd.TransferSpreadsheet acImport, , "SP05", "C:\GROUP2", True, "SP05!A123"
DoCmd.TransferSpreadsheet acImport, , "FA05", "C:\GROUP2", True, "FA05!A123"
DoCmd.TransferSpreadsheet acImport, , "SP06", "C:\GROUP2", True, "SP06!A123"
End If

If "C:\GROUP3" = True Then
DoCmd.TransferSpreadsheet acImport, , "Students", "C:\GROUP3", True, "Students3!A1:I23"
DoCmd.TransferSpreadsheet acImport, , "FA05", "C:\GROUP3", True, "FA05!A123"
DoCmd.TransferSpreadsheet acImport, , "SP06", "C:\GROUP3", True, "SP06!A123"
DoCmd.TransferSpreadsheet acImport, , "FA06", "C:\GROUP3", True, "FA06!A123"
DoCmd.TransferSpreadsheet acImport, , "SP07", "C:\GROUP3", True, "SP07!A123"
End If

 

[RickBerem]

Hello,

Joe pointed you to the good solution.

you should be doing

If Dir("C:\Group2.xls") <> "" Then
   DoCmd.TransfertSpreadsheet ... (specific to your Group)
End If

For each 'group' and then you can define specifics for each.

Hope it helps you a little.

SG

 

[khosseini]

Thanks so much you guys are lifesavers. I am not getting compile errors with that code now I am getting an error with this:

Private Sub cmdImport_Click()

which wasn't throwing an error before. I can't wait until this becomes a bit more natural to me!