From Quickbooks, I can export any report directly into Excel. Quickbooks tries to maintain perfect fidelity between the report generated and what you get in Excel. An admirable goal, maybe, but here’s the result:
In this small piece of a physical inventory worksheet, you can see that columns A:D are all small. This so they can indent. Even though you can only see one level of indent in this shot, all four of those columns are used. Also G, I, and K are small, which may look nice, but it’s a pain. Generally, I’m not exporting to Excel to print – I can do that directly from Quickbooks. I’m exporting to manipulate; sort, pivot table, autofilter, and the like. These blank columns do not help in that regard. I find myself deleting them, which can be time consuming for wider reports.
I can also export from Quickbooks as a CSV. The resulting file has no blank columns and no formatting. Perfect. It’s a bit more of a process though. When I export to Excel, it opens the workbook in Excel with no interaction from me. For CSV files, I have to choose a place to save it, then navigate to the place from Excel to open it, and change the Files As Type to ‘Text Files’. But I do the same thing over and over. I export the file then immediately go and open it in Excel. I almost never want the CSV to remain on the disk or even to ever exist on the disk. I’d prefer if it opened in Excel like the other export does.
To try to eliminate steps, I created a macro to open the newest CSV file in a particular folder. My system now is to save every CSV to the same folder under My Documents. Since Quickbooks starts in My Documents, it will be me less navigation than saving it in an appropriate place on the server. The macro will check the creation dates of all the CSV files in that folder and pick the newest.
I’m using the File Scripting Object, so I start by setting a reference.
Dim sFldr As String
Dim fso As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Dim fsoFldr As Scripting.Folder
Dim dtNew As Date, sNew As String
Const sCSVTYPE As String = "Microsoft Office Excel Comma Separated Values File"
Set fso = New Scripting.FileSystemObject
sFldr = "C:\Documents and Settings\Dick.nebraska\My Documents\QBExport"
Set fsoFldr = fso.GetFolder(sFldr)
For Each fsoFile In fsoFldr.Files
If fsoFile.DateCreated > dtNew And fsoFile.Type = sCSVTYPE Then
sNew = fsoFile.Path
dtNew = fsoFile.DateCreated
There should be an easier way to find the newest file in a directory, but I couldn’t figure it out, so I just loop through all of them. I also don’t intend to store anything in this directory except CSV files, so checking fsoFile.Type is a bit of waste. Better safe than sorry – that’s what I say when it suits me. I wonder how the Type property changes in other languages or even other Excel versions. I expected something a little more generic. I probably should have used:
fsoFile.Type Like "*Comma Separated*"
That may help with other versions, but probably not other languages.