Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Trying To Get Sql Query To Output Into Excel Spreadsheet


I have been trying to get an sql query to out put it's table into an excel spread sheet it is coming up with an error saying :
'Runtime error 5, Invalid procedure call or argument'

The Code I am trying to run is :

Sub SQLConnection()

Conn.Open "Driver=SQL Server;Server=EROS;Database=SII_DecisionSupport;Trusted_Connection;=Ye s"

Worksheets("Query").Activate

Range("A2:G5000").ClearContents

Tables = ("SELECT Account.Ref, Account.Portfolio, vwPortfolio.ClientCode, Fintran.HO, Fintran.FLD, Fintran.CC, Fintran.NOP") & ("FROM SII_DecisionSupport.dbo.Account Account, SII_DecisionSupport.dbo.Fintran Fintran, SII_DecisionSupport.dbo.vwPortfolio vwPortfolio") & ("WHERE Account.Portfolio = vwPortfolio.Portfolio AND Fintran.Ref = Account.Ref AND ((vwPortfolio.ClientCode='2B') AND (Fintran.ClearDate>{ts '2004-01-04 00:00:00'} And Fintran.ClearDate<{ts '2004-01-11 00:00:00'}) AND (Account.Portfolio='056'))") & ("ORDER BY Account.Portfolio DESC")

With ActiveSheet.QueryTables.Add(Connection:=Conn, Destination:=Range("A2:G5000"), Sql:=Tables)
.Refresh
End With

End Sub

Please can you help as it is driving me mad

Thanks
James




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Output In An Excel Spreadsheet?
I'd like to be able to output the content of the labels in my vb program to specific cells in an excel spreadsheet. Can I do so? How?

Thanks

Output - Excel Spreadsheet
When I save the contents of a listbox or whatever to a .xls and it will open Excel and everything will be okay. But does anyone know how I can adjust column height and width, and put data into certain cells?

Thanks,
Paul

I Need Code To Clean Up And Format Textfile And Output It Into A Excel Spreadsheet
Hi im trying to clean up and format a textfile that contains data as shown below and write the result to an Excel spreadsheet:

006252721954103167 2002090100000000N
045562081973091467 2002020100000000N
045800521973030667 2002031300000000N
116915141950110967T1996040100000000N
116915491956031367T1996040100000000N
116915571959042167T1996040100000000N
904323631954031467T2004012120040331N
904376671976070667T2004010120040331N
904382721975040267T2004010120040331N
905005041946062667 0000000000000000N
905005211967020567 0000000000000000N
905005391951062667 0000000000000000N


This is what needs to be done:

* Each line consists of 36 characters
* These lines need to be divided into columns(cells) as follows: cell 1 = char 1-8 , cell 2 = char 9-16 , cell 3 = char 17-18 , cell 4 = char 19 , cell 5 = char 20-27 , cell 6 = char 28-35 , cell 7 = char 36.

Cell 1 and 3 is fine as is.

Cell 4 can contain a 'T' or be NULL.

Cell 2,5 and 6 are containing dates in format YYYYMMDD eg. 20030423 . I need it to look like this YYYYMMDD eg. 2003423

If Cell 5 contains only zeroes,then it should insert default value of 20001201.(this should also be formatted as above)

If Cell 6 contains only zeroes,then it should insert default value of " " or it can be NULL.

Cell 7 cannot be NULL and should contain only a 'N" or 'Y' if it is NULL it should put a 'N' to the cell.

The result must be in a Excel spreadsheet. Im working with hundreds of thousands of records, and Excel only allows 60 000 records per file. Therefor i need to break the resulting file every 60 000 records and create another resulting file containing the rest of the records till there is no more.

Can you please help, this is very urgent! Im using Visual Basic at the moment. This is a doozy for me and im still new at this...If anyone knows of an easier way to this please let me know...

PS: I can't use Excel formatting in this file because the program that uses the result files does not recognise the fields.


Thanx in advance!
L

SQL Query To Excel Spreadsheet
Hi,

I was wondering how you might go about transfering a query you have just created to a exsiting Excel spreadsheet?

Any hints, tips or advice would be welcome!

Thanks in Advance.

Access Query To Excel Spreadsheet
Hello

I have an Access database which allows users to enter schedules for employees. I have put the information the information goes into a table and can be viewed from the form as a query. I have multiple records. (as below)

Date Venue Name Position
27/1/05 Home Joe Blow Permanent
27/1/05 Home Hansel Permanent
27/1/05 Home Gretel Casual
27/1/05 Home Barney Casual
27/1/05 Home Lisa Casual

There may be mutiple Venues for the same date. I want these to appear on a new row for every new venue/ date combination. Is there anyway I can take multiple records and place them into an excel spreadsheet set out as below.


Date Venue Permanent Casual
27/1/05 Home Joe Blow, Hansel Gretel, Barney, Lisa


Thankyou

Ps - I dont need the user to be able to do this themselves. I would like this to be able to be done after all records have been entered for the month.

Output From Query Run From Within Excel To Be Dumped To A .csv/.txt File.
Hi Everybody

Is there a way to run a MS Query from within Excel and have the output, instead of being returned to Excel, dumped in a .csv or .txt file?

The idea is that :-

1. The query that I am actually running, is run from within Excel using Excel VBA
2. Since the output is expected to normally contain more than 70000 rows, I would like the output to be dumped into a text file that I can open programmatically using Excel VBA and then do further processing.

Best regards


Deepak Agarwal

Excel - Macro To Query Then Output To Text File
I'm a VB newbie. I'm trying to find out if, and if so, how.....

I exported an .xls file from our school database that includes student names, course number and section. In order to import this data in to our grading program I need to sort students by course section, then save as separate text files for each section.
I can record the macro in excel to auto sort, select course, then section, copy, then paste in to a new workbook and save as a tab delimited text file that is named as the "course + section". This recording process isn't very efficient. I'm hoping to find out if I can, and if so how, have the sort automatically increment by section and course, export the sort to a text file and name the file "course + section".

I'd appreciate any suggestions or help you can offer.
Thanks
Mike

Returning 2 Fields From A Query To Be Output On A Single Output Control, Eg. Textbox/label
Hi all..

Here's another question..

Is it possible to output 2 fields from a single query onto a single output control, eg. textbox or label? I've read before.. in a book titled "Sql Fundamentals" but I can't remember much- that it is possible to do so. The syntax looks something like this..

Code:
SELECT "Field1" // " Field2" FROM table1
um.. or is it...

Code:sql = "SELECT table1.Field1, table1.Field2 FROM table1 AS '"Field1//Field2"' "

set recordset = db.OpenRecordset(sql)
set label1.caption.datasource = recordset

I guess this would mainly apply onto fields such as First Name , LastName which would usually be required to be combined into FullName...

thanks!



Edited by - hueyjenn on 3/9/2003 6:53:16 AM

Excel Spreadsheet From FTP To Customized Excel Spreadsheet
Hi guys!!

Help on this PLEASE!!

I get an EXCEL SPREADSHEET via FTP. I want to take that xls file and convert it to my own format which is ALSO an xls file. How can I do that? I went through your forums but cannot find what I am looking for. Basicly want to push a button and it must take the "rough" xls and create an new xls with the data "neat" so that I can use it.

For example the original xls has nonsense at the bottom and top like a address and names that I do not use, ONLY the "Item Codes" and "Item Description". Luckily the "Item Codes" and "Item Description" is in one column.

To Get A TABLE(for SQL-query Purpose) From A XL-spreadsheet.
To be able to query an Excel spreadsheet with SQL you need a table name.
Create this in Excel like this:

-Select your data
-menu 'Insert'
-'names'
-'define'

IMPORTANT: choose one of those really good names like 'myData' or 'myFeetSmell'.

or do it in code like that:


VB Code:
Private Sub Command1_Click() Dim xlApp As New Excel.ApplicationDim xlWb As New Excel.WorkbookDim xlWs As New Excel.WorksheetDim xlRng As Excel.Range Set xlWb = xlApp.Workbooks.Open("d:xltest1.xls")Set xlWs = xlWb.Worksheets(1)Set xlRng = xlWs.UsedRange xlWs.Names.Add "myXlTbl", xlRngxlWb.SaveAs "d:xltestA.xls" Set xlRng = NothingSet xlWs = NothingxlWb.CloseSet xlWb = NothingxlApp.QuitSet xlApp = Nothing End Sub


Now you can query it after you set up your connection or DE!

Have fun!

Helger

Invalid Data In Excel Spreadsheet/count Group Of Items In Excel Sprdsh
Please help:
Problem#1:
I populated an excel spreasheet with data from access table, using CopyFromRecordset function.  The process was successful and all the fields displayed correctly in the spreadsheet, except the date field which displayed:"########".  What did I do wrong?

Problem#2:
What vb code can I used to count group of items in the spreadsheet.
Any insight will be very helpful.
Thanks,
C.

Working With Excel Spreadsheet (*Resolved*No Excel Solution)
I haven't done any of this before, so I'm a bit stuck.
I need to create a small app that will allow the user to enter an item number, click find, and display the recordset to the user on a form. The info is stored in an excel spreadsheet. I would have done it using Access, but the user is across the country, doesn't have Access, and needs this thing by this afternoon.
Can anyone point me to something that will give me some VBA tips, or some instruction on how to do this?
I always seem to get install/access errors when I use Access db's, and with the guy being on the other side of the country, I want to avoid trying to work out all those problems. This should be a simple task, but I'm on a totally different train of thought program wise and can't seem to get into this Excel thing.

Thanks in advance.

Query Output
Ok here is the newbie question

I need to make an application at work that enables employes to browse our database without them having to enter query language, basically i will make command buttons sending popular commands for them.

Up to now i managed to connect my application to the database using

rdoConnection object
rdoResultset object

i can also perform queries via the following lines

MyClass.GetRecords ("Select * from dbo.employes")
MyClass.rs.Requery

but my problem lies here
once i made those queries, i cannot output them anyhow
i did not find a way to have it either stored in a text file or a visual way to see it in my application

can anyone help me with this

Query Cmd And Output Results In .txt
Hi,

I got this password cracking program which I sometimes use to crack 1000 password hashes at once, so when I do this in cmd some of the output are not even shown, as its so much. So I saw this other post where someone needed to ping using cmd, and I tested this and it output the ping results to a textfile. So this would be perfect for me as I would like the output to a .txt

The command for the ping to work was

Shell ("C:WindowsSystem32cmd.exe /c C:WindowsSystem32ping 192.168.1.102 >> d:myfile.txt"), vbNormalFocus

And this code pigned 192.168.1.102 and output the result to d:myfile.txt

Anyways, the program I want to query requires some more parameters, when I'm in its folder which is c: I type this to run it:

rcrack.exe *.rt -h 741294727b80bc751cc02ec886e7be6a

If you mistype any of its parameters it will result in an error.

I try this:

Private Sub Command1_Click()
Shell ("C:
crack.exe *.rt -h 741294727b80bc751cc02ec886e7be6a >> d:myfile.txt"), vbNormalFocus
End Sub

But now it only gives the error, some parameters are wrong or such.
I would also like a textfield (Text1.Text) to replace the hash, where user can enter their own hash.

This above command wont do anything, just give the error. I wont see much of it either as the cmd window goes up and away in a second.

But I know for sure that if all parameters are set right it works.

When its working it takes about 50%++ CPU usage for a few seconds, and then output the result of the passcracking after some seconds/minutes.

Hope anyone can help me out on this problem of using cmd from VB and outputting result in a txt. A kind of scrollable frame would also work for the output.

Hope anyone can help me out here, thanks alot!!

Need To Get A Textbox To Output A Query
I have a textbox (I'll call it txttext.Text). This textbox appears on a form. The value in the textbox cannot be changed by the user.

The value in the textbox is supposed to come from a query:

SELECT COUNT (nameoffield) FROM tbltablename;

However, the textbox will print #Name?

How do I get the textbox read the query and print the appropriate number?

Retreiving Output From A Query
Hello all,

  I have a stored procedure that is returning some variables.  I already have those variables declared as outputs within the procedure.  I have tried just seting up the variable's direction as output within vb but this has not worked.  Is there something else that I need to do in order to retrieve the value for the variable?



Any help, as always, is greatly appreciated.

Brian

How To Get The Sql Query Output In Vb Variable
hi all,

can anyone help me how to get the sql query output which are obtained using the aggregate functions like min,max,..etc in a vb variable.

i'ad already posted the same doubt in this forum also,but didn't got proper solution.is there any solution??if so plz post it with some sample code.

 
greetings

Query Output Into Textbox
Hi, Ive got a query that produces records with a single field. How can I put each of these returned records (single fields only) into a label or textbox, seperated by commas (,) in a vb module.

Thx
:thmb:

------------------------------------------------------------------------------
And when a man gets to heaven,
To saint Paul he'll tell:
1 more soldier reporting sir,
I served My Time in Hell
6 June 1944

2GB DDR400 RAM, 3.4Ghz EM64T P4 Socket 775, 200gb PATA HD 8mb cache, 500GB WD 16mb Cache (Master), 256mb 9950 ATI RADEON AGP ,19" LCD, and a 250W PSU

Running Vs6 Enterprise, running all vs8 products express edition

Output SQL Query Result To CSV File
Hello all.

I have the following query that I need to run at multple locations. I would like to output the results to a CSV file. Can anyone tell me the best way to accomplish this?

Query -

Code:
SELECT Items.ItemName, SUM(ItemsSold.Quantity) AS TotalCount, Transactions.TranClass
FROM Transactions INNER JOIN
ItemsSold ON Transactions.SiteID = ItemsSold.SiteID AND Transactions.ServDate = ItemsSold.ServDate AND
Transactions.LineNum = ItemsSold.LineNum AND Transactions.Transnbr = ItemsSold.TransNbr INNER JOIN
Items ON ItemsSold.ItemNum = Items.ItemNum
WHERE (Transactions.TransType BETWEEN 0 AND 7) AND (ItemsSold.ItemNum = '707')
GROUP BY Items.ItemName, Transactions.TranClass

Results -

Code:
Lunch Emergency Meal3Free
Lunch Emergency Meal475Full Pay
Lunch Emergency Meal63No ID - Full Pay
Lunch Emergency Meal66Reduced
Lunch Emergency Meal2Temporary Free
Lunch Emergency Meal23Temporary Reduced

Thanks
Garry B

Append A Query Output To A Recordset
Is there a way to append the output of a query to a existing recordset?

Hi,

I have a magazine subscriptions database and every month need to identify valid subscribers for a particular magazine using various criteria.

Say first i get users who are less than 1 yr old coming from a particular source and then if the final count is less i need to move on to add extra users from the between 1 and 2 yrs category of the same source and when all is done then if the count is still less i have to at last pick up from a list of "Referred Subscribers" to get the final number.

I used to do this previously using a union operator, but now as mentioned above i have to check with respect to the year breakup and the category the user belongs to. Hence i have to split the queries.

Is it possible to get the result of first query in a recordset and then append the output of the second query to the same recordset and so on getting finally all the records in one recordset, through which i can loop and get the desired results?

Thanks and regards

Hitendra

A Query's Output To A Word File
Actually .. I want a query's output in the word file in a table form.

something like this :

select * from employee

I ve tried putting data in the word file directly but... a query's output doesnt goes to the word file...

how do I do it ?????

VBA/Access - Query Output To Forms
Hi,
I normally work with MySQL/PHP, but technology contstraints on a short project are forcing me to use MS Access, which I have no experience with. I understand DB concepts quite well, but I have almost no clue re: the VB environment.

PROBLEM:
I have defined a many-to-many relationship in Access using a 3rd table. I need to configure a form so that selecting an item from a combo/list box enters a row in the 3rd table, connecting the records between the 1st and 2nd tables.

Can anyone give me a quick overview of how to set that part up?

Output Query Results To A Word Doc
I am running an SQL query from VB and want the result to be written to a Word doc.

Anybody can help me?

Thks

Output Query Results To A Pdf File
Kindly tell me how to save the result of a query I am running from VB and the output is dispalyed ina pdf file?

Thanks

How To Pass The Sql Query Output Value In To Vb Variable
hi all,

can anyone help me how to get the sql query output which are obtained using the aggregate functions like min,max,..etc in a vb variable.

i'ad already posted the same doubt in this forum also,but didn't got proper solution.is there any solution??if so plz post it with some sample code.

 
greetings

How To Alter The Output Of A Union All Query !
Hai,

   I am writing a Union All query as below,

Select ProductName from Company1 order by ProductName Union All Select ProductName from Company2 order by Productname.

I am displaying it in the Grid

Private Sub View_Click()
 
  Set MshFlexGrid1.DataSource = rst

I am getting the exact output what i need. But my problem is i am having the some products which have same name in both the company. I am getting the output as below now in the Grid.


3Com
Comq
Esg
Esg
Fsc
Psg
Psg
Samsung
Sup
Xirc
 


the Esg and Psg are repeating. Actually when it takes from the second company how can i identify it.

Only two or three Products will have the similar names. how can we differentiate them.
If I need to change Esg from company2 to HP Esg. How can i add it in the Grid.

What i have to do exactly here. Whether i have to change these product in the query itself or i have to change after it load the Grid.

Actually I am expecting the below output

3Com
Comq
Esg
HP Esg
Fsc
Psg
HP Psg
Samsung
HP Sup
Xirc

Kindly view this issue and reply me.


Thank you very much,
Chock.

Get Output Of A Query To Text File
Hey,

How do i get output of a query to txt fiel w/o a delimiter.

The query that I use is :
txtFile.Write (rst1.GetString(adClipString, , , vbCrLf, ""))

This takes TAB as default delimiter

can u help me please!

thanx,
twinkle

Automation Of Query To Output Results After Comparing..
Sorry guys, I'm a new programmer.. i am really new... Please bear with me, i know i'm gonna irritate u guys soon but i dun mean to!

I have a table in Access97. Let's call this Orange Table . It contains of data i want to compare with soon-to-be access table data.

I'm going to extract a list of component numbers from a program-SAP. Then this list of component numbers will be in excel form. And i have a lot of lists of component numbers from this program that i need to extract. So i need a automated program to compare data and output difference.
Then from there, i'll go to access97 and "Get External Data" from the list of component numbers i extracted. Let's call this Red Table .

From Orange Table of data, i'll need to compare them with Red Table , then output extra component numbers that Red Table has.
EG: If Red Table got component number 514784-00 and Orange Table doesn't have, then i'll need to output the component number out[in any form]. However, if Orange Table has 154879-00 while Red Table doesn't have, then dun need to output them!

How to go about designing the "Union Query" for automation of such comparing of datas just between two tables... That's all.. Please help me...

Searching 2 Tables With One Query And Getting Distinct Output
I am writing a a program to search through a db.

In a lot of instances, I get EOF/BOF errors and other db related errors..

How can I handle these errors with out the program crashing and without letting the user ?

thanks-Lee

I Want Query Output Directly In Data Reports
Hi everyone


i am developing a s/w. i need query output directly in data reports. i am using MS Access.

i have one combobox having customer's name. when i select customer from combo box. A related data must be displayed in reports.

Please help me.

Thanx in advance

waiting for reply.

Export Query Output To Multiple WOrksheet
Hi,
please enlighten me with this scenario:

I have 5 queries, each have different result, need to export each result
to 1 excel file with different sheet(1 result, 1 sheet). And if the sheet1
value = 0 then the fontcolor must be red..

thanks in advance.
Anna

Mscomm And Sending More Than One Query To Mscomm1.output
I mean ;

I try to write a vb6 program by mscomm ocx. I have finished most of them . But just one part is missing . I just wait a response for this. If anybody help , i will be appreciate.

MY Try :
The program will collect more than one different data from a one serial device.

for instance ;

I send "AB15" to device , then device respond me A's value as integer example : "375"
Then i send "BC15" to device , then device respond me B's value as integer example : "500"
Then i send "DE15" to device , then device respond me C's value as integer example : "200"

The problem I encounter is ;
But, when i try to send this data to mscomm's output by timer control by one by , some times i see mscomm respond me as an example : "375500" . I recieve this type error randomly.

Also when i send strings by timer sometimes mscomm adds 2 strings wrongly like "AB15BC15". When this occurs of course my serial device doesn't respond me correctly .

My question ;
How can i collect and send serial data to serial device without mix each other.

thanks


 





Edited by - tanero on 8/15/2007 4:11:13 AM

Excel Spreadsheet
Code:
ChDir "C:WINDOWSDesktopDailyreport"
Workbooks.OpenText Filename:="C:WINDOWSDesktopDailyreport123.txt.TXT", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1))
I am saving daily information into separate textfiles and am building an excel interface so that a user can choose 3 or more days to build a graph..so now I need to put the textfiles into an excel workbook.
The above code will open up a text file and place it perfectly into its own excel workbook....but what I really want to do is.
Open up several of these text files and place them all into the same workbook (the one I am creating these from).
So that each textfile will go into its own spreadsheet in my workbook.
Any suggestions would be appreciated.

Non Excel Spreadsheet
I would like to find a control that is similar to a spreadsheet that allows me to edit any column. I found out that the listView or whatever it is called only allows you to edit one column. I do not want the users to have to have Excel. I also would prefer a small DLL if it is required. I guess I could design my own, but I think a pre-made control would make life much easier. Thanks.


http://www.xtremevbtalk.com/show...ht=spreadsheet

http://www.google.com/search?q=-Exce...&start=10&sa=N

EXcel Spreadsheet From VB
Hi
I have the following problem :
I have embedded a exel ole object in one of my forms.
Now within my program I turn it into a Ole Automation object.
eg. dim exelobj as object
set exelobj as OLE1.object
I then populate the neccessary cells.

all works fine up to this point.

I then try to save the exel sheet using the following command exelobj.worksheets(1).saveas "test.xls". It creates the exel file but the file is blank.

please help

Excel Spreadsheet In Vb6
I need to disable the right click menu at runtime on a spreadsheet. i am able to set the menu as read only, but how do completely remove the menu?

help

Excel Spreadsheet && VB
Hello to everyone:

I am very new at VB and programming. I am taking accounting and also, looking at taking programming while in school. At the moment, I am trying to create a simple function in an excel workbook. I wanted to use a listbox in a merged cell b8 to d8. With the choice being Worksheet (1), then the only other item in the listbox would be Worksheet(2). I wanted the listbox to show the list Worksheet (1), Worksheet (2). Action would be when you clicked on one Worksheet (1) it would show Worksheet(1) and then if you clicked on Worksheet (2) you would have the second worksheet show. I wanted the individual to be able to see the listbox when they opened the workbook. I am having a heck of a time getting the listbox to show any items in it in Excel. It is there and can't even click on it. I have tried while using the VBEditor in Excel putting this command in the Module, userform or any other place even on the listbox click(). I can not get it to show let alone work. I was wondering if any one would know or could help me here. I have even tried the groups from MS Office usergroup and instead get no answers and alot of spam to my email account.

With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
lb.ControlFormat.ListFillRange = "A1:A10"
End With


any help would be greatly appreciated.

Pamela

Import An Excel Spreadsheet Into Excel
Hello
Here is another basic Excel question that I did not see the answer to in FAQ's or Excel help. Anyway, does anyone know how to bring or import an Excel spreadsheet intp a current spreadsheet. Basically I'm just trying to combine the two into one workbook. thanks for any help

Hal

How To Add Linked Tables Objects To This Access Query Output?
Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks



Code:
SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))
ORDER BY MsysObjects.Name;

Output Query Results In A Word Doc On Seperate Lines
The following is the code I have done. The query outputs more than one field which I want to be displaye din seperate lines in the word document. How do I do this?

strsql = " SELECT ccline1 , ccline2, ccline3, ccline4, ccline5, ccline6, CSCOMPREGNO, CCTN_AREA, CCTN2_AREA FROM CCONTACT_ALL cl,CUSTOMER_ALL ca Where ca.customer_id = 277255 AND ca.customer_id=cl.customer_id AND ccbill='X' AND ccseq = (SELECT MAX(ch1.ccseq) FROM CCONTACT_ALL ch1 WHERE ch1.customer_id = cl.customer_id)"
Set rs1 = cn.Execute(strsql)
str_txt = rs1.Fields.Item(0)
str_txt1 = rs1.Fields.Item(1)
str_txt2 = rs1.Fields.Item(2)
str_txt3 = rs1.Fields.Item(3)
str_txt4 = rs1.Fields.Item(4)
str_txt5 = rs1.Fields.Item(5)
str_txt6 = rs1.Fields.Item(6)
str_txt7 = rs1.Fields.Item(7)
str_txt8 = rs1.Fields.Item(8)

path_ps = "c:doku"

If openst.value = 0 And closest.value = 0 Then
MsgBox "No Status Selected", vbCritical
Exit Sub
End If

If fromdt.Text = "" Then
MsgBox "From Date not Inserted", vbCritical
Exit Sub
End If

If todt.Text = "" Then
MsgBox "To Date Inserted", vbCritical
Exit Sub
End If

If all.Text = "" And range.Text = "" And individual.Text = "" Then
MsgBox "No Subscriber Inserted", vbCritical
Exit Sub
End If

If all.Text <> "" Then
subscr = all.Text
End If

If range.Text <> "" Then
subscr = range.Text
End If

If individual.Text <> "" Then
subscr = range.Text
End If


If openst.value = 1 Then
status = 1
End If

If closest.value = 1 Then
status = 0
End If

fdate = fromdt.Text
tdate = todt.Text




'Open Word Object and Save Text

Set objword = New Word.Application

Set objdoc = objword.Documents.Open(path_ps & "DOC.doc")

objdoc.Activate

Dim wcnt, l As Integer



objdoc.ActiveWindow.Selection.WholeStory

objdoc.ActiveWindow.Selection.delete

objdoc.ActiveWindow.Selection.GoToNext (wdGoToLine)
objdoc.ActiveWindow.Selection.InsertAfter (str_txt)
objdoc.ActiveWindow.Selection.InsertParagraph


objdoc.ActiveWindow.Selection.InsertAfter (str_txt1)
objdoc.ActiveWindow.Selection.InsertAfter (str_txt2)
'objdoc.ActiveWindow.Selection.InsertAfter (str_txt2)
'objdoc.ActiveWindow.Selection.InsertAfter (str_txt3)
'objdoc.ActiveWindow.Selection.InsertAfter (str_txt4)
'objdoc.ActiveWindow.Selection.InsertAfter (str_txt5)
'objdoc.ActiveWindow.Selection.InsertAfter (str_txt6)
'objdoc.ActiveWindow.Selection.InsertAfter (str_txt7)
'objdoc.ActiveWindow.Selection.InsertAfter (str_txt8)


objdoc.ActiveWindow.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter

objdoc.ActiveWindow.Selection.Font.Bold = True

objdoc.ActiveWindow.Selection.Font.Size = 12

objdoc.ActiveWindow.Selection.Font.Italic = True

objdoc.ActiveWindow.Selection.Font.ColorIndex = wdRed





objdoc.ActiveWindow.Selection.EndOf

'objdoc.UpdateStylesOnOpen = True





objdoc.SaveAs (path_ps & "doc.pdf")


'objdoc.Close


objword.Visible = True

objword.quit (True)



Set objword = Nothing



End Sub

ebejerva
View Public Profile
Send a private message to ebejerva
Find all posts by ebejerva
Add ebejerva to Your Buddy List

Converting An Excel Spreadsheet To A PDF Using VB?
Is it possible to use VB6 to convert my workbook object into a PDF if I have Adobe Acrobat?

The program generates 13 page reports in Excel and they are too easy for people too modify. The PDF file ends up being about 1/3 the size of the workbook also. I'd rather convert them to PDFs automatically.

Thanks.

Automating Excel Spreadsheet /w VB6
Dear Sirs,

I am a Newbie here (and to VB as well) and I am sure that this question has been asked before, but I couldn't find the references anywhere, so bear with me.

My problem is very simple: I need to be able to open an existing spreadsheet, look for an empty cell, take in data from the user, based on that automatically populate cells in the spreadsheet (iteration of the original data), and save it.

Then every time the user needs to open the spreadsheet via Vb6 app it would know the empty cell location automatically and allow the user to just enter the initial data values and how many new instances the user wants to be created on the spreadsheet this time.

I would be eternally grateful for all the possible help in this matter. Some example code would be fabulous, but anything really would help to just me me going with this. I have some programming experience, so I am not a total newbie, but I haven't doen much VB, so that's why I am here.

Thanks a bunch!

Populating Excel Spreadsheet From VB6
Hey everyone,

Okay this has me a little frustrated. I am able to open Excel, write values to certain cells, save & close the spreadsheet. However, that's not exactly what I need to do.

First off, I'm writing this in VB6, not VBA. The application itself is quite complicated and couldn't be done in VBA unfortunately.

The part of the project that relates to my problem is as follows:

- I have 2 combo boxes in a VB6 project.

- When the user clicks a "POPULATE" button, I need to copy the info from the VB6 comboboxes to MS-Excel. (both combo boxes go in different cells on row 1)

- When the user changes the values in the comboboxes, then hits "POPULATE" again, it will copy the values to row 2, and so fourth.
(so it needs to be able to find out the last row with information, and put info in the following row.)

- The Excel spreadsheet should always be open.. so there's no need to close it everytime values are written to it. Just need to find the spreadsheet that is open (if it's not open, then open it... and leave it open)

I know this has been done before, but I couldn't figure out how to do it for the life of me... kinda frustrating.

I don't expect to get exact code or anything.. but some information or a URL that can help me would be greatly appreciated.

Thanks anyone for your help,

+++ath0
NO CARRIER

Saving An Excel Spreadsheet In VB
Hi,

I am opening an excel file for editing and saving it as another filename.
What I need is: if the other filename already exists, can I overwrite it automatically without the user getting prompted with...."the file already exists, are you sure you wish to overwrite it....??

Is the following code the right way to go??

xlFile.ActiveWorkbook.SaveAs FileName:="Error File" & ".xls", _FileFormat:= xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=True, _
CreateBackup:=False,...............????????????????????

Thanks in advance,

blaaboy

Connecting To An Excel Spreadsheet
greetings all, How can I connect to an excel spread sheet in visual basic? I actually had it working using ADOBC controll, but now for some reason it gives me an error which says the jet database engine canot find my record source. I am using the microsoft OLE DB provider for ODBC databases and I am using adCmdTable as my record source simply because I am not sure what else I should do for record source. The test connection is always successful but I just cant seem to get any data, should I try a different datacontroll or something? I just wish I knew why it used to work fine with ADOBC....

Excel Spreadsheet Graph
I have several spreadsheets that I need to graph but they are not exactly the same. Some have an extra row of data than others, so lets say that "widgets one" may be on row three in one spreadsheet - but on row two on the second spreadsheet. All the info I have on graphing this information gathers the data by a specific row (.Range("A1:B15")....so this will not work for me. Is there some way to query the row for "widgets one" on each spreadsheet, then query for "widgets two" on each spreadsheet....etc. like in an access table?

Need Help With Code For Excel Spreadsheet
Hello all, I'm new to using vB and especially in Excel. I've got Excel XP and am trying to use it to create a workbook for my Fantasy Football League.

The problem I'm having is arranging the standings week by week. The league has 2 divisions of 4 teams each and I would like the standing to display the team with best record at top of each division. Also, if two or more teams have the same record, they need to be displayed in order of points.

If anyone has any suggestions or comments, please post. I'll check asap and thanks in advance for any help.

rossboy69

Emailing An Excel Spreadsheet
Hi all.. in a I have a similar thing going on where the spreadsheet acts as a form.

It worked fine in Win 2000 but recently, the upgrade to XP and Office 2003 has meant some stuff has gone wrong.

I use the ActiveWorkbook.SendMail call from within Excel so it emails when you press a button in the spreadsheet. Then, as a backup confirmation, the item then appeared in your Sent Items folder (as you would expect it to).

Since the XP upgrade, it no longer goes into the Sent Items which was a necessary part of the step.. Does anyone know what has gone wrong?

Emailing An Excel Spreadsheet
I've created a spreadsheet that populates cells based on information stored in a data file. Once the macros/code have done the business and populated all the relevant fields I want to be able to automatically email the excel spreadsheet to various people who require this information

Is this possible?

Any help would be much appreciated

Copyright 2005-08 www.BigResource.com, All rights reserved