Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Using VBA seems like the way to go to me. This lets you write a macro that takes care of all of the various formatting options and should, hopefully, be simple enough for your finance people to run themselves.</p> <p>You said you need something that takes a sheet or range in Excel. The first column never changes so we can store that in the macro, columns 3-7 come from the spreadsheet and column 8 is just blank. That leaves column 2 (the quarter/year as QYY) as an issue. If the quarter/year is specified somewhere in the workbook (e.g. stored in a cell, as a worksheet name, as part of the workbook title) then we can just read it in. Otherwise you will need to find some method for specifying the quarter/year when the macro runs (e.g. pop up a dialog box and ask the user to input it)</p> <p>Some simple code (we'll worry about how to call this later):</p> <pre><code>Sub ProduceStatePayrollReportFile(rngPayrollData As Range, strCompanyNo As String, _ strQuarterYear As String, strRecordCode As String, strOutputFile As String) </code></pre> <p>The parameters are fairly obvious: the range that holds the data, the company number for column 1, the quarter/year for column 2, the fixed code for column 7 and the file we want to output the results to</p> <pre><code>' 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 </code></pre> <p>To output to a file in VBA we need to get a file handle so we need a variable to store that in. We'll build up each line of the report in the report line string and use the row index to work through the range</p> <pre><code>' 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 </code></pre> <p>These sets of variables store the raw data from the worksheet and the cleaned data to be output to the file respectively. Naming them "raw" and "clean" makes it easier to spot errors where you accidentally output raw data instead of cleaned data. We will need to change the raw wages from a string value to a numeric value to help with the formatting</p> <pre><code>' Open up the output file fnOutPayrollReport = FreeFile() Open strOutputFile For Output As #fnOutPayrollReport </code></pre> <p>FreeFile() gets the next available file handle and we use that to link to the file</p> <pre><code>' 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 &amp; strCompanyNo ' Add in the quarter/year (assumption: already correctly formatted) strPayrollReportLine = strPayrollReportLine &amp; strQuarterYear </code></pre> <p>In our loop to work through each row, we start by clearing out the output string and then adding in the values for columns 1 and 2</p> <pre><code>' Get the raw SSN data, clean it and append to the report line strRawSSN = rngPayrollData.Cells(indexRow, 1) strCleanSSN = cleanFromRawSSN(strRawSSN) strPayrollReportLine = strPayrollReportLine &amp; strCleanSSN </code></pre> <p>The <code>.Cells(indexRow, 1)</code> part just means the left-most column of the range at the row specified by indexRow. If the ranges starts in column A (which does not have to be the case) then this just means A. We'll need to write the <code>cleanFromRawSSN</code> function ourselves later</p> <pre><code>' Get the raw last and first names, clean them and append them strRawLastName = rngPayrollData.Cells(indexRow, 2) strCleanLastName = Format(Left$(strRawLastName, 10), "!@@@@@@@@@@") strPayrollReportLine = strPayrollReportLine &amp; strCleanLastName strRawFirstName = rngPayrollData.Cells(indexRow, 3) strCleanFirstName = Format(Left$(strRawFirstName, 8), "!@@@@@@@@") strPayrollReportLine = strPayrollReportLine &amp; strCleanFirstName </code></pre> <p><code>Left$(string, length)</code> truncates the string to the given length. The format picture <code>!@@@@@@@@@@</code> formats a string as exactly ten characters long, left justified (the ! signifies left justify) and padded with spaces</p> <pre><code>' 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 &amp; strCleanWages </code></pre> <p>We convert it to currency so that we can multiply by 100 to move the cents value to the left of the decimal point. This makes it much easier to use <code>Format</code> to generate the correct value. This will not produce correct output for wages >= $10 million but that's a limitation of the file format used for reporting. The <code>0</code> in the format picture pads with 0s surprisingly enough</p> <pre><code>' Append the fixed code for column 7 and the spaces for column 8 strPayrollReportLine = strPayrollReportLine &amp; strRecordCode strPayrollReportLine = strPayrollReportLine &amp; CStr(String(29, " ")) ' Output the line to the file Print #fnOutPayrollReport, strPayrollReportLine </code></pre> <p>The <code>String(number, char)</code> function produces a Variant with a sequence of <code>number</code> of the specified <code>char</code>. <code>CStr</code> turns the Variant into a string. The <code>Print #</code> statement outputs to the file without any additional formatting</p> <pre><code>Next indexRow ' Close the file Close #fnOutPayrollReport End Sub </code></pre> <p>Loop round to the next row in the range and repeat. When we have processed all of the rows, close the file and end the macro</p> <p>We still need two things: a cleanFromRawSSN function and a way to call the macro with the relevant data.</p> <pre><code>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 &amp; Mid$(strRawSSN, indexRawChar, 1) End If Next indexRawChar ' Check for correct length and return empty string if incorrect If (Len(cleanFromRawSSN) &lt;&gt; 9) Then cleanFromRawSSN = "" End If End Function </code></pre> <p><code>Len</code> returns the length of a string and <code>Mid$(string, start, length)</code> returns <code>length</code> characters from <code>string</code> beginning at <code>start</code>. This function could be improved as it doesn't currently check for non-numeric data</p> <p>To call the macro:</p> <pre><code>Sub CallPayrollReport() ProduceStatePayrollReportFile Application.Selection, "1234560007", "109", "01", "C:\payroll109.txt" End Sub </code></pre> <p>This is the simplest way to call it. The Range is whatever the user has selected on the active worksheet in the active workbook and the other values are hard-coded. The user should select the range they want to output to the file then go Tools > Macro > Run and choose <code>CallPayrollReport</code>. For this to work, the macro would either need to be part of the workbook containg the data or in a different workbook which had been loaded before the user calls the macro.</p> <p>Someone would need to change the hard-coded value of the quarter/year before each quarter's report was generated. As stated earlier, if the quarter/year is already stored in the workbook somewhere then it's better to read that in rather than hard-coding it</p> <p>Hope that makes sense and is of some use</p>
    singulars
    1. This table or related slice is empty.
    1. POExport Excel range/sheet to formatted text file
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USbarrowc
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POExport Excel range/sheet to formatted text file
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload