Problems calling Function with Argument (Need to pass File name from one function to another)

[SamatarM]

I am trying to integrate 2 functions.

I have one sub function which works to loop through all files one by one.

once it has identified the file name.


It should call the function to run, on the opened file.


I can not seem to find a way to pass this on,


I did some reading on calling functions with arguments but when i try this i get a "compile error seperate list or )"


Can you please point me in the right direction?


I have posted the code below:

Option Explicit
Option Base 1

Public Const DATASHEET As String = "MDFDATA"
Public Const TABLECONVERSIONSHEET As String = "TABLECONVERSION"
Public Const OPTIONSSHEET As String = "OPTIONS"
Public Const FinalSheet As String = "Final Sheet"
Public lByte_Order As Long                  'byte order
Public lData_Groups As Long                 'number of data groups
Public lChannel_Groups As Long              'number of channel groups
Public lChannels As Long                    'number of channels
Public lTable_offset As Long                'row offset for the conversion table sheet
Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
Dim sFile_Name As String                'MDF file name
Dim lFile_Number As Long                'file number
MyFolder = "C:\Users\Mohamed samatar.DSSE-EMEA\Documents\EQVL\Test"
sFile_Name = Dir(MyFolder & "\*.dat")
Do While sFile_Name <> ""
lFile_Number = FreeFile
Open sFile_Name For Binary Access Read Shared As lFile_Number
Call PARSE_MDF


Loop
End Sub
'==================================================================================================
'   PARSE_MDF
'   Main function
'   Returns True if successful
'==================================================================================================
Function PARSE_MDF() As Boolean
    Dim sFile_Name As String                'MDF file name
    Dim lFile_Number As Long                'file number
    
    Dim lData_Groups_Counter As Long        'data groups counter
    Dim lChannel_Groups_Counter As Long     'channel groups counter
    Dim lChannels_Counter As Long           'channels counter
    Dim lRecords As Long                    'number of records in data block
    Dim lRecord_Length As Long              'length of record in data block
    
    Dim lData_Group_Address As Long         'data group address
    Dim lData_Address As Long               'data address
    Dim lChannel_Group_Address As Long      'Channel group address
    Dim lChannel_Address As Long            'Channel address
    
    Dim byCol As Byte                       'column counter for output
    Dim wsData_Sheet As Worksheet           'main worksheet
    Dim wsTable_Conversion_Sheet As Worksheet
    
    Dim rFirst_Signal As Range              'first signal in channel group
    Dim rLast_Signal As Range               'last signal in channel group
    Dim rSignals As Range                   'range of signal names for a channel group
    Application.EnableEvents = False
    
    lTable_offset = 0
    Set wsTable_Conversion_Sheet = Workbooks(ActiveWorkbook.Name).Worksheets(TABLECONVERSIONSHEET)
    Set wsData_Sheet = Workbooks(ActiveWorkbook.Name).Worksheets(DATASHEET)
    
      
    'file selected
    If sFile_Name <> "False" Then
        'clear old data
        wsData_Sheet.Columns.Clear
        wsTable_Conversion_Sheet.Columns.ClearContents
        
        'set headers
        wsData_Sheet.Cells(1, 1).Value = "Signal name"
        wsData_Sheet.Cells(2, 1).Value = "Data type"
        wsData_Sheet.Cells(3, 1).Value = "Lsb"
        wsData_Sheet.Cells(4, 1).Value = "Offset"
        wsData_Sheet.Cells(5, 1).Value = "Bit length"
        wsData_Sheet.Cells(6, 1).Value = "Formula ID"
        wsData_Sheet.Cells(7, 1).Value = "Formula"
        wsData_Sheet.Cells(8, 1).Value = "First Bit position"
        wsData_Sheet.Cells(9, 1).Value = "Table length"
        wsData_Sheet.Cells(10, 1).Value = "Start Row"
        
        'offset columns because of headers
        byCol = 2
        
        'get file number
        lFile_Number = FreeFile
        
        'open file
              
            'check file integrity
            If IDBLOCK(lFile_Number) Then
                      
                'check data exists
                If HDBLOCK(lFile_Number, lData_Group_Address) Then
                    'main iteration for data groups
                    For lData_Groups_Counter = 1 To lData_Groups
                    
                        'check channel group exists
                        If DGBLOCK(lFile_Number, lData_Group_Address, lChannel_Group_Address, lData_Address) Then
                            'channel group iteration
                            For lChannel_Groups_Counter = 1 To lChannel_Groups
          
                                'get channel group data
                                Call CGBLOCK(lFile_Number, lChannel_Group_Address, lChannel_Address, lRecord_Length, lRecords)
                                'set the first signal range in this channel group
                                Set rFirst_Signal = wsData_Sheet.Cells(1, byCol)
                                
                                'channels iteration
                                For lChannels_Counter = 1 To lChannels
                                
                                    'get channel data for each channel
                                    Call CNBLOCK(lFile_Number, lChannel_Address, wsData_Sheet, byCol)
                                    
                                    'excel fudge
                                    If byCol <> 255 Then
                                        byCol = byCol + 1
                                    End If
                                    
                                Next    'lChannels_Counter
         
                                'set the last signal range in this channel group
                                Set rLast_Signal = wsData_Sheet.Cells(1, byCol - 1)
                                
                                'format divider columns
                                wsData_Sheet.Columns(byCol).ColumnWidth = 5
                                wsData_Sheet.Columns(byCol).Interior.ColorIndex = 0
                                wsData_Sheet.Columns(byCol).Interior.Pattern = xlLightUp
                                wsData_Sheet.Columns(byCol).Interior.PatternColorIndex = xlAutomatic
                                
                                'excel fudge
                                If byCol <> 255 Then
                                    'for space between channels
                                    byCol = byCol + 1
                                End If
                                
                            Next    'lChannel_Groups_Counter
                            'get range of signals to get data for
                            Set rSignals = wsData_Sheet.Range(rFirst_Signal, rLast_Signal)
                            'get signal data
                           
                        'no channel data in this data group
                        Else
                            PARSE_MDF = False
                            
                        End If
                    
                    Next    'lData_Groups_Counter
 
                'no data in MDF file
                Else
                    PARSE_MDF = False
                
                End If
            'not a MDF file
            Else
                PARSE_MDF = False
            
            End If
            
        'close file
        Close #lFile_Number
            
        'tidy up sheet
        wsData_Sheet.Rows.EntireRow.AutoFit
        wsData_Sheet.Columns.EntireColumn.AutoFit
        wsData_Sheet.Rows("2:15").EntireRow.Delete
        wsData_Sheet.Columns("A:A").EntireColumn.Delete
        wsData_Sheet.Cells.HorizontalAlignment = xlCenter
                
        'function ends normally
        PARSE_MDF = True
        
    'no file was selected
    Else
        PARSE_MDF = False
        
    End If
    Application.EnableEvents = True
    
End Function

 

[RoryA]

Oh - try changing this loop in the first sub:

Do While sFile_Name <> ""
    Call PARSE_MDF(sFile_Name)
Loop

to this:

Do While sFile_Name <> ""
    Call PARSE_MDF(MyFolder & "\" & sFile_Name)
Loop

 

[SamatarM]

Perfect!!!!!

Thank you so much for taking the time

Quick question, how did that change affect it being recognised as an MDF?

 

[RoryA]

Because the file path wasn't included, the code was actually opening a new blank file in the current directory and reading that.

 

[SamatarM]

Thanks again much appreciated!

 

[RoryA]

Glad to help.

 

[SamatarM]

Hi Rory, Just one last question, I have been trying to get the sheet to list all the "signal names" I have managed to do this successfully by specifying that:

Nextrow = Range("A" & Rows.Count).End(xlUp).Row + 1
    
    'signal name
    sSignal_Name = Space(32)
    Get lFile_Number, lChannel_Address + &H1B, sSignal_Name
    
    If InStr(1, sSignal_Name, "\") <> 0 Then
        sSignal_Name = Mid(sSignal_Name, 1, InStr(1, sSignal_Name, "\") - 1)
    End If
    
    wsData_Sheet.Range("A" & Nextrow) = sSignal_Name

However , I want it to select a new column for each file (each loop) ,this is something I can not seem to get itto do.?

Do I need to start a new thread for this?

 

[SamatarM]

I have managed to get it working by removing the above code and include the request in code below:

Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
Dim sFile_Name As String                'MDF file name
Dim lFile_Number As Long                'file number
Dim i As Integer
MyFolder = "C:\Users\Documents\EQVL\Test"
sFile_Name = Dir(MyFolder & "\*.*")
Do While sFile_Name <> ""
i = i + 1
    Call PARSE_MDF(MyFolder & "\" & sFile_Name, i)
    sFile_Name = Dir()
Loop
End Sub

Thanks again Rory! Finally completed.

 

[SamatarM]

Correction *

Sub OpenFiles()
Dim wsData_Sheet As Worksheet           'main worksheet
Dim MyFolder As String
Dim MyFile As String
Dim sFile_Name As String                'MDF file name
Dim lFile_Number As Long                'file number
Dim i As Integer
Dim lRow As Long
MyFolder = "C:\Users\Mohamed samatar.DSSE-EMEA\Documents\EQVL\Test"
sFile_Name = Dir(MyFolder & "\*.*")
Do While sFile_Name <> ""
Set wsData_Sheet = Workbooks(ActiveWorkbook.Name).Worksheets(DATASHEET)
lRow = Cells(Rows.Count, "A").End(xlUp).Row
i = lRow + 1
    Call PARSE_MDF(MyFolder & "\" & sFile_Name, i)
    sFile_Name = Dir()
Loop
End Sub