quick question on modules/procedures/functions/sub

[Cire]

Need help on using a module to enable Export to excel functionality on all forms

Hi guys

i'm in the midst of improving my application but i need some questions answered.

My application has several queries, each having a form where the user can enter his/her criteria. So i've successfully tested using recordset to export the results to Excel(finally...) BUT this code is within a button's on_click event in an individual form. So if i want to re-use this code for all the forms that require the same functionality, how do i do this? i wouldnt want to copy and paste the same code over all the forms, i would just want to call it when its required.

i understand to an extent how modules would work in this situation but i'm not sure how its implemented. do i just Code the "Formatting part" into a module or class module? i.e.

sub FormatExcel()   'formatting cells in excel

                    With xlSheet
                        For Each Cell In xlSheet.Range("A1", "S1")
                        Cell.Font.Size = 10
                        Cell.Font.Name = "Arial"
                        Cell.Font.Bold = True
                        Cell.Interior.Color = rgb(204, 255, 255)
                        Cell.HorizontalAlignment = xlHAlignCenter
                        Cell.WrapText = True
                    Next
                        .Cells(1, 2).HorizontalAlignment = xlHAlignLeft
                        .Columns("A:S").HorizontalAlignment = xlHAlignLeft
                        .Columns("A").ColumnWidth = 10
                        .Columns("B").ColumnWidth = 24
                        .Columns("C:D").ColumnWidth = 12
                        .Columns("E").ColumnWidth = 40
                        .Columns("F").ColumnWidth = 20
                        .Columns("G").ColumnWidth = 65
                        .Columns("H").ColumnWidth = 7
                        .Columns("I").ColumnWidth = 7
                        .Columns("J:K").ColumnWidth = 24
                        .Rows(1).RowHeight = 16
                    End With
End sub

and then in the form's "export to excel" command button, code the prompt for user to select directory and the copying of recordset to a worksheet in excel, i.e.

'assume all necessary variables have been declared

    Set maindb = CurrentDb()
    Set mainqdf = maindb.QueryDefs("qryCOSearch")
    Set mainRst = mainqdf.OpenRecordset(dbOpenDynaset)

strFile = GetSaveFile_CLT("C:\", "Save this file as", "strDefName") 'dialog window to allow user to select directory to save
                'declare excel objects used to store query data
                Set xlBook = xlApp.Workbooks.Add
                Set xlSheet = xlBook.Worksheets.Add
                Call ModuleProcedureName

 'deleting all other worksheets except for "Results"
                     For lngCount = lngMax To 1 Step -1
                     If xlBook.Worksheets(lngCount).Name <> "Results" Then
                     xlBook.Worksheets(lngCount).Delete
                     End If
                     Next lngCount
                    
                    'copying the query results from the recordset to the excel file
                     With xlSheet
                        .Name = "Results"
                        .UsedRange.ClearContents
                        lngMax = mainRst.Fields.Count
                        For lngCount = lngMax To 1 Step -1
                        .Cells(1, lngCount).Value = mainRst.Fields(lngCount - 1).Name
                        Next lngCount
                        .Range("A2").CopyFromRecordset mainRst
                    End With
                        
                        lngMax = xlBook.Worksheets.Count
                        'deleting all other worksheets except for "Results"
                        For lngCount = lngMax To 1 Step -1
                        If xlBook.Worksheets(lngCount).Name <> "Results" Then
                            xlBook.Worksheets(lngCount).Delete
                        End If
                            Next lngCount
                    
                    xlBook.SaveAs strDefName
                    MsgBox "Export Successful!", vbInformation

'rest of code is clean up and error handling

is that the right method?
or can i just put the whole line of "export to excel" on_click event into a module then call this module for all the "export to excel" buttons on all my forms?

 

[namliam]

Allways try and prevent "copy/paste"-ing code with in the same database, so I would say yes this way is right...

 

[Cire]

Allways try and prevent "copy/paste"-ing code with in the same database, so I would say yes this way is right...

thanks but it isn't working...no file is being exported..i'm still trying to troubleshoot..

edit: do the sub FormatExcel need to pass any value to the main form?
right now my flow of logic for the whole procedure:

User clicks on "Export to excel" button, in the code:

1. VBA 1st launches the windows dialog to ask user to select directory to save to, declared as strFile
2. VBA then checks for any running excel application by detecting Excel.exe
3. If exists then use getobject, else use createobject
4. Run procedure(sub with no arguments in my case) to format excel file
5. After procedure ended, copy data in recordset to excel
6. If getobject used, do not use .quit because i don't the user's excel to shutdown if it was running. Else use .quit

maybe someone else have a better and more efficient way than this? and is the procedure(stored in a module) require arguments? like sub(strFormName As string)? then call it with Call subName ("strFormName")
i'm not too sure what arguments to declare..

 

[namliam]

Looking at your code in more detail, it doesnt look to be using any form settings...

Tho It does look like you are snipping it, there is a floating "Next" in your first bit and the part about deleting everything but the Result tab is double in the second bit...

Also about your code in general, use "proper" indenting, like with the For ... Next your not indenting, making it harder to read than it should be...

 

[Cire]

yes sorry its snippedm i didn't quite have the whole picture so i posted part of it, here is the entire code that i've used so far

modFormatExcel:

Option Compare Database
    Public xlApp As Object     'assiging the variable xlApp as object, xlApp will be declared as excel application
    Public xlBook As Object    'where xlBook & xlSheet are excel objects used to store the query data
    Public xlSheet As Object
Sub FormatExcelFile()
    Dim lngMax As Long
    Dim lngCount As Long

             'checking for and defining the excel object
            On Error Resume Next
            If fIsAppRunning("excel", False) = True Then
                MsgBox "Excel is Running"
                Set xlApp = GetObject(, "Excel.Application")    'points object to the active application and uses it
                Call Format
            Else
                MsgBox "Excel is not running"
                Set xlApp = CreateObject("Excel.Application")   'Create a new excel object
                Call Format
                Set xlBook = Nothing
                Set xlSheet = Nothing
                xlApp.Quit
            End If
                    
                    'deleting all other worksheets except for "Results"
                     For lngCount = lngMax To 1 Step -1
                     If xlBook.Worksheets(lngCount).Name <> "Results" Then
                     xlBook.Worksheets(lngCount).Delete
                     End If
                     Next lngCount
                    
exit_handler:
   Set xlBook = Nothing
   Set xlSheet = Nothing
   
Exit Sub
                    
End Sub

Public Sub Format()
                'declare excel objects used to store query data
                Set xlBook = xlApp.Workbooks.Add
                Set xlSheet = xlBook.Worksheets.Add
            'formatting cells in excel
                    With xlSheet
                        For Each Cell In xlSheet.Range("A1", "S1")
                        Cell.Font.Size = 10
                        Cell.Font.Name = "Arial"
                        Cell.Font.Bold = True
                        Cell.Interior.Color = rgb(204, 255, 255)
                        Cell.HorizontalAlignment = xlHAlignCenter
                        Cell.WrapText = True
                    Next
                        .Cells(1, 2).HorizontalAlignment = xlHAlignLeft
                        .Columns("A:S").HorizontalAlignment = xlHAlignLeft
                        .Columns("A").ColumnWidth = 10
                        .Columns("B").ColumnWidth = 24
                        .Columns("C:D").ColumnWidth = 12
                        .Columns("E").ColumnWidth = 40
                        .Columns("F").ColumnWidth = 20
                        .Columns("G").ColumnWidth = 65
                        .Columns("H").ColumnWidth = 7
                        .Columns("I").ColumnWidth = 7
                        .Columns("J:K").ColumnWidth = 24
                        .Rows(1).RowHeight = 16
                    End With
End Sub

code of "Export to Excel" command button:

Option Compare Database

    
    Public xlApp As Object                  'assiging the variable xlApp as object, xlApp will be declared as excel application
    Public xlBook As Object                 'where xlBook & xlSheet are excel objects used to store the query data
    Public xlSheet As Object
'code behind command button "Export to Excel"
 Sub cmdExpToExcel_Click()
    Dim mainqdf As DAO.QueryDef          'used to define pass-through query, where SQL statements are saved to
    Dim maindb As DAO.Database           'used to define active database
    Dim mainRst As DAO.Recordset         'VBA uses this variable to store the data extracted by the query defined in mainqdf
    Dim strFile As String                'used to store the string that contains the path where the user chooses to save to
    Dim lngMax As Long
    Dim lngCount As Long
   
    
    Set maindb = CurrentDb()
    Set mainqdf = maindb.QueryDefs("qryCOSearch")
    Set mainRst = mainqdf.OpenRecordset(dbOpenSnapshot)
    
         strFile = GetSaveFile_CLT("C:\", "Save this file as", "strDefName") 'dialog window to allow user to select directory to save and enter filename-strFile
      If strFile = "" Then GoTo exit_handler 'user clicked cancel so clean up
                
        FormatExcelFile      'calls procedure in modFormatExcelFile

           'copying the query results from the recordset to the excel file
                    With xlSheet
                        .Name = "Results"
                        .UsedRange.ClearContents
                        lngMax = mainRst.Fields.Count
                        For lngCount = lngMax To 1 Step -1
                        .Cells(1, lngCount).Value = mainRst.Fields(lngCount - 1).Name
                        Next lngCount
                        .Range("A2").CopyFromRecordset mainRst
                    End With
                           
                    xlBook.SaveAs strDefName
                    MsgBox "Export Successful!", vbInformation
                    
                   
                    
                
exit_handler:

   'clean up
   
   
   If Not mainRst Is Nothing Then
       mainRst.Close
       Set mainRst = Nothing
   End If

   If Not mainqdf Is Nothing Then
       Set mainqdf = Nothing
   End If

   If Not maindb Is Nothing Then
       Set maindb = Nothing
   End If
       
   Exit Sub
      
Err_Handler:
    On Error Resume Next
    MsgBox "please inform your adminstrator and tell them these information", Err.Description, vbExclamation, "Error No: " & Err.Number
    Resume exit_handler
    Resume
End Sub

when i tried to export, i got an error at the "Export to excel" command button on_click event at this line
.Name = "Results"
the error says object variable or With block variable not set

i believe its because VBA doesnt know what is xlSheet here...which is the crux of the prob i believe. xlSheet is declared in my module, as part of the formatting so this same formatted xlSheet is being used to store my recordset data which is from the query on the form where the export to excel button exists.

so once the procedure in the module is ran, it does not pass the value of the procedure back to the main form so VBA doesnt know what this xlSheet means..SO how do i solve this prob? i have up to 10 different queries thus all are using different recordsets(or should i make them use all the same recordsets?)
is it possible for the procedure's value to pass back to the calling function?
from what i understand, Function or sub with arguments simply pass the arguments BACK to the procedure being called and not the other way round..

 

[Cire]

guys please help here..i've no idea where i went wrong, apparently excel doesnt capture the path where i designated nor the filename i typed. It actually creates a "Book1" temporarily and prompted me whether i want to save the changes i made to it, if i click yes i am prompted in another window to name it and specify the directory..if i click no the file is not created but when i open up excel, there will be the book1 in there, i.e. unnamed but with all my data.

Here are my codes...

frmCOSearchDisplay's ExportToExcel on_click event:

'code behind command button "Export to Excel"
 Sub cmdExpToExcel_Click()
    'declaring variables
    Dim QdfSearch As DAO.QueryDef          'used to define pass-through query, where SQL statements are saved to
    Dim dbSearch As DAO.Database           'used to define active database
    Dim RstSearch As DAO.Recordset         'variable to store the data extracted by the query defined in QdfSearch
    Dim strFile As String                  'variable to store the path and name of the file which user defines
    'assigning variables
    Set dbSearch = CurrentDb()
    Set QdfSearch = dbSearch.QueryDefs("qryCOSearch")
    Set RstSearch = QdfSearch.OpenRecordset(dbOpenSnapshot)
   
    'dialog window to allow user to select directory to save and enter filename
    strFile = GetSaveFile_CLT("C:\", "Save this file as", "strDefName")
    
    'calls procedure in modFormatAndSaveExcelFile
    'supplies RstSearch and "strFile" to the function FormatAndSaveExcel
    Call fExportToExcel(RstSearch, "strFile", "strDefName")
                    
                
exit_handler:

   'clean up
   If Not RstSearch Is Nothing Then
       RstSearch.Close
       Set RstSearch = Nothing
   End If

   If Not QdfSearch Is Nothing Then
       Set QdfSearch = Nothing
   End If

   If Not dbSearch Is Nothing Then
       Set dbSearch = Nothing
   End If
       
   Exit Sub
      
Err_Handler:
    On Error Resume Next
    MsgBox "please inform your adminstrator and tell them these information", Err.Description, vbExclamation, "Error No: " & Err.Number
    Resume exit_handler
    Resume
End Sub

the FormatAndSaveToExcel module:

Option Compare Database
    Public xlApp As Object     'assiging the variable xlApp as object, xlApp will be declared as excel application
    Public xlBook As Object    'where xlBook & xlSheet are excel objects used to store the query data
    Public xlSheet As Object
                 

Public Function fExportToExcel(rstForm As DAO.Recordset, strFile As String, strDefName As String)
           
             'checking for and defining the excel object
            On Error Resume Next
            If fIsAppRunning("excel", False) = True Then
                MsgBox "Excel is Running"
                Set xlApp = GetObject(, "Excel.Application")    'points object to the active application and uses it
                Call fFormatAndSave(rstForm, "strFile", "strDefName")
            Else
                MsgBox "Excel is not running"
                Set xlApp = CreateObject("Excel.Application")   'Create a new excel object
                Call fFormatAndSave(rstForm, "strFile", "strDefName")
                        Set xlBook = Nothing
                        Set xlSheet = Nothing
                        xlApp.Quit
                        Set xlApp = Nothing
            End If
                            
exit_handler:
   Set xlBook = Nothing
   Set xlSheet = Nothing
   
   If Not RstSearch Is Nothing Then
       RstSearch.Close
       Set RstSearch = Nothing
   End If

   If Not QdfSearch Is Nothing Then
       Set QdfSearch = Nothing
   End If

   If Not dbSearch Is Nothing Then
       Set dbSearch = Nothing
   End If
   
Exit Function
                    
End Function
'this function is called back to the public function fExportToExcel
'which supplies the argument rstSave and strSave
Private Function fFormatAndSave(rstSave As DAO.Recordset, strSave As String, strDefName As String)
   
    Dim lngMax As Long
    Dim lngCount As Long
  
  
                'define excel objects used to store query data
                Set xlBook = xlApp.Workbooks.Add
                Set xlSheet = xlBook.Worksheets.Add
                    
                    'formatting cells in excel
                    With xlSheet
                        For Each Cell In xlSheet.Range("A1", "S1")
                        Cell.Font.Size = 10
                        Cell.Font.Name = "Arial"
                        Cell.Font.Bold = True
                        Cell.Interior.Color = rgb(204, 255, 255)
                        Cell.HorizontalAlignment = xlHAlignCenter
                        Cell.WrapText = True
                    Next
                        .Cells(1, 2).HorizontalAlignment = xlHAlignLeft
                        .Columns("A:S").HorizontalAlignment = xlHAlignLeft
                        .Columns("A").ColumnWidth = 10
                        .Columns("B").ColumnWidth = 24
                        .Columns("C:D").ColumnWidth = 12
                        .Columns("E").ColumnWidth = 40
                        .Columns("F").ColumnWidth = 20
                        .Columns("G").ColumnWidth = 65
                        .Columns("H").ColumnWidth = 7
                        .Columns("I").ColumnWidth = 7
                        .Columns("J:K").ColumnWidth = 24
                        .Rows(1).RowHeight = 16
                    End With
                    'copying the query results from the recordset to the excel file
                    With xlSheet
                        .Name = "Results"
                        .UsedRange.ClearContents
                        lngMax = rstSave.Fields.Count
                        For lngCount = lngMax To 1 Step -1
                        .Cells(1, lngCount).Value = rstSave.Fields(lngCount - 1).Name
                        Next lngCount
                        .Range("A2").CopyFromRecordset rstSave
                    End With
                    
                    'deleting all other worksheets except for "Results"
                     For lngCount = lngMax To 1 Step -1
                     If xlBook.Worksheets(lngCount).Name <> "Results" Then
                     xlBook.Worksheets(lngCount).Delete
                     End If
                     Next lngCount
                     
                        xlBook.SaveAs strSave
                        MsgBox "Export Successful!", vbInformation
        
      
End Function

 

[Cire]

and the windows dialog prompt code

and finally the windows dialog save to module, modSaveFile which i gotten off this forumn or mvps, can't quite rem:

Option Compare Database
Option Explicit

' Declarations for Windows Common Dialogs procedures
Private Type CLTAPI_OPENFILE
  strFilter As String             ' Filter string
  intFilterIndex As Long          ' Initial Filter to display.
  strInitialDir As String         ' Initial directory for the dialog to open in.
  strInitialFile As String        ' Initial file name to populate the dialog with.
  strDialogTitle As String        ' Dialog title
  strDefaultExtension As String   ' Default extension to append to file if user didn't specify one.
  lngFlags As Long                ' Flags (see constant list) to be used.
  strFullPathReturned As String   ' Full path of file picked.
  strFileNameReturned As String   ' File name of file picked.
  intFileOffset As Integer        ' Offset in full path (strFullPathReturned) where the file name (strFileNameReturned) begins.
  intFileExtension As Integer     ' Offset in full path (strFullPathReturned) where the file extension begins.
End Type

Const ALLFILES = "All Files"

Private Type CLTAPI_WINOPENFILENAME
    lStructSize As Long
    hWndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustrFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    Flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustrData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Const OFN_ALLOWMULTISELECT = &H200
Const OFN_CREATEPROMPT = &H2000
Const OFN_EXPLORER = &H80000
Const OFN_FILEMUSTEXIST = &H1000
Const OFN_HIDEREADONLY = &H4
Const OFN_NOCHANGEDIR = &H8
Const OFN_NODEREFERENCELINKS = &H100000
Const OFN_NONETWORKBUTTON = &H20000
Const OFN_NOREADONLYRETURN = &H8000
Const OFN_NOVALIDATE = &H100
Const OFN_OVERWRITEPROMPT = &H2
Const OFN_PATHMUSTEXIST = &H800
Const OFN_READONLY = &H1
Const OFN_SHOWHELP = &H10

Declare Function CLTAPI_GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
  (pOpenfilename As CLTAPI_WINOPENFILENAME) _
As Boolean
  
Declare Function CLTAPI_GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" _
  (pOpenfilename As CLTAPI_WINOPENFILENAME) _
As Boolean
  
Declare Sub CLTAPI_ChooseColor Lib "msaccess.exe" Alias "#53" _
  (ByVal Hwnd As Long, rgb As Long)

Function GetSaveFile_CLT(strInitialDir As String, strTitle As String, strDefName As String) As String
  ' Comments  : Simple file save routine. For additional options, use GetFileSaveEX_CLT()
  ' Parameters: strInitialDir - path for the initial directory, or blank for the current directory
  '             strTitle - title for the dialog
  '             strDefName - default file name and extension to use
  ' Returns   : string path, name and extension of the file specified
  '
  Dim fOK As Boolean
  Dim typWinOpen As CLTAPI_WINOPENFILENAME
  Dim typOpenFile As CLTAPI_OPENFILE
  Dim strFilter As String
  
  On Error GoTo PROC_ERR
  
  ' Set reasonable defaults
  strFilter = CreateFilterString_CLT("Microsoft Excel 2003 (*.XLS)", "*.XLS")
  If strInitialDir <> "" Then
    typOpenFile.strInitialDir = strInitialDir
  Else
    typOpenFile.strInitialDir = CurDir()
  End If
  
  If strDefName <> "Results" Then
    typOpenFile.strInitialFile = strDefName
  End If
    
  If strTitle <> "" Then
    typOpenFile.strDialogTitle = strTitle
  End If
  
  typOpenFile.strFilter = strFilter
  typOpenFile.lngFlags = OFN_HIDEREADONLY Or OFN_SHOWHELP Or OFN_OVERWRITEPROMPT
  
  ' Convert the CLT structure to a Win structure
  ConvertCLT2Win typOpenFile, typWinOpen
  
  ' Call the Common dialog
  fOK = CLTAPI_GetSaveFileName(typWinOpen)
  
  ' Convert the Win structure back to a CLT structure
  ConvertWin2CLT typWinOpen, typOpenFile
  
  GetSaveFile_CLT = typOpenFile.strFullPathReturned
      
PROC_EXIT:
  Exit Function
  
PROC_ERR:
  GetSaveFile_CLT = ""
  Resume PROC_EXIT

End Function

Sub ConvertCLT2Win(CLT_Struct As CLTAPI_OPENFILE, Win_Struct As CLTAPI_WINOPENFILENAME)
  ' Comments  : Converts the passed CLTAPI structure to a Windows structure
  ' Parameters: CLT_Struct - record of type CLTAPI_OPENFILE
  '             Win_Struct - record of type CLTAPI_WINOPENFILENAME
  ' Returns   : Nothing
  '
  Dim strFile As String * 512

  On Error GoTo PROC_ERR
  
  Win_Struct.hWndOwner = Application.hWndAccessApp
  Win_Struct.hInstance = 0

  If CLT_Struct.strFilter = "" Then
    Win_Struct.lpstrFilter = ALLFILES & Chr$(0) & "*.*" & Chr$(0)
  Else
    Win_Struct.lpstrFilter = CLT_Struct.strFilter
  End If
  Win_Struct.nFilterIndex = CLT_Struct.intFilterIndex

  Win_Struct.lpstrFile = String(512, 0)
  Win_Struct.nMaxFile = 511
  
  Win_Struct.lpstrFileTitle = String$(512, 0)
  Win_Struct.nMaxFileTitle = 511

  Win_Struct.lpstrTitle = CLT_Struct.strDialogTitle
  Win_Struct.lpstrInitialDir = CLT_Struct.strInitialDir
  Win_Struct.lpstrDefExt = CLT_Struct.strDefaultExtension

  Win_Struct.Flags = CLT_Struct.lngFlags

  Win_Struct.lStructSize = Len(Win_Struct)
  
PROC_EXIT:
  Exit Sub
  
PROC_ERR:
  Resume PROC_EXIT
   
End Sub

Sub ConvertWin2CLT(Win_Struct As CLTAPI_WINOPENFILENAME, CLT_Struct As CLTAPI_OPENFILE)
  ' Comments  : Converts the passed CLTAPI structure to a Windows structure
  ' Parameters: Win_Struct - record of type CLTAPI_WINOPENFILENAME
  '             CLT_Struct - record of type CLTAPI_OPENFILE
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
      
  CLT_Struct.strFullPathReturned = Left(Win_Struct.lpstrFile, InStr(Win_Struct.lpstrFile, vbNullChar) - 1)
  CLT_Struct.strFileNameReturned = RemoveNulls_CLT(Win_Struct.lpstrFileTitle)
  CLT_Struct.intFileOffset = Win_Struct.nFileOffset
  CLT_Struct.intFileExtension = Win_Struct.nFileExtension
  
PROC_EXIT:
  Exit Sub
  
PROC_ERR:
  Resume PROC_EXIT
  
End Sub

Function CreateFilterString_CLT(ParamArray varFilt() As Variant) As String
  ' Comments  : Builds a Windows formatted filter string for "file type"
  ' Parameters: varFilter - parameter array in the format:
  '                          Text, Filter, Text, Filter ...
  '                         Such as:
  '                          "All Files (*.*)", "*.*", "Text Files (*.TXT)", "*.TXT"
  ' Returns   : windows formatted filter string
  '
  Dim strFilter As String
  Dim intCounter As Integer
  Dim intParamCount As Integer

  On Error GoTo PROC_ERR
  
  ' Get the count of paramaters passed to the function
  intParamCount = UBound(varFilt)
  
  If (intParamCount <> -1) Then
    
    ' Count through each parameter
    For intCounter = 0 To intParamCount
      strFilter = strFilter & varFilt(intCounter) & Chr$(0)
    Next
    
    ' Check for an even number of parameters
    If (intParamCount Mod 2) = 0 Then
      strFilter = strFilter & "*.*" & Chr$(0)
    End If
    
  End If

  CreateFilterString_CLT = strFilter
  
PROC_EXIT:
  Exit Function
  
PROC_ERR:
  CreateFilterString_CLT = ""
  Resume PROC_EXIT
  
End Function

Function RemoveNulls_CLT(strIn As String) As String
  ' Comments  : Removes terminator from a string
  ' Parameters: strIn - string to modify
  ' Return    : modified string
  '
  Dim intChr As Integer

  intChr = InStr(strIn, Chr$(0))

  If intChr > 0 Then
    RemoveNulls_CLT = Left$(strIn, intChr - 1)
  Else
    RemoveNulls_CLT = strIn
  End If

End Function

so at this moment, the recordset in my form is passed back to the functions but the strFile and strDefName isn't

 

[Cire]

anyone? sigh i have perpertually given up on using a module for the export to excel functionality and just the old method, putting everything into 1 form. Until i can figure a way out..

 

[namliam]

The FormatExcelFile procedure defines the xlapp and stuff... so I think your problem is here:
Public xlApp As Object 'assiging the variable xlApp as object, xlApp will be declared as excel application
Public xlBook As Object 'where xlBook & xlSheet are excel objects used to store the query data
Public xlSheet As Object
'code behind command button "Export to Excel"
Sub cmdExpToExcel_Click()
....

you are declaring a public within the form, I think (80+percent sure) this means its public within the form... And if you put procedures outside the form they cannot use it unless you pass them on.

Alternatively you can 'simply' make them really public, which means just putting the public declaration in a module instead of a form...