Good day Folks,
I'm hoping someone can help me here, I have two code modules that are constructed exactly the same with the only exception that they are picking up data from seperate text files in seperate folders.
Please also note that I do know that they are both using the same Import specification and that is because they are both of the exact same import requirement and therefore there was no point in having seperate import specifications, the import specification isn't the problem though, but I thought I would add this information just in case someone thinks that might be the cause.
One module runs in the automated process complete and does as required, whereas the other module only runs in part and doesn't complete the final steps, here is the two code modules firstly the one that runs okay and the the one that doesn't complete:
And this is code module that doesn't complete:
The part of the code that doesn't complete is the bit where it exports the data into and excel file.
If I run this code manually i.e. I open the module and click on the "run sub" button, the code runs fine and completes the creation of the excel file.
However when it is ran as part of an automated process it doesn't create the excel file, that is the Glowworm function, the Vaillant function works every time.
I've tried debugging but no error messages are generated when the code is ran.
Here is the automated process that both these code modules are called from:
It is when this code is run and when both code modules are called that the Glowworm module doesn't complete the exporting of the data into an excel spreadsheet format.
Even when I swap the call of the code modules around or place the Glowworm Module call after the Call for the Vaillanat Module the same problem occurs.
If there is anyone out there that can point me in the right direction or even knows why this is happening and how to resolve it, I would most appreciate your responses.
Regards
John Lee
I'm hoping someone can help me here, I have two code modules that are constructed exactly the same with the only exception that they are picking up data from seperate text files in seperate folders.
Please also note that I do know that they are both using the same Import specification and that is because they are both of the exact same import requirement and therefore there was no point in having seperate import specifications, the import specification isn't the problem though, but I thought I would add this information just in case someone thinks that might be the cause.
One module runs in the automated process complete and does as required, whereas the other module only runs in part and doesn't complete the final steps, here is the two code modules firstly the one that runs okay and the the one that doesn't complete:
Code:
[FONT=Calibri]Function VaillantProcess()[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]'=============================================[/FONT]
[FONT=Calibri]'References:[/FONT]
[FONT=Calibri]'Visual Basic For Applications[/FONT]
[FONT=Calibri]'Microsoft Access 9.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft DAO 3.6 Object Library[/FONT]
[FONT=Calibri]'Microsoft Excell 11.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft Outlook 14.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft Word 14.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft Scripting Runtime[/FONT]
[FONT=Calibri]'OLE Automation[/FONT]
[FONT=Calibri]'Microsoft ActiveX Date Objects 2.1 Library[/FONT]
[FONT=Calibri]'Microsoft Outlook View Control[/FONT]
[FONT=Calibri]'=============================================[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] DoCmd.Echo False, "Running Program - mod_VaillantProcess" [/FONT]
[FONT=Calibri] DoCmd.Hourglass True [/FONT]
[FONT=Calibri] DoCmd.SetWarnings False [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Dim FS As FileSystemObject [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Set FS = CreateObject("Scripting.FileSystemObject") [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] If FS.FileExists("B:\vaillant.fof\vaillant.txt") = True Then [/FONT]
[FONT=Calibri] DoCmd.TransferText acImportFixed, "VaillantImportSpec", "tblVaillantImport", "B:\vaillant.fof\vaillant.txt", False, "" [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Kill "B:\vaillant.fof\vaillant.txt" [/FONT]
[FONT=Calibri] DoEvents [/FONT]
[FONT=Calibri] DoCmd.Echo True, "Importing and Deleteing Vaillant Text File: " & "Vaillant.txt" [/FONT]
[FONT=Calibri] End If[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Dim Vaillant As Integer [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] VaillantCur = DCount("*", "tblVaillantImport")[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] If VaillantCur > 0 Then[/FONT]
[FONT=Calibri] DoCmd.OpenQuery "qryApptblVaillantImportToExport", acNormal, acEdit[/FONT]
[FONT=Calibri] DoCmd.OpenQuery "qryDeltblVaillantImport", acNormal, acEdit[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] DoCmd.TransferSpreadsheet acExport, 8, "tblVaillantExport", "G:\Scan - Verify\eFlow\VaillantRetailerTextFiles\Vaillant" & "_" & Format(Date, "ddmmyy") & ".xls", False, ""[/FONT]
[FONT=Calibri] DoCmd.TransferSpreadsheet acExport, 8, "tblVaillantExport", "G:\Scan - Verify\eFlow\BackupProdRegOnprdfs01\Vaillant.fof\After\Vaillant" & "_" & Format(Date, "ddmmyy") & ".xls", False, ""[/FONT]
[FONT=Calibri] End If[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] DoCmd.OpenQuery "qryDeltblVaillantExport", acNormal, acEdit[/FONT]
[FONT=Calibri] DoEvents[/FONT]
[FONT=Calibri] DoCmd.Echo True, "Deleting the contents of the tblVaillantExport table"[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] DoCmd.Echo True, "Program End" [/FONT]
[FONT=Calibri] DoCmd.Hourglass False [/FONT]
[FONT=Calibri] DoCmd.SetWarnings True [/FONT]
[FONT=Calibri]End Function[/FONT]And this is code module that doesn't complete:
Code:
[FONT=Calibri]Function GlowwormProcess()[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]'=============================================[/FONT]
[FONT=Calibri]'References:[/FONT]
[FONT=Calibri]'Visual Basic For Applications[/FONT]
[FONT=Calibri]'Microsoft Access 9.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft DAO 3.6 Object Library[/FONT]
[FONT=Calibri]'Microsoft Excell 11.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft Outlook 14.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft Word 14.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft Scripting Runtime[/FONT]
[FONT=Calibri]'OLE Automation[/FONT]
[FONT=Calibri]'Microsoft ActiveX Date Objects 2.1 Library[/FONT]
[FONT=Calibri]'Microsoft Outlook View Control[/FONT]
[FONT=Calibri]'=============================================[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] DoCmd.Echo False, "Running Program - mod_VaillantProcess" [/FONT]
[FONT=Calibri] DoCmd.Hourglass True [/FONT]
[FONT=Calibri] DoCmd.SetWarnings False [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Dim FS As FileSystemObject [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Set FS = CreateObject("Scripting.FileSystemObject") [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] If FS.FileExists("B:\glowworm.fof\glowworm.txt") = True Then [/FONT]
[FONT=Calibri] DoCmd.TransferText acImportFixed, "VaillantRegImportSpec", "tblGlowwormImport", "B:\glowworm.fof\glowworm.txt", False, "" [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Kill "B:\glowworm.fof\glowworm.txt" [/FONT]
[FONT=Calibri] DoEvents [/FONT]
[FONT=Calibri] DoCmd.Echo True, "Importing and Deleteing glowworm Text File: " & "glowworm.txt" [/FONT]
[FONT=Calibri] End If[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Dim GlowwormCur As Integer [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] GlowwormCur = DCount("*", "tblGlowwormImport")[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] If GlowwormCur > 0 Then[/FONT]
[FONT=Calibri] DoCmd.OpenQuery "qryApptblGlowwormImportToExport", acNormal, acEdit[/FONT]
[FONT=Calibri] DoCmd.OpenQuery "qryDeltblGlowwormImport", acNormal, acEdit[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] DoCmd.TransferSpreadsheet acExport, 8, "tblGlowwormExport", "G:\Scan - Verify\eFlow\VaillantRetailerTextFiles\Glowworm" & "_" & Format(Date, "ddmmyy") & ".xls", False, ""[/FONT]
[FONT=Calibri] DoCmd.TransferSpreadsheet acExport, 8, "tblGlowwormExport", "G:\Scan - Verify\eFlow\BackupProdRegOnprdfs01\glowworm.fof\After\Glowworm" & "_" & Format(Date, "ddmmyy") & ".xls", False, ""[/FONT]
[FONT=Calibri] End If[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] DoCmd.OpenQuery "qryDeltblGlowwormExport", acNormal, acEdit[/FONT]
[FONT=Calibri] DoEvents[/FONT]
[FONT=Calibri] DoCmd.Echo True, "Deleting the contents of the tblGlowwormExport table"[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] DoCmd.Echo True, "Program End" [/FONT]
[FONT=Calibri] DoCmd.Hourglass False [/FONT]
[FONT=Calibri] DoCmd.SetWarnings True [/FONT]
[FONT=Calibri]End Function[/FONT]The part of the code that doesn't complete is the bit where it exports the data into and excel file.
If I run this code manually i.e. I open the module and click on the "run sub" button, the code runs fine and completes the creation of the excel file.
However when it is ran as part of an automated process it doesn't create the excel file, that is the Glowworm function, the Vaillant function works every time.
I've tried debugging but no error messages are generated when the code is ran.
Here is the automated process that both these code modules are called from:
Code:
[FONT=Calibri]Function eFlowProcess3()[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]'=============================================[/FONT]
[FONT=Calibri]'References:[/FONT]
[FONT=Calibri]'Visual Basic For Applications[/FONT]
[FONT=Calibri]'Microsoft Access 9.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft DAO 3.6 Object Library[/FONT]
[FONT=Calibri]'Microsoft Excell 11.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft Outlook 14.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft Word 14.0 Object Library[/FONT]
[FONT=Calibri]'Microsoft Scripting Runtime[/FONT]
[FONT=Calibri]'OLE Automation[/FONT]
[FONT=Calibri]'Microsoft ActiveX Date Objects 2.1 Library[/FONT]
[FONT=Calibri]'Microsoft Outlook View Control[/FONT]
[FONT=Calibri]'=============================================[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri]DoCmd.Echo False, "Running Program - mod_eFlowProcess3" [/FONT]
[FONT=Calibri]DoCmd.Hourglass True [/FONT]
[FONT=Calibri]DoCmd.SetWarnings False [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Dim filenum As Integer [/FONT]
[FONT=Calibri] Dim Count As Long [/FONT]
[FONT=Calibri] Dim tmp As String [/FONT]
[FONT=Calibri] Dim rst As DAO.Recordset [/FONT]
[FONT=Calibri] Dim DB As Database [/FONT]
[FONT=Calibri] Dim FS As FileSystemObject [/FONT]
[FONT=Calibri] Dim Folder As Folder [/FONT]
[FONT=Calibri] Dim subFolder As Folder [/FONT]
[FONT=Calibri] Dim File As File [/FONT]
[FONT=Calibri] Dim TextFilePath [/FONT]
[FONT=Calibri] Dim dtmDate As Date [/FONT]
[FONT=Calibri] Dim dtmFileDate As Date [/FONT]
[FONT=Calibri] Dim TextFileDate [/FONT]
[FONT=Calibri] Dim NameOfFile [/FONT]
[FONT=Calibri] Dim FileNameWithExt [/FONT]
[FONT=Calibri] Dim strTemp As String [/FONT]
[FONT=Calibri] Dim FileLoc As String [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Const ForReading = 1 [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Set DB = CurrentDb [/FONT]
[FONT=Calibri] Set FS = CreateObject("Scripting.FileSystemObject") [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] dtmDate = Date [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Call BiasiProcess 'Call the Biasi Process[/FONT]
[FONT=Calibri] [B][COLOR=red]Call GlowwormProcess 'Call the Glowworm Process[/COLOR][/B][/FONT]
[FONT=Calibri] Call MiraProcess 'Call the Mira Process[/FONT]
[FONT=Calibri] [B][COLOR=red]Call VaillantProcess 'Call the Vaillant Process[/COLOR][/B][/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] TextFilePath = "B:\" 'Set the text file path here [the location where text files are be stored][/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Set Folder = FS.GetFolder(TextFilePath) 'Open the text file folders[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] For Each subFolder In Folder.SubFolders 'Loops through the Folders looking for SubFolders[/FONT]
[FONT=Calibri] For Each File In subFolder.Files 'Loops through Subfolders looking for Files[/FONT]
[FONT=Calibri] NameOfFile = GetAttr("File.Name") 'Get the text file name[/FONT]
[FONT=Calibri] If Right(File.Name, 4) = ".txt" Then 'If the file found has a ".txt" extension then[/FONT]
[FONT=Calibri] FileNameWithExt = Mid$(File.Name, InStrRev(File.Name, "\") + 1) [/FONT]
[FONT=Calibri] strTemp = Mid$(File.Name, InStrRev(File.Name, "\") + 1) [/FONT]
[FONT=Calibri] NameOfFile = Left$(strTemp, InStrRev(strTemp, ".") - 1) [/FONT]
[FONT=Calibri] subFilePath = Left$(subFolder, InStrRev(subFolder, "\")) [/FONT]
[FONT=Calibri] SubFolderName = Mid$(subFolder, InStr(3, subFolder, "\")) [/FONT]
[FONT=Calibri] FileLoc = subFolder & "\" & File.Name [/FONT]
[FONT=Calibri] If File.Name <> "rtmail.txt" Then [/FONT]
[FONT=Calibri] DoCmd.TransferText acImportFixed, "eFlowImportSpecRegs", "tbleFlowTextFilesConvert", subFolder & "\" & FileNameWithExt, False, "" [/FONT]
[FONT=Calibri] End If[/FONT]
[FONT=Calibri] End If[/FONT]
[FONT=Calibri] DoEvents [/FONT]
[FONT=Calibri] DoCmd.Echo True, "PROCESS 3: Importing Text Files From Live Drive Location: " & NameOfFile & ".txt" [/FONT]
[FONT=Calibri] Next[/FONT]
[FONT=Calibri] Next[/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] Call eFlowProcess4 [/FONT]
[FONT=Calibri] [/FONT]
[FONT=Calibri] DoCmd.Echo True, "Program End" [/FONT]
[FONT=Calibri] DoCmd.Hourglass False [/FONT]
[FONT=Calibri] DoCmd.SetWarnings True [/FONT]
[FONT=Calibri]End Function[/FONT]It is when this code is run and when both code modules are called that the Glowworm module doesn't complete the exporting of the data into an excel spreadsheet format.
Even when I swap the call of the code modules around or place the Glowworm Module call after the Call for the Vaillanat Module the same problem occurs.
If there is anyone out there that can point me in the right direction or even knows why this is happening and how to resolve it, I would most appreciate your responses.
Regards
John Lee
