0

We're currently migrating from W10 to MacOS in my company and I'm trying to convert some VBA scripts to be MacOS compatible. We have this macro in W10 where we gather data depending on errors in columns from several csv files and place it in the main workbook, where it then get filtered by a different macro:

Sub Splunk_import()

'String definition
Dim Datei As String
Dim letzteZeileNachImport As String
Dim letztezeile As String
Dim Dateiname As String
Const E109 As String = "_ERROR109_"
Const E207 As String = "_ERROR207_"
Const E1302 As String = "_ERROR1302_"
Const resetting As String = "_resetting_"
Const RDT As String = "RDT"
Const ETDR As String = "ETDR"
Const TSENF As String = "TSENF"
Const RAJP As String = "RAJP"
Dim ChDir As String
'TBD
Application.ScreenUpdating = False

'Here we determine the last row of column A
letztezeile = ActiveSheet.Cells(1048576, 1).End(xlUp).Row
'letzteZeilePlusEins = letztezeile + 1

'UserId
UserName = VBA.Environ("Username")
'Path preset for the dialog box
ChDrive "C:\"
ChDir = ActiveWorkbook.path
'File type is limited to csv
Datei = Application.GetOpenFilename("csv Files (*.csv), *.csv")
'Filename is extracted from path
Dateiname = Right(Datei, Len(Datei) - 25) '' to have only the file name without path the 25 must be adjusted for another PFad
'Delimiter preset
Workbooks.OpenText Datei, comma:=True
ActiveSheet.UsedRange.Copy _
Destination:=ThisWorkbook.ActiveSheet.Range("C" & letztezeile + 1)
ActiveWorkbook.Close False
Application.ScreenUpdating = True

'Here we determine the last row of column A after importing
letzteZeileNachImport = ActiveSheet.Cells(1048576, 1).End(xlUp).Row

'The error code is determined and entered depending on the open file
If InStr(Dateiname, E109) > 0 Then
       Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "ERROR_CODE : 109"
    ElseIf InStr(Dateiname, E207) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "ERROR_CODE : 207"
    ElseIf InStr(Dateiname, E1302) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "ERROR_CODE : 1302"
    ElseIf InStr(Dateiname, resetting) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "resetting"
    ElseIf InStr(Dateiname, RDT) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "RDT & SCBE"
    ElseIf InStr(Dateiname, ETDR) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "*ETDR*"
    ElseIf InStr(Dateiname, TSENF) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "*TSENF*"
    ElseIf InStr(Dateiname, RAJP) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "RAJP"
    Else
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "Error does not exist"
    End If
   
'Date is entered in the respective column?gt
Sheets("alle").Range("A" & letztezeile + 1 & ":A" & letzteZeileNachImport).Value = Date

'MsgBox Datei & vbNewLine & DateiName & vbNewLine & letztezeile & vbNewLine & letzteZeileNachImport, , "Meldung"
End Sub

(Code is in German as I'm still translating it, it was passed onto us some years ago)

Code above gives this code: Run-time error '1004': Method 'GetOpenFilename' of object '_Application' failed

I've tried changing the Datei = Application.GetOpenFilename("csv Files (*.csv), *.csv") to the function found here, but I can't get it to work, error below (I'm recently new to VBA, and it's the first time I've touched functions, let alone MacOS VBA)

Compile error: Type mismatch

Any help is appreciated!

Nelson
  • 17
  • 6
  • Please not that [*"It's not working"* is not helpful](http://idownvotedbecau.se/itsnotworking/). What we need is an error description and where it occurs, or a description what your code actually does and what you expect it to do. – Pᴇʜ Oct 19 '21 at 06:56
  • Sorry about that, totally forgot to add the error! Updated the question with it, thank you! – Nelson Oct 19 '21 at 08:25
  • Your question was already asked and has an answer. See link above your question. – Pᴇʜ Oct 19 '21 at 08:28
  • I've tried that solution but did not manage to get it to work, that's why I linked that same article on my text. I cannot get that function to work with my code, hence the question so it's not actually duplicated. "I've tried changing the Datei = Application.GetOpenFilename("csv Files (*.csv), *.csv") to the function found here, but I can't get it to work, error below (I'm recently new to VBA, and it's the first time I've touched functions, let alone MacOS VBA)" – Nelson Oct 19 '21 at 08:35

0 Answers0