Solved: Export/Import Range to Text File

[Doc]

Greetings all
First I just want to say thanks for this great site, keep up the good work.
Second I apologize if my question has been answered already, but I have searched the forum since the day that I registered trying to find a solution to my problem and haven?t been able to find anything yet, I have found some that was close to what I need but since I?m new to excel and vba I was unable to implement the codes that I have found to do what I need.
Ok here?s my problem I need to export a range of cells to a text file from a named worksheet and all the changes made to that sheet range area, has to be saved to the text file automatically I would say every 20 minutes the data is saved to that text file and when the workbook is closed, the range data has to be deleted from the worksheet range and when the workbook is open again it will load the data back to the data range automatically.

Question, is it possible to have the data loaded to the worksheet refreshed every 10 minutes or so?

I appreciate all and any help that you can give me on this matter.

[JimmyTheHand]

Hi Doc :hi:
Welcome to VBAX!

You want to export alll changes of a certain range into a textfile every 20 minutes. You also want to import data from the same textfile every 10 minutes. I suspect you want to import the data because, in the meantime, someone else has modified the textfile. I also suspect the Excel project you want to create will function as a user interface to manipulate data stored in the textfile, which is accessible by every uer.

This all looks like a classical database functionality. I need to ask, are you sure you want this in Excel? What you want is possible, I'm sure, but I'm also positive on the account that data integrity cannot be maintained in this way. For example, two user saving data into the same textfile, overwriting eah other's modifications?

Jimmy

[Doc]

Hi Jimmy!
Thanks for taking the time to answer my post.

Jimmy what I?m trying to do is create a task and appointment on excel, basically the person on front desk would be the one that will add data to that worksheet, but I intend to be able to add and modify data as well from time to time, and the other people only will be opening the workbook to check what we have so far.

The worksheet would be named task and the data range would be named as DataEntry and the data from that range (DataEntry) should be saved to a text file.

The reason that I?m trying to do that on excel is because it allows me to use the conditional formatting to apply colors to rows and cells based on the date or type of task that the use have added, among other things.

e.g. Today?s appointment would be highlighted in green etc?

Maybe you right I would be better off doing that with some other application, but I think excel is much easier and fast especially for a person like me that doesn?t have much knowledge about coding and stuff.

People are putting a lot of pressure on me because they need something like that asap and since I?m working against the clock I thought I would try to do that on excel for now and later on I could try to find a better way to accomplish that.

But I?m open to ideas and suggestions on how to get that done.

I hope I have answered all your questions if not please let me know ok?

Thanks again in advance for all and any help that you guys can give me to get this done.

[Bob Phillips]

I don't get why you would rfemove the data on close then re-load it on open, seems pointless to me.

The data exported to a textfile, can that be amended within the textfile, otherwise why bother?

[Doc]

I don't get why you would rfemove the data on close then re-load it on open, seems pointless to me.

The data exported to a text file, can that be amended within the textfile, otherwise why bother?

That was just a thought (because I was thinking on saving the workbook as a template) but again that was just a thought, the most important thing to me is to be able to save the range from a named worksheet to a text file and be able to import it back to that same range.

but as I said I'm open to ideas and suggestions.

Thanks.

[Bob Phillips]

To make any sensible suggestions, we need to understand the requirement. What is that made you think the solution was to export the range every 20 mins, and import it again every 10 mins (I presume those 2 times would never coincide)?

[Doc]

To make any sensible suggestions, we need to understand the requirement. What is that made you think the solution was to export the range every 20 mins, and import it again every 10 mins (I presume those 2 times would never coincide)?

Because I was thinking about the front desk people would have that workbook open most of the time and the other people that would only open the book to check what tasks were added could always get a up to the minute info.

again that was just a thought.

Thanks again for taking the time to try to help me.

[JimmyTheHand]

Access, too, allows conditional formatting, and I still say Access would be better suited for this task. And VBAX is also a good place to seek help with Access, and it's no problem if you don't have coding knowledge. Time and patience are much more important. But I understand that you are in a hurry, so I agree that first aid goes first, and it will provide the necessary time to reach a better solution.

Can you imagine something like this as a first aid:
1) There is the operator user who keeps the workbook, which is, in effect, the up to date database, opened all the time. He does data entry and deletion all the time.
2) There are you, who have your own excel program, which creates a separate, temporary workbook with your data additions.
3) The operator's workbook has a built in macro which, from time to time, checks if there is a temporary workbook, and if there is one, opens it and appends its content to the main database.
4) There are the workers, who can open the main workbook (the databese) only for reading, as it is alerady opened by the operator.

The advantage of this solution is that there's no need to parse text files, which simplifies the task a lot (IMO).

Jimmy

[Doc]

Access, too, allows conditional formatting, and I still say Access would be better suited for this task. And VBAX is also a good place to seek help with Access, and it's no problem if you don't have coding knowledge. Time and patience are much more important. But I understand that you are in a hurry, so I agree that first aid goes first, and it will provide the necessary time to reach a better solution.

Jimmy

I will give it a try, but since it could take a couple of days or weeks in order for me to get it done, and since a have to start from scratch in access, can you or anybody else provide me with a solution to export/import to a text file just so I can have something that we can use for now untill I get that done in access?

Thanks again

[Doc]

I already tryed to implement the code in the link below with no go.
http://vbaexpress.com/forum/showthread.php?p=46322#post46322

P.S. Im gonna follow your advice and try to integrate everything in access and I hope i dont get stuck on this project for months and lose all my hairs along the way. :yes

[JimmyTheHand]

The attached workbook might be a good start. Modify it as you desire.

Sheet "Def" is for definition data. Right now it contains only the exact time of the last data export. It gets updated each time when the workbook is opened, and also when the data is exported. You can hide sheet "Def" so that it doesn't confuse users.

Sheet "Data" contains the data that gets exported into the textfile. Worksheet_Change event compares last export time to current time, and if difference is greater than ExportFrequency then calls the ExportData sub


The exported data goes into the textfile called "C:\DataBase.csv", in csv format. The file gets overwritten with each export. I set the range to be exported as Data!A1: D300 but this can be freely changed.

If you don't follow, please ask. In any event, I recommend you to study the VBA code on all modules.

Jimmy

[Doc]

Hi Jimmy!

First I would like to thank you for the wise advises that you gave me and for guiding me into the right path on getting my project done, there’s no words that can describe how thankful I am, you not only provided me with great advices but also you provided me the solutions to my problem as well so thank you, thank you, thank you.

Not only you but also the community in this forum is one of a kind, you guys really care about other people’s problem and do whatever you can to help, I haven’t seen one single post unanswered and not to say that almost 99% of it has been solved which is something amazing, so keep up the good work guys.

Jimmy I already started working on access and I hope I will get it done in a couple of days or so, and I also have tried the solution that you provided me and it’s working beautifully.

Also I would like to thank all others who have taking the time to assist me on this project as well, you guys are great and you all have my gratitude and I think I can say that in name of all our community as well.

God bless you all.