automatically export each record to separate text/html files with VBA (1 Viewer)

[world33]

Hi all,

I searched but i could not find any code for exporting each record of an access query to a separate text file using a VBA module as suggested in this Microsoft article Outputting one HTML file per record in an Access database at http://office.microsoft.com/en-us/access/HA010345961033.aspx

I managed to adopt the first method (Use a report to output one HTML file per record) but it is not possible to specify a custom name for the exported files.

The second method (Use the VBA PRINT statement to output one HTML file per record) is much more powerful but i could not find any pre-made code example around.

My MS Query is very simple and has just 2 fields (ID, UniversityName). I would like the ID to be the name of the exported html/text files and the UniversityName the content of the exported html/text files. Obiously I would like to generate automatically one separate html/text file for each record.

Is there any pre-made VBA script that can help me achieve this? my knowledge of VBA is very limited.

Thanks heaps for your help,

FF

 

[ajetrumpet]

take a look at this faq: http://www.access-programmers.co.uk/forums/showthread.php?t=183685


from this, you can adopt some code to do what you want. for example, use recordset objects to open your query statement, loop through the records and adopt the code in that faq to make the text files. your dec section would look like:

    Dim fs, s, oFile
    Set fs = CreateObject("Scripting.FileSystemObject")

within each record loop, you would do something like this:

s = rs!University
       
   Set oFile = fs.createtextfile("c:\local folder\" & rs!ID & ".txt", True)

   oFile.writeline s
   oFile.Close

 

[world33]

Thanks m8. Given my limited knowledge of vba I have no idea where to start. I appreciate anyway your time and reply. cheers.

 

[ajetrumpet]

Thanks m8. Given my limited knowledge of vba I have no idea where to start. I appreciate anyway your time and reply. cheers.

if this is the case, i would suggest hiring someone to do it quickly for you. someone astute with VBA can do this easily, I'm sure