Navigation
Recent Posts
- Macro to insert footer
- $this PHP Variable
- php5 extensions directory and mysql undefined function
- Windows Vista biggest disappointment of 2007
- Excel giving incorrect results
- SnagIt 8.2.0 workaround
- Find Matching Text in Excel
- Excel macro to copy to Custom Template
- UK Post Code GeoCoding
- Accept User Input in SQL*Plus
- Exit SQLPlus from Batch File
- Run Multiple SQLPlus Queries via DOS batch file
- CSS Browser Differences IE and Firefox
- Create csv import file from Excel
- Use Query Result as Variable in Batch File
Archive
- November-2009
- August-2008
- January-2008
- December-2007
- November-2007
- October-2007
- September-2007
- August-2007
- June-2007
- May-2007
- April-2007
- March-2007
- February-2007
- January-2007
Create csv import file from Excel
In the macro below I create a csv file from the worksheet named accrual. As the sheet is the data returned from an MS Query I first refresh the query before creating the csv file. The Query refreshes from cell A2 as row one contains the header row, the field identifiers required to import into a Finance System.
To use it copy the VBA into the worksheet you wish to run it on and change the details as necessary, i.e. sheet name, whether you wish to refresh the query or not, save as file name etc..
I actually use this twice in the workbook, once to create a csv file of invoices to import into the Sales Ledger, and the second time to create a reversing accrual to import into the Nominal Ledger, to accrue the revenue, as the Invoices are dated first day of the month so miss quarter end tax point for VAT. The accrual posts the revenue into prior month and reverses it out again on the first day of the next month.
Sub Accrual()
Dim wb As Workbook
Dim source As Range
Dim dest As Workbook
Sheets("accrual").Activate
'If sheet is the result of a query refresh the data
'started on A2 as row one contains the field identifiers
'Comment out if you do not need to refresh a query
ActiveSheet.Range("A2").Select
Selection.QueryTable.refresh BackgroundQuery:=False
Set source = Nothing
On Error Resume Next
Set source = Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "No Data", vbOKOnly
Exit Sub
End If
Application.ScreenUpdating = False
Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With
'Change Filename to what you require
dest.SaveAs Filename:="c:accrual.csv", _
FileFormat:=xlCSV, CreateBackup:=False
dest.Close SaveChanges:=False
'Return to main application page
Application.ScreenUpdating = True
Sheets("Form").Activate
End Sub
Posted in: Business
Tags: csv | Import file | Sage MMS | VBA
