Save to csv text file

[davesmith202]

Just want to save the contents of a memo field to a new text file. My code is below. But something odd is happening. I get some data output but when I open it up in a spreadsheet, it shows masses of data in the first cell (A1) and then data slightly misaligned for the rest of the spreadsheet. Any ideas why that might be? What should I be doing?

Dim strFile As String

strFile = "C:\Documents and Settings\Dave Smith\Desktop\" & "MSN-CSV-Data-" & DefaultID & ".csv"

Open strFile For Output As #1
Write #1, MSNCSV
Close #1

Thanks,

Jon

 

[boblarson]

Same thing as on Utter Access:

Post the current routine that builds the text box output and we'll modify it so you can call it for various purposes (including the export).

 

[davesmith202]

Same response...

I want a separate routine because I only occasionally need to export the data to a csv file.

Going line by line is beyond my programming ability. If anyone knows how to do this or pointers I would be very grateful!

 

[boblarson]

Same response...
I want a separate routine because I only occasionally need to export the data to a csv file.

Going line by line is beyond my programming ability. If anyone knows how to do this or pointers I would be very grateful!

That answer is not very useful. I DO know how to do this and if you would give the code that you are using to create the text box output it would take limited time to just modify it for you so it would work in BOTH circumstances by means of an extra boolean that you use when you call it. Not rocket science and, by your own admission, you don't know how to do it. I keep trying to tell you that I will help you with this but you seem intent on doing it your own way, even though this is more efficient and you even get someone to do it for you. That just confuses the @$%% out of me.

 

[davesmith202]

Bob, I appreciate your help. But the code used for creating the output is nested in hundreds of lines of code. To provide everything in the nested for/next loops would be very cumbersome and in addition there is proprietary code that I am not allowed to reveal.

In essence, there is a line of code in a loop that looks like this:

MSNCSV = MSNCSV & variousvariables & Chr(13) & Chr(10)

That is where the textbox gets its value from. Only need it exported 5% of the time and to my desktop for convenience.

 

[Banana]

But the code used for creating the output is nested in hundreds of lines of code.

I've never found any good reason to have a single function that contains 100s lines of code. I usually break my code into discrete block and call functions for a given task.... Do one thing but do it well. (UNIX philosophy)

Even so, you could at least list the variables used, and the section where we are actually doing the export so we can understand what you are. One line isn't enough to give us the context and thus analyze the problem.

 

[DJkarl]

Just want to save the contents of a memo field to a new text file. My code is below. But something odd is happening. I get some data output but when I open it up in a spreadsheet, it shows masses of data in the first cell (A1) and then data slightly misaligned for the rest of the spreadsheet. Any ideas why that might be? What should I be doing?

Dim strFile As String
 
strFile = "C:\Documents and Settings\Dave Smith\Desktop\" & "MSN-CSV-Data-" & DefaultID & ".csv"
 
Open strFile For Output As #1
Write #1, MSNCSV
Close #1

Thanks,

Jon

Have you tried using Print #1 instead of Write #1?

 

[davesmith202]

Banana, can I ask why listing the variables is important? Ideally, I would like to have a command button that exports the data from the MSNCSV field on the current record.

DJkarl, I just tried it and it made no difference.

 

[Banana]

That was in response to your statement that you couldn't post the whole routine- in order for us to help you, we need to know what variables were declared, and what types those are, plus the sections that actually deal with the export.

It could be a problem with type of variable being used, or how it is used, or it can be something else, but we won't know until we have the context.

 

[davesmith202]

The confusion comes because it was a follow on from a thread where Bob suggested I incorporate the routine in my loop, whereas I was suggesting I clean routine for it. In the latter case it would be just the code I gave in my first post. In the former case, all variables are just strings. DIM MSNCSV as String. etc.

 

[boblarson]

Banana, can I ask why listing the variables is important? Ideally, I would like to have a command button that exports the data from the MSNCSV field on the current record.

DJkarl, I just tried it and it made no difference.

I know you don't believe me when I tell you that the code that creates the output of the text box should be the same code that exports the csv file. It would save time and effort for all involved because to send to the csv file in the way you want, you need to build EACH LINE SEPARATELY and write it out SEPARATELY and there is no reason to come up with so much code to split the text box back into individual lines when you have the code already that creates the lines of code. The only change would be in your function where you would include a parameter like

Public Function CreateLinesForTextBox(blnExport As Boolean) 
   ...your code to create the text output
    If blnExport Then
        ...Write #1, MSNCSV
    End If
... Your code to continue building the text box output
End Function

which would then write out line-for-line the text file as you are building the line for the text box. One function - little extra coding, compared to creating a whole new function to take the output of the textbox and split it back into individual lines in order to write it out as a CSV file (you've been programming for how many years now? You asked for the expert help and we keep trying to give it to you, but you keep wanting it in the most inconvenient and whacked out programming way).

 

[davesmith202]

I hear what you say Bob but it isn't quite what I want. As I stated before, I only want to create that file on my desktop on the occasion when I need it, not every time. In addition, I will be using the same code for 5 other similar fields. I can then just call the routine for each textbox that needs it.

To me, creating that CSV file on my desktop every time when I don't want that is whacked out programming.

 

[Banana]

Bob wasn't giving you a code that would create it every time; it only creates the export if the boolean flag is toggled. That can be done by, say, checking a checkbox before clicking the button or having a messagebox pop up asking "Do you want to export it?" Yes/No. Boolean by default, are false, so unless you explicitly set it to true, no export is made.

 

[boblarson]

I won't say another word...

 

[davesmith202]

It's not possible to tick the box beforehand because the workflow requires export at a later date and on different fields at different times. That happens way after the initial code run that generates the MSNCSV field contents. You will eventually realise that what I am asking for is not actually the wrong way but the right way to do it.

 

[Banana]

Will that source where you got data from still be available when you want to export it out?

If so, then it would be easier to have a separate routine that deals with the export and a associated button to execute it.

 

[davesmith202]

Yes Banana, that is exactly what I am looking for and has been a point of contention between myself and Bob. Bob seems to think that I should export the file every time but ignores the fact that is not what I am seeking. And for some reason he gets shirty about it.

What happens is that the data that is stored in the 5 memo fields I mentioned may require Export at any time. So the user would navigate to the record and then click the Export button next to the appropriate memo field. This would export the data of that particular Memo field to the desktop.

The routine I listed in my first post exports the data but for some strange reason, I get loads of data in the first field of Excel when I open the csv file, and the rest in the other fields. Strange. If I copy and paste the data into a csv file, its fine!

Hope that clarifies things.

So, my current state of play is, how do I do that?

 

[Banana]

Well, if the source is still available, why not export from that instead from the textbox and avoid the hassle of formatting (I suspect there is something about textbox that screws around with the formatting)?

That was what Bob was telling you- modify the code so it would export to either textbox or a text file either by clicking the appropriate button or having the checkbox. This means you only have one piece of code that works, instead of two pieces.

 

[davesmith202]

There still seems to be confusion. The data would be displayed in the textbox anyway because it is stored in a field. I can't tick a checkbox to say textbox or textfile when the data is initially generated, since it is always stored in a field. At the point when the MSNCSV field is populated, you are not looking to export to a textfile.

It works like this. A process is run and the data is stored in a memo field which has a table source. At some future date, I might want to click a command button to export that data. I can't combine the too. Make sense?

Are you suggesting using a dlookup to get the data from the specific table's field'?

 

[davesmith202]

Banana, your thoughts gave me an idea. I created a string and exported that instead, to avoid formatting issues.

strMSNCSV = MSNCSV
Open strFile For Output As #1
Print #1, strMSNCSV
Close #1

It worked! Got there in the end and thanks for that suggestion. Rep given.