data from txt / excel

[elixir_5000]

hello ,
can some one plz tell me how can i use a data from a text pad or a excel file actually i have to do some editing in those ( for example sum up all the amounts written etc) and then take a print of that . i have 2 option either data from a notepad( but that is un formatted) or i can manually open it with excel save it there then use it ,it comes in some format so looks better .

some one plz tell me how to start.

[RobDog888]

If your textfile is a comma separated values listing then just open it with Excel programmatically. Apply a formula to SUM up the needed column and thats it.

Option explicit
'add a reference to ms excel xx.0 object library
private sub command1_click()
    dim oapp as excel.application
    dim owb as excel.workbook
    set oapp = new excel.application
    set owb = oapp.workbooks.open("C:\MyFile.xls")
    owb.cells(21, 3).value = "=SUM(C1:C20)"
    Msgbox owb.cells(21, 3).value
end sub

[elixir_5000]

sir, if i open the txt file manually in excel it asks me for what type of seprations are used (i give ; ) and the file is opened perfectly how can i do so programatically as i open the file programatically it opens but semicoluns in between are also shown also sir i want some of the columns not to be shown , how can i do that .

[elixir_5000]

sir also tellme how to insert a new row in between two rows

[RobDog888]

set owb = oapp.Workbooks.OpenText Filename:="C:\Test.txt", StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, [b]Semicolon:=True[/b], Comma:=False, Space:=False,Other:=False
'...

[elixir_5000]

sir the following code gives syntax error and at run time says end of statement required ( at filename).
sir cud u plz tell me how to insert a new row in sheet

set owb = oapp.Workbooks.OpenText Filename:="C:\Test.txt", StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, Space:=False,Other:=False

[RobDog888]

Note the space and underscore chars at the end as its a line continuation syntax.

[elixir_5000]

sir that i know it is ok but there is a error saying expected end of statement and filename is highlighted

[RobDog888]

Forgot to add the parenthesis and charset. 437 is United States. If your not in the usa then change it to your locale.

set owb = oapp.Workbooks.OpenText(Filename:="C:\Test.txt", Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, Space:=False,Other:=False)

[elixir_5000]

now it is giving error expected function or variable and highlights .opentext

i am really stuck

[RobDog888]

Your not just using it by itself are you?

Option explicit
'add a reference to ms excel xx.0 object library
private sub command1_click()
    dim oapp as excel.application
    dim owb as excel.workbook
    set oapp = new excel.application
    set owb = oapp.Workbooks.OpenText(Filename:="C:\Test.txt", Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, Space:=False,Other:=False)
    'owb.cells(21, 3).value = "=SUM(C1:C20)"
    'Msgbox owb.cells(21, 3).value
end sub

[elixir_5000]

only god knows why this is still not working

[RobDog888]

Did you add a reference to Excel? Did you change the textfile location in the .OpenText function?

[elixir_5000]

yes sir i had , but what's the significance of doing that as it is giving error function or variable required. and highlightes .opentext in the line workbook.opentext

[RobDog888]

If your going to automate Excel then you need to use either Early or Late Binding. Early binbding is when you add the reference so VB knows what the objects are. When you use Late binding you dont adda reference and declare all your excel objects as generic Objects.

What version of Excel are you running as the .OpenText may have different arguments for your version.

[elixir_5000]

sir , i am using ms excel 2002 and i hav added refrence to library ms exel 10 in my vb as it was the only version available

[RobDog888]

The function call looks like its the same definition for 2002 and 2003.

http://msdn.microsoft.com/library/de...thOpenText.asp

Since it doesnt look like there is anything wrong, try recording a macro of you importing the text file then stop and view the generated macro code and compare the .OpenText call.