Exporting query with parameters to csv template (1 Viewer)

uniq_usr

Registered User.
Local time
Tomorrow, 03:26
Joined
Jan 21, 2015
Messages
12
Hello All,

I have question as to the approach I should take regarding a problem I have.

I have a template csv file which has comes with headers. I now need to export multiple datas from my different tables into the csv file. I thought I would open an instance of excel, open the query as recordset (in VBA) and go through each record and finally use appExcel.saveAs as a CSV file. The approach doesnt look like it will look. I have looked into the DoCmd.Transfer text method but I cant seem to create a export specification because my query requires two parameters (startDate and endDate). If anyone could shed a bit more light on exporting results from a query into existing csv file would be great.
Secondly it is a huge template, over 700 fields and I will only be exporting around 40 fields so there will be huge number of empty fields. If this was a excel file, I would write few queries and write result from one query into the file then skip required columns and then write results from another query but I don't know if its possible with Docmd.write txt.
I can understand VBA fairly well and would really appreciate if you point me to any learning material,code examples or any other insights you have.

I am using access 2013 (Office 365 Pro) to perform all of this.
 

GinaWhipp

AWF VIP
Local time
Today, 15:26
Joined
Jun 21, 2011
Messages
5,901
Are you going to allow your Users to put in parameters via a Form and then export to your Template?
 

uniq_usr

Registered User.
Local time
Tomorrow, 03:26
Joined
Jan 21, 2015
Messages
12
The template, as far as I can see, will not be changed via access. It will be a csv file with headers written into it and I want to write into the file then save a copy as filename_timestamp. This leaves the original template intact (This is what I have been doing with excel)
The user will be in a form where all they do is select the dates and click on the button. I will then pass on this dates to my query in vba and the returned result will be written into the csv. I also have a text box where I inform the user of the steps happening (this is incase a error happens)
All of my exports so far has happened in excel but a new system requires us to export a set of data in the exact format (.csv) and they have a provided us with a template file. For eg it has 10 fields with (employee1, employee2,..) but we only use 1 employee so I will need to ensure the query exports out empty field in a lot of steps.
Please let me know if I am not clear in any of the things I wrote and really appreciate your help in this problem
 

GinaWhipp

AWF VIP
Local time
Today, 15:26
Joined
Jun 21, 2011
Messages
5,901
Oh, you have done this in Excel, well it's the same for your .CSV file only now you export to TEXT. You could probably set up and use a saved import for that. Are you having a problem converting your existing code?
 

uniq_usr

Registered User.
Local time
Tomorrow, 03:26
Joined
Jan 21, 2015
Messages
12
I find it easier to manipulate data to different cells as required using appExcel.application and .Activecell.offset. I also define cell names in the templates so I can easily focus where to start the writing from.
Thank you for your direction so far and I have been able to save it as csv, for any one stumbling upon this in the future, I have used :

Code:
appExcel.ActiveWorkbook.SaveAs FileName:=CsvFileName, FileFormat:=23
'// where CsvFileName is a variable that stores the full path and the intended file name ending with csv and the number 23 represents the excel fileformat for windows csv

I have got a slight problem, the client end of this report is demanding each field in the csv come wrapped in " " (double quotes), while saving it from excel just outputs the standard field contents. Would you know of any approach in doing it ?

Secondly, I dont know if this warrants a separate question ( I would be happy to do this), is there a limitations in the number of fields a query can have in acces, or a limitation in number of empty fields in access >

Thank you for your help so far :)
 

uniq_usr

Registered User.
Local time
Tomorrow, 03:26
Joined
Jan 21, 2015
Messages
12
As i have been doing all the manipulating with an open instance of excel in the background. I found it a bit of extra stretch on memory to again go through the whole file and write a txt file. The code i wrote above works perfectly except for the " " part. If I manually add character infront and behind each field with the use
Code:
Chr(34) & rs.fields (i) & Chr(34)    '// Chr(34) is the code for ""
. Then excel saves it as "My value" but then converting it to csv, excel will convert it to ""My value"". I cant find a way for access to use a instance of excel and force it to not convert my txt when saving a csv. A solution I will try is save the file as a excel file, use the method mentioned in the following post and save it as csv. If any one knows any other easier way of approachin this, please let me know :)
 

GinaWhipp

AWF VIP
Local time
Today, 15:26
Joined
Jun 21, 2011
Messages
5,901
Cool code.. are you saying you now have it working?
 

uniq_usr

Registered User.
Local time
Tomorrow, 03:26
Joined
Jan 21, 2015
Messages
12
Unfortunately No. The link I posted is for a excel macro. I need to do the same to my excel file from within access (using vba). Not getting anywhere with that.
 

GinaWhipp

AWF VIP
Local time
Today, 15:26
Joined
Jun 21, 2011
Messages
5,901
Any way you could set up a Template file with the Excel Macro and then dump the data to the Template, run the Macro from Access (which can be done) and then, again from within Access, do a Save As a .CSV file. That actually sounds doable if you could use a Template.
 

Users who are viewing this thread

Top Bottom