Excel and forms
-
Hi to all i would like to know about some VBA and excel. I would like to create a form and i would like to use excel as my database. How am i suppose to code it and update the "database". Before updating the database i have a Txt file from my TCS system as it only generate txt file. In this file there are lots of space, comma and semicolon. therefore the code need to delimite the space, comma as well as semicolon. how am i suppose to do it?? besides updating, in the form i need to read the data as well. so how to read the data from the database, maybe is it like C# SQl where they is a need of some store procedure. and for some part i would like to read selected column. once again how am i suppose to code thanks once again thanks my dear friends Warm Regards Veon
-
Hi to all i would like to know about some VBA and excel. I would like to create a form and i would like to use excel as my database. How am i suppose to code it and update the "database". Before updating the database i have a Txt file from my TCS system as it only generate txt file. In this file there are lots of space, comma and semicolon. therefore the code need to delimite the space, comma as well as semicolon. how am i suppose to do it?? besides updating, in the form i need to read the data as well. so how to read the data from the database, maybe is it like C# SQl where they is a need of some store procedure. and for some part i would like to read selected column. once again how am i suppose to code thanks once again thanks my dear friends Warm Regards Veon
It sounds like you are maybe confusing terms. Your TCS system (whatever that is) is apparently your database. In other words, the TCS system holds the data that you are working with. What is it that you actually need to do with the data from the text file that the TCS system puts out? Do you just need to create readable reports, or do you need to manipulate the data, and feed it back into the TCS system, or what? Generally speaking Excel should be used to display data, and perhaps make some calculations based on that data. As a "database", it is not the ideal solution. You would be much better off downloading Visual Studio Express 2008 from Microsoft (it's free), which includes a simple database, and offers much better tools to do what it sounds like that you need to do. It allows you to program in C++, C#, JAVA, and VB, so you will also be able to find plenty of tutorials and other helpful articles on the internet.
My advice is free, and you may get what you paid for.
-
It sounds like you are maybe confusing terms. Your TCS system (whatever that is) is apparently your database. In other words, the TCS system holds the data that you are working with. What is it that you actually need to do with the data from the text file that the TCS system puts out? Do you just need to create readable reports, or do you need to manipulate the data, and feed it back into the TCS system, or what? Generally speaking Excel should be used to display data, and perhaps make some calculations based on that data. As a "database", it is not the ideal solution. You would be much better off downloading Visual Studio Express 2008 from Microsoft (it's free), which includes a simple database, and offers much better tools to do what it sounds like that you need to do. It allows you to program in C++, C#, JAVA, and VB, so you will also be able to find plenty of tutorials and other helpful articles on the internet.
My advice is free, and you may get what you paid for.
hi my dear friend thanks for ya advice, maybe we could discuss this issuse and come out with a solution :).ok actually i'm doing a system for company and it has a system (TCS system) that links to other station where this system will catch the sales data from all the station. This TCS system is at HQ where we will generate the data into text file as this is an old school system which is still using MDOS. so currently they will just manually input the txt file to excel and process it. therefore its very time consuming. so i'm trying to create a software to help them generate their reports as well as other things related to the Txt file faster, easier and user friendly :) hope to hear from you Warm Regards veon
-
hi my dear friend thanks for ya advice, maybe we could discuss this issuse and come out with a solution :).ok actually i'm doing a system for company and it has a system (TCS system) that links to other station where this system will catch the sales data from all the station. This TCS system is at HQ where we will generate the data into text file as this is an old school system which is still using MDOS. so currently they will just manually input the txt file to excel and process it. therefore its very time consuming. so i'm trying to create a software to help them generate their reports as well as other things related to the Txt file faster, easier and user friendly :) hope to hear from you Warm Regards veon
Okay, so first of all you need to automatically convert a text file to Excel. But I am still not entirely clear on what it is that you need to do with the data once you put it in Excel. Or is converting the text file to Excel really the only problem?
My advice is free, and you may get what you paid for.
-
Okay, so first of all you need to automatically convert a text file to Excel. But I am still not entirely clear on what it is that you need to do with the data once you put it in Excel. Or is converting the text file to Excel really the only problem?
My advice is free, and you may get what you paid for.
hi Besides converting there is still other problems. i'm still wondering what to use to programe this system. Anyway Once i get the data into excel it will become my "database", the current situation is that everymonth this data from all the outlet would be like 10000 Rows in excel and my staff need to manually input using privot table to generate the total sales for stations. besides that the manager would also like to view the turnover as compare to last year by Q1, Q2,Q3, Q4 ,H1,H2 or a year. from the report maybe it could generate graphs or chart for a better view. thanks veon
-
hi Besides converting there is still other problems. i'm still wondering what to use to programe this system. Anyway Once i get the data into excel it will become my "database", the current situation is that everymonth this data from all the outlet would be like 10000 Rows in excel and my staff need to manually input using privot table to generate the total sales for stations. besides that the manager would also like to view the turnover as compare to last year by Q1, Q2,Q3, Q4 ,H1,H2 or a year. from the report maybe it could generate graphs or chart for a better view. thanks veon
Again, Excel would not become your "database", it would become your reporting tool. In other words, the program to show manager the sales results. The TCS system is your database; it actually collects and stores the sales data (i.e. it is a database). However, do I understand correctly, that the data in the text file is not in a list form? For Example:
Customer
Sales
Profit
A
100.00
10.00
B
150.00
20.50
etc. First, again I recommend downloading Visual Studio, and challenging your VB and SQL skills. If the data in the text file needs to be ordered into a database first, so you can query the data, before showing it in Excel, I recommend using Visual Studio's built-in Database program to first store your data. So: The TCS System creates a text file (with "chaotic" data) --> your application reads the text file, extracts the necessary data, and puts it in order. Then it stores the ordered data in MS SQL Express Server (database program) --> next your application queries the database, and writes the necessary data (for example Q1 sales) to an Excel file. And you create a macro that builds the pivot tables from the data. As an alternative, you could use a regular version of MS SQL Server, installed on a server, and create queries directly in Excel from any workstation.
My advice is free, and you may get what you paid for.