Die Verwendung von VBA scheint mir die beste Lösung zu sein. Damit können Sie ein Makro schreiben, das sich um alle Formatierungsoptionen kümmert und hoffentlich so einfach ist, dass Ihre Finanzleute es selbst ausführen können.
Sie sagten, Sie brauchen etwas, das ein Blatt oder einen Bereich in Excel übernimmt. Die erste Spalte ändert sich nie, also können wir sie im Makro speichern, die Spalten 3-7 stammen aus dem Arbeitsblatt und Spalte 8 ist einfach leer. Bleibt noch Spalte 2 (das Quartal/Jahr als QYY) als Problem. Wenn das Quartal/Jahr irgendwo in der Arbeitsmappe angegeben ist (z. B. in einer Zelle, als Arbeitsblattname, als Teil des Arbeitsmappentitels), können wir es einfach einlesen. Andernfalls müssen Sie eine Methode finden, um das Quartal/Jahr anzugeben, wenn das Makro ausgeführt wird (z. B. ein Dialogfeld öffnen und den Benutzer auffordern, es einzugeben).
Ein einfacher Code (wir werden uns später Gedanken darüber machen, wie man ihn aufruft):
Sub ProduceStatePayrollReportFile(rngPayrollData As Range, strCompanyNo As String, _
strQuarterYear As String, strRecordCode As String, strOutputFile As String)
Die Parameter sind ziemlich offensichtlich: der Bereich, der die Daten enthält, die Unternehmensnummer für Spalte 1, das Quartal/Jahr für Spalte 2, der feste Code für Spalte 7 und die Datei, in die wir die Ergebnisse ausgeben wollen
' Store the file handle for the output file
Dim fnOutPayrollReport As Integer
' Store each line of the output file
Dim strPayrollReportLine As String
' Use to work through each row in the range
Dim indexRow As Integer
Für die Ausgabe in eine Datei in VBA benötigen wir ein Dateihandle, also eine Variable, in der wir es speichern können. Wir bauen jede Zeile des Berichts in der Berichtszeilenzeichenfolge auf und verwenden den Zeilenindex, um den Bereich zu durchlaufen
' Store the raw SSN, last name, first name and wages data
Dim strRawSSN As String
Dim strRawLastName As String
Dim strRawFirstName As String
Dim strRawWages As String
Dim currencyRawWages As Currency
' Store the corrected SSN, last name, first name and wages data
Dim strCleanSSN As String
Dim strCleanLastName As String
Dim strCleanFirstName As String
Dim strCleanWages As String
Diese Variablensätze speichern die Rohdaten aus dem Arbeitsblatt bzw. die bereinigten Daten, die in die Datei ausgegeben werden sollen. Die Benennung in "raw" und "clean" erleichtert die Fehlersuche, wenn Sie versehentlich Rohdaten statt bereinigter Daten ausgeben. Um die Formatierung zu erleichtern, müssen wir den Rohlohn von einem String-Wert in einen numerischen Wert ändern
' Open up the output file
fnOutPayrollReport = FreeFile()
Open strOutputFile For Output As #fnOutPayrollReport
FreeFile() holt sich das nächste verfügbare Dateihandle und verwendet dieses, um auf die Datei zu verweisen
' Work through each row in the range
For indexRow = 1 To rngPayrollData.Rows.Count
' Reset the output report line to be empty
strPayrollReportLine = ""
' Add the company number to the report line (assumption: already correctly formatted)
strPayrollReportLine = strPayrollReportLine & strCompanyNo
' Add in the quarter/year (assumption: already correctly formatted)
strPayrollReportLine = strPayrollReportLine & strQuarterYear
In unserer Schleife, in der wir jede Zeile abarbeiten, löschen wir zunächst die Ausgabezeichenfolge und fügen dann die Werte für die Spalten 1 und 2 ein
' Get the raw SSN data, clean it and append to the report line
strRawSSN = rngPayrollData.Cells(indexRow, 1)
strCleanSSN = cleanFromRawSSN(strRawSSN)
strPayrollReportLine = strPayrollReportLine & strCleanSSN
El .Cells(indexRow, 1) Teil bedeutet einfach die Spalte ganz links im Bereich bei der durch indexRow angegebenen Zeile. Wenn der Bereich in Spalte A beginnt (was nicht der Fall sein muss), dann bedeutet dies einfach A. Wir müssen die cleanFromRawSSN Funktion selbst später
' Get the raw last and first names, clean them and append them
strRawLastName = rngPayrollData.Cells(indexRow, 2)
strCleanLastName = Format(Left$(strRawLastName, 10), "!@@@@@@@@@@")
strPayrollReportLine = strPayrollReportLine & strCleanLastName
strRawFirstName = rngPayrollData.Cells(indexRow, 3)
strCleanFirstName = Format(Left$(strRawFirstName, 8), "!@@@@@@@@")
strPayrollReportLine = strPayrollReportLine & strCleanFirstName
Left$(string, length) schneidet die Zeichenkette auf die angegebene Länge ab. Das Format Bild !@@@@@@@@@@ formatiert eine Zeichenkette als genau zehn Zeichen lang, linksbündig (das ! steht für linksbündig) und mit Leerzeichen aufgefüllt
' Read in the wages data, convert to numeric data, lose the decimal, clean it and append it
strRawWages = rngPayrollData.Cells(indexRow, 4)
currencyRawWages = CCur(strRawWages)
currencyRawWages = currencyRawWages * 100
strCleanWages = Format(currencyRawWages, "000000000")
strPayrollReportLine = strPayrollReportLine & strCleanWages
Wir wandeln ihn in eine Währung um, damit wir ihn mit 100 multiplizieren können, um den Cent-Wert nach links vom Dezimalpunkt zu verschieben. Das macht es viel einfacher zu verwenden Format um den richtigen Wert zu erzeugen. Dies führt zu keiner korrekten Ausgabe für Löhne >= 10 Millionen Dollar, aber das ist eine Einschränkung des für die Berichterstattung verwendeten Dateiformats. Die Website 0 im Format Bildpads mit 0s überraschenderweise
' Append the fixed code for column 7 and the spaces for column 8
strPayrollReportLine = strPayrollReportLine & strRecordCode
strPayrollReportLine = strPayrollReportLine & CStr(String(29, " "))
' Output the line to the file
Print #fnOutPayrollReport, strPayrollReportLine
El String(number, char) Funktion erzeugt eine Variante mit einer Folge von number des angegebenen char . CStr verwandelt die Variante in eine Zeichenkette. Die Print # Anweisung wird ohne zusätzliche Formatierung in die Datei ausgegeben
Next indexRow
' Close the file
Close #fnOutPayrollReport
End Sub
Schleife bis zur nächsten Reihe im Bereich und wiederholen. Wenn wir alle Zeilen verarbeitet haben, schließen wir die Datei und beenden das Makro
Wir brauchen noch zwei Dinge: eine cleanFromRawSSN-Funktion und eine Möglichkeit, das Makro mit den relevanten Daten aufzurufen.
Function cleanFromRawSSN(strRawSSN As String) As String
' Used to index the raw SSN so we can process it one character at a time
Dim indexRawChar As Integer
' Set the return string to be empty
cleanFromRawSSN = ""
' Loop through the raw data and extract the correct characters
For indexRawChar = 1 To Len(strRawSSN)
' Check for hyphen
If (Mid$(strRawSSN, indexRawChar, 1) = "-") Then
' do nothing
' Check for space
ElseIf (Mid$(strRawSSN, indexRawChar, 1) = " ") Then
' do nothing
Else
' Output character
cleanFromRawSSN = cleanFromRawSSN & Mid$(strRawSSN, indexRawChar, 1)
End If
Next indexRawChar
' Check for correct length and return empty string if incorrect
If (Len(cleanFromRawSSN) <> 9) Then
cleanFromRawSSN = ""
End If
End Function
Len gibt die Länge einer Zeichenkette zurück und Mid$(string, start, length) gibt zurück. length Zeichen aus string beginnend bei start . Diese Funktion könnte verbessert werden, da sie derzeit nicht auf nichtnumerische Daten prüft
Zum Aufrufen des Makros:
Sub CallPayrollReport()
ProduceStatePayrollReportFile Application.Selection, "1234560007", "109", "01", "C:\payroll109.txt"
End Sub
Dies ist die einfachste Art, sie aufzurufen. Der Bereich ist das, was der Benutzer auf dem aktiven Arbeitsblatt in der aktiven Arbeitsmappe ausgewählt hat, und die anderen Werte sind fest kodiert. Der Benutzer sollte den Bereich auswählen, den er in die Datei ausgeben möchte, und dann auf Extras > Makro > Ausführen gehen und CallPayrollReport . Damit dies funktioniert, müsste das Makro entweder Teil der Arbeitsmappe sein, die die Daten enthält, oder in einer anderen Arbeitsmappe liegen, die geladen wurde, bevor der Benutzer das Makro aufruft.
Jemand müsste den fest kodierten Wert des Quartals/Jahres ändern, bevor der Bericht für jedes Quartal erstellt wird. Wie bereits erwähnt, ist es besser, wenn das Quartal/Jahr bereits irgendwo in der Arbeitsmappe gespeichert ist, als es hart zu kodieren und einzulesen.
Ich hoffe, das macht Sinn und ist von Nutzen
0 Stimmen
Ausgezeichnete Frage und Antwort.