Macro skips TransferText after a condition

[rlatham]

Hello,

In my macro I have a condition that if a file does not exists, then display a message and stop the macro... this works..

If the file exists, then:
1) display a message that the file was found,
2) ask for today's date,
3) import the file, and
4) notify the user when the import is done.

Steps 1,2,4 happen, but the import (3) does not.

I know the attributes for the TransferText are correct. I tried it successfully before putting the "file exists" condition.

Could anyone help me with this?

Thanks

rl

 

[AlexCuse]

I think you want to convert your macro to code and add an If then, else piece to it in order to determine if code is run or not.

Hope this helps,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[rlatham]

Hello AlexCuse,

I just put the TransferText into a function with a message after it: "function, import is done".
Then called the function from the macro.

I got the message from the function... but the table was still is empty.

Need more suggestions please.

Thanks,
rl

 

[AlexCuse]

Did you add the If, Then, Else language around it?

Can you post the function you are running?

Thanks,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[rlatham]

Function File_DisconnectLetters()

  If Dir("c:\F1\Import\disconnect.txt") = "" Then
                
    If MsgBox("File was not located.", vbOKOnly, "File Not Found") = vbAbort Then
      Quit
    End If
           
' if the file exists
  Else
  
    MsgBox "File Found. Begin import.", vbOKOnly
    
    DoCmd.TransferText acImportDelim, "Disconnect Letters Import Specification", "tblDisconnectLetters", "c:\F1\Import\disconnect.txt", yes
    
    MsgBox "Function: Import is Complete", vbOKOnly
    
 
    DoCmd.OpenReport rpt_DisconnectLetters, acViewPreview

    

  End If

    
End Function

 

[AlexCuse]

I think the problem is in your first if statement. The directory you specify will never equal " ".

I would use file system object like this:

Function File_DisconnectLetters()

dim fs, f, f1, fc  
dim folderspec as string

folderspec = "c:\F1\Import"
     
Set fs = CreateObject("Scripting.FileSystemObject")
    
Set f = fs.GetFolder(folderspec)         'Specify Folder
Set fc = f.Files

For each f1 in fc

If f1.name = "disconnect.txt" then

MsgBox "File Found. Begin import.", vbOKOnly
    
DoCmd.TransferText acImportDelim, "Disconnect Letters Import Specification", "tblDisconnectLetters", "c:\F1\Import\disconnect.txt", yes
    
MsgBox "Function: Import is Complete", vbOKOnly
    
 
DoCmd.OpenReport rpt_DisconnectLetters, acViewPreview
 
Else

MsgBox("File was not located.", vbOKOnly, "File Not Found") 

DoCmd.Quit

End If

End Function

Let me know if this works for you.

Thanks,

Alex




Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[rlatham]

The FOR causes to loop through the folder and since there is only 1 disconnect.txt file... it shows the "file not found" message unnecessarily.

Still, the TransferText did not work.

=(

 

[rlatham]

Hi Alex,

Thanks for your help!

My code was not wrong.. it was the file.

Have a great week!

rl

 

[AlexCuse]

Glad you got it to work. You do the same

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.