|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
I am working on quite a large form that has become very slow to load, so i'm looking to take off some of the controls and have them loading on a background worker. The most used controls will remain, and a stack will be set up of controls to load in the background, loading the most recently requested control next.
As controls can only be added on the main thread there is also a queue of controls that have been loaded on the background worker and now need adding to the form. All of this works, the issue i have is it would be useful to keep a reference to each control, so you only load it once etc.
To stop code replication it would be great if i can create an object by sending in the System.Type of the object i want to make, the controlCollection it should be added to, some data it can use and the class/form level referance you want set to the control when it has loaded.
Most of this works, however, i cannot get the class level reference to point to object created on the background worker. I get the feeling it should be doable using byRef, but am not quite sure how. I'm thinking my C++ way of thinking is causing problems here, any advice would be appreciated.
Sorry for the large amount of code being posted, think it is all needed to solve the problem.
Public Class MyClass
'class level declaration of control to load on the background worker
Private _addresses As uscAddresses = Nothing
Public Function Addresses() As uscAddresses
If _addresses Is Nothing Then 'if its not been loaded we should put it on the top of the stack
'don't worry if it is already on the stack as it will not get loaded twice
_loadQueue.Push(New ComponentToLoad(GetType(uscAddresses), _addresses, Nothing, tpAddresses))
'if we are already running the background worker no need to start it a second time
If Not bgwLoader.IsBusy Then bgwLoader.RunWorkerAsync()
'ideally won't need this next bit in the final solution
While _addresses Is Nothing
'Application.DoEvents()
'Threading.Thread.Sleep(100)
End While
End If
Return _addresses
End Function
Structure ComponentToLoad
Public Type As System.Type 'type of object that we want to load
'**************************************************************
Public Target As UserControl 'the class level reference to use for the created object
'**************************************************************
Public Data As Object 'any data to pass to the object
Public Parent As Control 'the control to add the new object to after its created
Public Sub New(ByVal inType As System.Type, ByVal inTarget As UserControl, ByVal inData As Object, ByVal inParent As Control)
Type = inType
Target = inTarget
Data = inData
Parent = inParent
End Sub
End Structure
'runs as a stack so most recent request is delt with first
Private Shared _loadQueue As New Stack(Of ComponentToLoad)
'could use another stack but a queue will do, user should see little difference
Private Shared _controlsToAdd As New Queue(Of ComponentToLoad)
Private Sub MatterManager_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'queue objects to load here if any are needed but not immediatly at form load
End Sub
'handles all loading, including checks that the object is not already loaded
Public Sub LoadControl(ByVal onUIThread As Boolean, ByRef inComponent As ComponentToLoad)
If onUIThread Then
'only add the control if its not there already
If Not DirectCast(inComponent.Parent, TabPage).Controls.Contains(inComponent.Target) Then
DirectCast(inComponent.Parent, TabPage).Controls.Add(inComponent.Target)
End If
Else
'only make the object if its not already made
If inComponent.Target Is Nothing Then
inComponent.Target = Activator.CreateInstance(inComponent.Type)
inComponent.Target.Dock = DockStyle.Fill
End If
'could pass the data in to the object here
End If
End Sub
Private Sub bgwLoader_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bgwLoader.DoWork
'check there is something in the queue
If _loadQueue.Count > 0 Then
'make reference to what we are loading so we can add it to the ui queue
Dim toLoad As ComponentToLoad = _loadQueue.Pop
'load it
LoadControl(False, toLoad)
'stick it on the ui addition queue
_controlsToAdd.Enqueue(toLoad)
End If
End Sub
Private Sub bgwLoader_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgwLoader.RunWorkerCompleted
While _controlsToAdd.Count > 0
'add all loaded controls
LoadControl(True, _controlsToAdd.Dequeue())
End While
If _loadQueue.Count > 0 AndAlso Not bgwLoader.IsBusy Then
'load the next object
bgwLoader.RunWorkerAsync()
End If
End Sub
End Class
modified on Wednesday, July 29, 2009 12:24 PM
|
|
|
|
|
This sounds a bit like you are trying to solve a basic simple problem with a really really really complicated solution.
From the way you are trying to solve this problem now, I understand that even when the form is loaded completely, the user won't need all the controls all the time anyway.
Wouldn't it be easier to just rethink your GUI design? No program should need so many buttons, text boxes, or whatever, that an average PC starts having trouble loading the interface.
Even if you were to get your solution to work, your app would likely bring the PC to its knees anyway, by the time the background thread has finished loading all controls.
Try to combine controls, and reuse them, or to move certain secondary functionality (and the accompanying controls) to its own form, which you then open with a single button or automatically, as a result of another action taken or choice made.
My advice is free, and you may get what you paid for.
|
|
|
|
|
While i understand your points, the application i am working on is a product that has been in development and use for many years, and i'm quite restricted in what i can do to change where information is displayed.
I think the C++ example of what i want is something like this:
#include <iostream>
#include <stdlib.h>
#include "objTest.h"
void main()
{
int toSet = 0;
objTest middleMan = objTest();
middleMan.setRef(&toSet);
middleMan.set(10);
std::cout<<toSet;
char c;
std::cin>>c;
}
#pragma once
class objTest
{
public:
objTest(void);
~objTest(void);
void setRef(int* newRef)
{
myVal = newRef;
}
void set(int newVal)
{
*myVal = newVal;
}
private:
int* myVal;
};
This is really quite simple and can be very powerful when used correctly, but i'm guessing not something that can be done in VB.
|
|
|
|
|
I understand your problem. In this light, I think you are right, your C++ way of thinking is causing you problems. I honestly don't know whether this can be done in VB or not, but I am certain VB was never meant for application control on that level.
Considering that the GUI must be created on the main thread, and that all interaction between user and app also happens on the main thread, what exactly are you trying to achieve by loading the controls in groups? If you consider that a PC with a multi core CPU (which will actually handle two threads at the same time), should be strong enough to load your form the old way, and a single core processor will handle each thread alternatingly, so it won't really load any faster anyway, and loading controls in groups will limit the use of the loaded controls until all controls have been loaded?
How about creating and placing groups of controls at runtime, upon some mouse-over event or something? A bit like MS Office or the windows start menu.
My advice is free, and you may get what you paid for.
|
|
|
|
|
the advantage i am looking to gain is that the main form would load up quicker, and allow interaction with the initially loaded controls, the ones that are used most. The less used but still required can be loaded say on the tab they are on being selected, however this means there will always be a wailt when the tab is clicked on, even if its a small wait.
If the controls are loaded in the background then they will be available. I agree that loading them when they are needed is a good solution, but if i could get them loaded while the app idles, or the user is doing other tasks it would just be a nice bonus.
GUI items don't need to be created on the main thread, the only action that must occur on the main thread is adding the created control to say a tab page's control collection. This may be because i am not declaring them as with events, but the way the form is used is 95% for displaying data, there is very little user interaction with the form which is why i can load them on the background worker.
Maybe its just me getting a little frustrated that VB won't let me implement whats in my head.
Thanks for the advice though.
|
|
|
|
|
Jep, like I said, VB just isn't meant for control on that level. And if you are used to C++, and the level of control it affords, you are bound to feel some frustration every once in a while.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Well, the other problem you have, besides loading way too many controls on a form, is performance when the form has to redraw itself. A large number of controls simply takes longer and longer to render and gets worse as you add controls. You cannot move this to a background thread.
Back to your original problem, I don't really see this as a viable solution considering all controls have to be created on the UI thread. Creating them on a seperate thread causes problems for messages crossing thread boundries when they a dispatched from the app's message pump. You can very easily run into issues with your GUI that are funky and hard to replicated and diagnose.
|
|
|
|
|
ok, i've got this all working now, i had to do something that i'm not massivly proud of, and i know its not very VB in its workings but here goes, just incase any one is in a similar situation. The issue i was having was that the reference in the structure would change to point at the newly created control, insted of pointing the class level object to point to the new control. I'm aware this is how things are designed to work, just lacks the ability to tell it how i want it working.
So i wrapped the control in a class, like so.
Class uscHolder
Public ctrl As UserControl
Public Sub New()
ctrl = Nothing
End Sub
End Class
That way the structure will not change its reference to the new object, that will always point at the class level object. That way setting the usercontrol in the wrapper class is applied every where.
Thanks agian for all the pointers, once i have a problem in my mind though even if its not the solution i use in the long run it will bug me untill i can think up a solution. When coding nothing should ever be impossible is a mindset i'm stuck with.
|
|
|
|
|
Hi,I am junior .net developer I want to developer an application project for a given book let say having 500 pages.I want to search for a particular keyword in the book and the application should display the lines in the book that holds the word.
For instance assume the book like a bible, in which every verse has a unique number and found in a particular chapter of the book.So that a user can search based on the verse number and a particular word as well,then the application displays the verses that matches the word or the verse number.
I need help at the starting point of the application.Should I use database for storing the entire book verse by verse or either any other mechanism which is better for handling the entire book,so that I can write codes that searches the book.
Although may question is language independent, I am developing the application using dot net technologies vb/C#.if you give me your help specially based on these languages i would appreciate it.
Thanks for help
|
|
|
|
|
Well, searching isn't all that difficult. The real question is, how is the book stored electronically? (pdf, txt, doc, dcx, etc.)
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi,
I need a function in VB6.0 that formats a number and displays in full format.
Eg. FormatFunction(1,Formattype) - One
FormatFunction(222,Formattype) - Two Hundred and Twenty Two
Thanks in Advance.
|
|
|
|
|
VB6 huu!!!!!  2009
huu!!!!!
why do you attemp to use vb6 in 2009 , microsoft has nothing to do with it again  well in C# your request is done this way
http://www.blackwasp.co.uk/NumberToWords.aspx[^]
in C++ like this
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
char *make_words(char *s, int ncomma);
char *insert_comma(long n, int *ncomma);
char *int2words(int n);
int main(void)
{
printf("100000000 = %s\n", int2words(100000000));
printf("10000000 = %s\n", int2words(10000000));
printf("1000000 = %s\n", int2words(1000000));
printf("100000 = %s\n", int2words(100000));
printf("10000 = %s\n", int2words(10000));
printf("1000 = %s\n", int2words(1000));
printf("100 = %s\n", int2words(100));
printf("10 = %s\n", int2words(10));
printf("1 = %s\n", int2words(1));
printf("\n");
printf("123456789 = %s\n\n", int2words(123456789));
printf("12345678 = %s\n\n", int2words(12345678));
printf("777777 = %s\n", int2words(777777));
printf("2005 = %s\n", int2words(2005));
printf("-273 = %s\n", int2words(-273));
getchar();
return 0;
}
char *make_words(char *s, int ncomma)
{
int i, len, rest = 0;
char *p = NULL;
static char zzz[256];
static char *ones[] = {"one ","two ","three ","four ",
"five ","six ","seven ","eight ","nine "};
static char *tens[] = {"ten ","eleven ","twelve ","thirteen ",
"fourteen ","fifteen ","sixteen ","seventeen ","eighteen ","nineteen "};
static char *twenties[] = {"","twenty ","thirty ","forty ",
"fifty ","sixty ","seventy ","eighty ","ninety "};
static char *hundreds[] = {
"hundred ","thousand ","million "};
memset(zzz, '\0', 256);
len = strlen(s);
for(i = 0; i < len; i++)
{
if ((p = strchr((s[i] == ',') ? &s[++i] : &s[i], ',')) == NULL)
{
p = &s[strlen(s)];
}
if (s[i] == '0')
{
continue;
}
if ((rest = (p - &s[i])) != 0)
{
if (rest == 3)
{
strcat(zzz, ones[s[i] - '0' - 1]);
strcat(zzz, hundreds[0]);
if (len == 7 && s[2] == '0') strcat(zzz, hundreds[1]);
if (len == 11 && s[2] == '0') strcat(zzz, hundreds[2]);
}
else if (rest == 2)
{
if (s[i] == '1')
{
strcat(zzz, tens[s[++i] - '0']);
rest--;
}
else
{
strcat(zzz, twenties[s[i] - '0' - 1]);
}
}
else
strcat(zzz, ones[s[i] - '0' - 1]);
}
if (rest == 1 && ncomma != 0)
{
strcat(zzz, hundreds[ncomma--]);
}
}
return zzz;
}
char *insert_comma(long n, int *ncomma)
{
static char zzz[30];
int i = 0;
char *p = &zzz[sizeof(zzz)-1];
*p = '\0';
*ncomma = 0;
do
{
if (i % 3 == 0 && i != 0)
{
*--p = ',';
++*ncomma;
}
*--p = (char)('0' + n % 10);
n /= 10;
i++;
} while(n != 0);
return p;
}
char *int2words(int n)
{
int nc;
char *ps, *zzz, *minus;
char *buffer;
buffer = (char *) malloc(256);
if (n < 0)
{
minus = "minus";
n = abs(n);
}
else
{
minus = "";
}
ps = insert_comma(n, &nc);
zzz = make_words(ps, nc);
sprintf(buffer,"%s %s", minus, zzz);
return buffer;
}
Good luck
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Have you tried to write a function to do this?
Try, and if you still need help, post your code.
Tim
|
|
|
|
|
Since this is such a common homework question, it's very unlikely you're going to get anyone to write it for you. You need to write this yourself, and when you get stuck , then ask questions about the specific problem you're having.
|
|
|
|
|
The fact that your school is teaching you VB6, means that they are out of touch and not a good place for you to be. VB6 is long dead. It has little to no value in the workplace. There's more existing VB6 programmers than there are legacy apps that need work.
Christian Graus
Driven to the arms of OSX by Vista.
Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
|
|
|
|
|
Hi,
I have several collections which are used many times within my application. These collections (which are mainly generic lists) are collections of user-defined objects populated with data from a database, objects instantiated form classes such as regions, ethnicities, disabilities etc.
Is it a good idea to build these collections when the application starts and hold them in memory while the application is running, or would it be better to build them when required then release them when done resulting in many database calls.
Thanks in advance.
|
|
|
|
|
Retaining the lists in memory, I do it all the time. How do you deal with the user adding to the list.
I have a static class MasterTables with a property for each table, in the getter if there is no table I get the data from the database, this requires 1 call.
If the user changes the master table data I simply set the mastertable property to null, next time it is requested a new copy is loaded.
This gets away from the initial hit when starting the app, loading all those master tables can be a noticable delay on the load. I know it can be done in a non ui thread but the master tables style suits me admirably.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ok great, thanks for the tip, sounds like a good idea and one I will definately use 
|
|
|
|
|
One consideration to make, if you edit the data, your local copy is still valid, but if many users use the program and they have a locally stored copy that is different to yours things get out of sync. You can keep a record of when you last loaded the table from the database, and also a record in the database of the last time the data was updated. This will mean you have to check you have the latest version each time, which is a database call, but you will only get the data if it has changed since you last got it.
Only really an issue with a multi user application.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
