LinkBack URL
About LinkBacks
I have made the original macro in excel 2007 in on a windows XP machine. I changed to an iMac with excel 2011 and am changing the parts of the macro that do not operate on the iMac.
The macro is designed to open a downloaded text file with new bank transactions, convert it to the format of my bookkeeping file, compare it row by row with the last imported row in the bookkeeping file and import only new transaction lines in the bookkeeping file.
Below are the altered Macro in Excel 2011 and the original in Excel 2007. The green parts work fine, but the red parts don't and I can't figure out why the don't work. Can anyone help me out or point me in the right direction cause I am kinda stuck?
Tnx T&E
Altered Macro in Excel 2011 for Mac (Not working)
Sub ImportTransactiesBewerkenAanvullenExporteren()
'Scherm Update uit
Application.ScreenUpdating = False
' Sub ImportTransacties ()
' ImportTransacties Macro
Workbooks.OpenText Filename:= _
"iMac L&C HD:Users:Luuk:Documents:Dropbox:Financien:L&C:TransactiesL&C.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 5), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1))
' Sub TransactiesBewerken ()
' TransactiesBewerken Macro
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("D:D").Select
Selection.Cut Destination:=Columns("A:A")
Columns("C:D").Select
Selection.ClearContents
Columns("F:F").Select
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns("F:F").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Columns("G:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
' Sub SamenvoegenTekstKolommen()
' SamenvoegenTekstKolommen Macro
Dim a As Long
Dim i As Long
i = Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To i
With Cells(a, 13).Text = True
End With
Cells(a, 13) = Cells(a, 14) & " " & Cells(a, 15) & " " & Cells(a, 16) & " " & Cells(a, 17) & " " & Cells(a, 18)
Next a
Range("N:Q").Clear
' Sub ColumnFormatMoney()
' KolomFormatMoney Macro
'Dim xCell As Variant
'Range("F1").Select
' Range(Selection, Selection.End(xlDown)).Select
'For Each xCell In Selection
' xCell.Value = CDec(xCell.Value)
' Next xCell
' Sub JaarMaandAanvullen()
' JaarMaandAanvullen Macro
Dim b As Long
Dim j As Long
j = Cells(Rows.Count, 1).End(xlUp).Row
For b = 1 To j
Cells(b, 3) = Year(Cells(b, 1))
Cells(b, 4) = Month(Cells(b, 1))
Next b
' Sub SaldiAanvullen()
' SaldiAanvullen Macro
Dim c As Long
Dim k As Long
k = Cells(Rows.Count, 1).End(xlUp).Row
For c = 1 To k
If Cells(c, 5) = "D" Then
Cells(c, 7) = -(Cells(c, 6))
Cells(c, 8) = (Cells(c, 6))
End If
If Cells(c, 5) = "C" Then
Cells(c, 7) = (Cells(c, 6))
Cells(c, 8) = -(Cells(c, 6))
End If
Next c
' Sub InvoegenNieuwTransactiesInBoekhouding()
' InvoegenNieuwTransactiesInBoekhouding Macro
Dim zRow As Long
Dim dRow As Long
Dim lRow As Long
' zRow is het regelnummer van de laatste transactie die ingevoerd is in de boekhouding
zRow = Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(Rows.Count, 1).End(xlUp).Row
'l is het regelnummer van de laatste transactie in de download "transactiesL&C.txt"
lRow = Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
For dRow = 1 To lRow
If Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow, 1) _
= Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(dRow, 1) _
And Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow, 6) _
= Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(dRow, 6) _
And Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow, 9) _
= Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(dRow, 9) _
And Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow, 10) _
= Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(dRow, 10) _
And Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow, 13) _
= Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(dRow, 13) _
Then Range("A1").Offset(dRow, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Next dRow
Selection.Copy
Windows("BoekhoudingL&C.xlsm").Activate
Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow + 1, 1).Select
ActiveSheet.Paste
' Scherm update weer aan
Application.ScreenUpdating = True
End Sub
Original Macro in Excel 2007 (Worked perfectly):
Option Explicit
Sub ImportTransactiesBewerkenAanvullenExporteren()
'Scherm Update uit
Application.ScreenUpdating = False
' Sub ImportTransacties ()
' ImportTransacties Macro
ChDir "Z:\Documents\Dropbox\Financien\L&C"
Workbooks.OpenText Filename:= _
"Z:\Documents\Dropbox\Financien\L&C\TransactiesL&C.txt", Origin:= _
xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:= _
True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 5), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 2), Array(8, 5), Array(9, 1), _
Array(10, 1), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array( _
16, 2)), TrailingMinusNumbers:=True
' Sub TransactiesBewerken ()
' TransactiesBewerken Macro
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("D:D").Select
Selection.Cut Destination:=Columns("A:A")
Columns("C:D").Select
Selection.ClearContents
Columns("F:F").Select
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("F:F").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Columns("G:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
' Sub SamenvoegenTekstKolommen()
' SamenvoegenTekstKolommen Macro
Dim a As Long
Dim i As Long
i = Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To i
With Cells(a, 13).Text = True
End With
Cells(a, 13) = Cells(a, 14) & " " & Cells(a, 15) & " " & Cells(a, 16) & " " & Cells(a, 17) & " " & Cells(a, 18)
Next a
Range("N:Q").Clear
' Sub ColumnFormatMoney()
' KolomFormatMoney Macro
Dim xCell As Variant
Range("F1").Select
Range(Selection, Selection.End(xlDown)).Select
For Each xCell In Selection
xCell.Value = CDec(xCell.Value)
Next xCell
' Sub JaarMaandAanvullen()
' JaarMaandAanvullen Macro
Dim b As Long
Dim j As Long
j = Cells(Rows.Count, 1).End(xlUp).Row
For b = 1 To j
Cells(b, 3) = Year(Cells(b, 1))
Cells(b, 4) = Month(Cells(b, 1))
Next b
' Sub SaldiAanvullen()
' SaldiAanvullen Macro
Dim c As Long
Dim k As Long
k = Cells(Rows.Count, 1).End(xlUp).Row
For c = 1 To k
If Cells(c, 5) = "D" Then
Cells(c, 7) = -(Cells(c, 6))
Cells(c, 8) = (Cells(c, 6))
End If
If Cells(c, 5) = "C" Then
Cells(c, 7) = (Cells(c, 6))
Cells(c, 8) = -(Cells(c, 6))
End If
Next c
' Sub InvoegenNieuwTransactiesInBoekhouding()
' InvoegenNieuwTransactiesInBoekhouding Macro
Dim zRow As Long
Dim dRow As Long
Dim lRow As Long
' zRow is het regelnummer van de laatste transactie die ingevoerd is in de boekhouding
zRow = Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(Rows.Count, 1).End(xlUp).Row
'l is het regelnummer van de laatste transactie in de download "transactiesL&C.txt"
lRow = Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
For dRow = 1 To lRow
If Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow, 1) _
= Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(dRow, 1) _
And Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow, 6) _
= Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(dRow, 6) _
And Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow, 9) _
= Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(dRow, 9) _
And Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow, 10) _
= Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(dRow, 10) _
And Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow, 13) _
= Workbooks("TransactiesL&C.txt").Worksheets(1).Cells(dRow, 13) _
Then Range("A1").Offset(dRow, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Next dRow
Selection.Copy
Windows("BoekhoudingL&C.xlsm").Activate
Workbooks("BoekhoudingL&C.xlsm").Worksheets("Transacties").Cells(zRow + 1, 1).Select
ActiveSheet.Paste
' Scherm update weer aan
Application.ScreenUpdating = True
End Sub
Register To Reply
Bookmarks