VBA Module Program will not work

[sap1958]

I have the following program in a module. It is designed to pull excel data from a folder named Test into access. I click the F8 button to analyze it line by line. The yellow line advances line by line but nothing happens. I get not error message but the program is not running either

Option Compare Database

Function TransferMultiples()
Dim strDir As String, strFile As String
strDir = "C:\pcsas_export_files\Test\"
strFile = Dir(strDir & "*.xls")
While strFile <> ""
DoCmd.TransferSpreadsheet acImport, 10, "Unet", strDir & strFile, True, ""
strFile = Dir
Wend
End Function

 

[MazeWorX]

First off you can execute a function by typing ?TransferMultiples() or Call TransferMultiples() in the intermediate window also the Transferspreadsheet method requires the type to be specified as follows eg. acSpreadsheetTypeExcel8. 8 is a default that will be used if no type is specified your code has 10 this will produce an error. The function can also be called from a command button using Call TransferMultiples() in the OnClick event

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[SkipVought]

hi,

No DIR()!!!

strFile = strDir & "*.xls"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jedraw]

Try adjusting your function to include minimal error handling and validation.

Function TransferMultiples()
Dim strDir As String, strFile As String
On Error GoTo TransferMultiples_Error
Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

strDir = "C:\pcsas_export_files\Test\"

'
' see if that folder/Dir exists
If (fso.FolderExists(strDir)) Then
MsgBox strDir & " exists."
Else
MsgBox strDir & " doesn't exist."
Exit Function
End If

strFile = dir(strDir & "*.xls")

While strFile <> ""
DoCmd.TransferSpreadsheet acImport, 10, "Unet", strDir & strFile, True, ""
strFile = dir
Wend

On Error GoTo 0
Exit Function

TransferMultiples_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure TransferMultiples of Module Module3"
End Function

Good luck

 

[TheAceMan1]

How are ya sap1958 . . .

Your code and your logic flow look just fine.

In parallel with [blue]MazeWorX[/blue] I query the 2nd arguement, [blue]acSpreadsheetType[/blue]. There is no [red]10[/red] to my knowledge. The current highest is [blue]acSpreadsheetTypeExcel9[/blue] which is a constant of [blue]8[/blue]. So try [blue]8[/blue] and see what happens.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]