Hello!
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:
(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!
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:
VBA Code:
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!