Ok, so I figured out a way to convert the tables into Quickbooks and for about the past year I've exported the table manually. Now I would like to do it automatically so I can take a vacation and others can do it.
I have to export a table into a csv file, which is basically a text file but is separated by commas instead of tabs.
I tried using DoCmd.Outputto acOutputTable, qbtblName, acFormatXLS, qbFileName
qbtblName = "tblqwikconvert"
qbFileName = "c:\qwikie.xls"
***Note: I have also tried doing this with it being exported as a text file
After I created my form, I went and created an export specification. The way I did that, and there are probably easier ways, was to go to the table I wanted to export and right click, and then go to export. Then I put in a file name and chose TEXT/CSV as the file type, and chose export. In that screen I put in my formats and then clicked on "Save As" and then named it "lead_notes_export_spec", like in the above code. After it was saved I cancelled the export and went to my form and clicked my button, and voila, it created my file on my C drive.
Is there away of doing this so its an automated event at the end of an on click process? So far ive got my database importing, processing and now i want it to export to a text file. I want to be promped to choose a save name for the file.
You need to use the TransferText method of DoCmd. To help you with the parameters, I would suggest creating a macro to do it. In my opinion, the descriptions in the macro setup better describe the parameters it is looking for. Once you have it successfully working as a macro, then it is very easy to turn it into code. In code, type:
DoCmd.TransferText and then enter the values you did for the macro.
Hope this will help.
If you want to go really posh and would like to give the users the power to output where ever with a interface to select a destination folder as well as predicting the output file name and displaying within the form then read further:
The idea is to make a form first with a some text box's named:
'slash' with default value '\' - enabled=no
'format' with default value '.txt' - enabled=no
'Filename' with no default - enabled=no
command button named 'command0'
command Button named 'Startimport'
Now make a module and save the code 'APIDialogBOX'(attachment) into it.
Then go back to your form and open under the properties of 'command0' on click event type:
Private Sub Command0_Click()
Me.selfilename = BrowseFolder(CurDir & "\*.mdb")
Now go to the properties of 'Startimport' and under click event add this code
Note: this code is based on that you first dump the data into a table which needs to exported by a 'make a table query' this gives you the flexibility to change the restriction whenever you want.So make sure you make a query and save it on your database window, I named it 'QRYExportMakeTabRoutine' which is a make a table query type.)
Private Sub StartImport_Click()
Dim rst As New ADODB.Recordset
Dim strNewTableName As String
Dim strExistingTableName As String
Dim strFileType As String
On Error GoTo ErrorHandler
If IsNull(Me.selfilename) Then
MsgBox "You must select a Path or Destination Folder.", vbExclamation, "Error"
DoCmd.OpenQuery "QRYExportMakeTabRoutine", acNormal, acReadOnly
DoCmd.TransferText acExportDelim, "SOexpo", "ExpoTemp", Me.selfilename & Me.slash & Me.Filename & Me.format, True, "", 50000
DoCmd.DeleteObject acTable, "ExpoTemp"
MsgBox "Export Was Successfully Completed. Check Your Destination Location for the File", vbExclamation, "Export Status"
Save and come out of VB, Now go and run just the query- make a table and do a manual routine to export
when you choose to export a table then in the ExportDialogBox select what type you want to export as should be txt, then select if you want export with field name (I have choosen Yes) then under the advance tab select
Field and txt sepraters and on the right hand side click save as 'SOexpo' , after that it should be available under SPEC to view. Cancel the routine and come out now go back to your form and try to export should work like a charm.
And if it gives too many OK and cancel messages then go under tools/option/editfind/actionqueries and take the tick away