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




Writing ADO Recordset Into Excel


I Want to write all the records of an ADO recordset into excel.(Specifying a file name and Cell Address).

Post me an easy way




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Writing A Recordset To Jet With ADO
I’m using ADO in VB and I want to import a dBase file into an Access file. Through SQL I can load the dBase file into a recordset. But how can I put that into access now without having to go record by record, field by field? In other words, how can I write a recordset to another ADO connection in one shot?

Writing To Available Recordset Field
Hello all

Can anyone please clue me in on an easy way to identify which tablerecordset field is null (vacant) so that my code can write to that fielddynamically. Here is my dilemna in a nutshell.

I have a table with a number of fields named Item1, Item2, Item3, Item4etc...
I need to be able to determine which fields are already populated andsave data to the next available field of this type.


I've bound the table to a data control and am using therecordset.addnew, edit and updaterecord commands to handle data saves. Itried setting a variable like this but it isn't supported .......datacontrol.recordset.Item(i) as opposed todatacontrol.recordset.Item1, datacontrol.recordset.Item2 etc...

I've got to be overlooking something.
How can I accomplish this task simply?

Thanks

Writing A Recordset To Disk
Hi, anyone know if there's a way to make a generic method to write down to disk any Recordset that I want!!??

Thanks in advance!!

Modify Recordset WITHOUT Writing Changes To The DB?
I am successfully pulling in an ADO recordset via VB6 and an Oracle DB. Once I get the recordset into VB, I am wanting to stroke the data somewhat, meaning I want to change the values of some of the recordset fields. I want to do this only so I can then push the data into a Crystal Report. I do NOT want my manipulated data to be written back into the Oracle DB.

Is it possible to open a read-only Oracle connection and still be able to play with the data? Do I have to copy the data into an Array to accomplish this? Can that even be done?

Any suggestions are welcome!

Thanks.

 

Writing Combo Box To Recordset
I am trying to write the entry in a combo box field to a record set. The record set has fields and three of them will hold different entries that will be entered in the combo box field. Here is my code and when I run it, it is not recognizing that the record set field “repair_history2” as null. When I add a watch to see the value of “repair_history2” it tells me that it is a variant/null datatype and the value is Null. Why then will it not write my text from the combo box to this null record set? Am I not thinking about something logically?


Code:


Set oConn = New ADODB.Connection
oConn.Open frmSplash.sConn

strSQL = "select * from tblRepairHistory where part_no = '" & txtPartNo.Text & "'"

Set rs = New ADODB.Recordset
rs.Open strSQL, oConn, adOpenKeyset, adLockOptimistic

If rs("repair_history1").Value = vbNullString Then
rs("repair_history1").Value = cboRepairHistory.Text
ElseIf rs("repair_history2").Value = Null Then
rs("repair_history2").Value = cboRepairHistory.Text
ElseIf rs("repair_history3").Value = "" Then
rs("repair_history3").Value = cboRepairHistory.Text
End If

rs.Update

Writing Data In A Recordset To A .csv File
Hi there!

I am trying to turn data from a recordset into a comma delimited file.

So I have data that looks like this: rs("FirstName")
and I need to write it to a .csv file.

How does one write data to a separate file? Ultimately, someone else will look at that file in Notepad.

Thank you, I appreciate your help!!

Andrea

Updating A Recordset In A Child Form Without Re-writing All The Code
Hello again,

I am just wondering if I have a command like this.....:

rsChild1.Open strSQL, cnChild1, adOpenStatic, adLockReadOnly

......when I open a child form and load records into it but when I move to another record in the parent form I want to update the record in the child form without having to go through all the .open lines for the connection and recordset and sql statements, can I do this. I tried doing a:

rsChild.update

But it gave me some run-time 3251 error saying it cannot be updated. Then I realized that I didnt want to change the data so update is not what I want. But I cannot find any properties or methods that go with teh recordset that will update my sql statement automatically so new records appear.

thanks

Excel Not Closing -Writing To Excel Using ADO
Hi,

Hoping someone can help me,... I am writing data to excel using ADO (see code extract below). The data transfer from VB to Excel works fine however I have the following problem (note the order in which events happen):

1. I open Windows Task Manager to view the Processes Display.
2. I run VB and it is sending data as per user inputs (no more then say 1 a minute) -"shouldn't be a problem"
3. I view Windows Task Manager and confirm that Excel is NOT running - "normal".
4. I can open the excel file (through explorer) and see the previously transferred data and any new data - "Great!"
5. I view Windows Task Manager and confirm that Excel is NOW running - "normal".
6. If I close the excel file (I am prompted to save/not save/cancel). So I select save and the application APPEARS to close. -"still don't know how to save from VB but that's another problem".
7. I view Windows Task Manager and confirm that Excel is STILL running - "NOT normal".
8. When I try to re-open the excel file it "craps-out" cos its already OPEN!

If I follow the steps above but instead, I close VB before closing Excel, the Windows Task Manager confirms that Excel is NOT running - "normal",.... However I want to keep the VB code running cos it's doing other stuff as well (shutting VB down doesn't help me)

I am using explicit variable references so that should prevent excel from NOT unloading.

Would appreciate if someone could show me how to "fully" terminate excel using ADO (if a method exists). Alternatively I would appreciate any other work around suggestions (Please specify any required references in your suggestion).

Here's the code
--------------------------------------------
Public Sub Write_Excel(sample_Type As Integer)

'On Error Resume Next

Dim xlConn As ADODB.Connection
Dim xlRs As ADODB.Recordset

Set xlConn = New ADODB.Connection
With xlConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source =" & Excel_Path_Alt & ";" & "Extended Properties = Excel 8.0"
    .Open
End With

Select Case sample_Type
Case 0 'calibration
    Set xlRs = New ADODB.Recordset
    xlRs.Open "Select * From [CALIBRATION_DATA$]", xlConn, adOpenDynamic, adLockOptimistic
    Debug.Print "made it to case 0"
    Cal.Total = Cal.Primer + Cal.Topcoat
    xlRs.AddNew
    xlRs.Fields(0).Value = Cal.TDS
    xlRs.Fields(1).Value = Cal.SampleNum
    xlRs.Fields(2).Value = Cal.Primer
    xlRs.Fields(3).Value = Cal.Topcoat
    xlRs.Fields(4).Value = Cal.Total
    'xlRs.Fields(5).Value = Cal.HoleSize
    xlRs.Update
     
    
Case 1 'sample data

End Select

xlRs.Close
xlConn.Close

Set xlRs = Nothing
Set xlConn = Nothing

End Sub
------------------------------------------------------


Thanking you in advance.

Regards,

Burzrk

Writing To Excel
Hi guys,

I just wanna be able to write to an excel file:
P for pass
F for fail

I already know how to read by row and column I just need to write.
Any help would be appreciated....

thanks

Writing In Excel
i have created an instance of excel on my vb using the
create object....

however how do i actually create an excel file
write data to it
and finally save the excel file so that people can actually see
the data stored on the excel file....

i'm quite clueless.. thanks in advance

Writing To Excel
I am using the below code to write to excel, this works fine, but when i click he button again, the new data overrides the old, this is as to be expected, but how do i make it so that it automatically appears on the line below.


dim SSheet As Object

Set SSheet = CreateObject("excel.application")

'Show Excel On Screen
SSheet.Visible = False
'Add a workbook
SSheet.Workbooks.Open (App.Path & " esting.xls")
'Set the title

SSheet.Cells(2, 1).formula = txtDuration.Text
SSheet.Cells(2, 2).formula = 2
SSheet.Cells(2, 3).formula = "=B2*A2"
SSheet.Cells(2, 4).formula = 20
SSheet.Cells(2, 5).formula = "=SUM(C2:C9999)"


SSheet.ActiveWorkbook.Save
SSheet.ActiveWorkbook.Close
SSheet.Quit

Set SSheet = Nothing

Writing To Excel
I want to write these values

Write #2, "Hour", "Total Through", "Accepted", "Rejected", "Lens"

To Cells A1,B1,C1,D1 in excel from vb

Is This Possible?

Thanks
Bill

Writing DLL's For Excel In VB/VC++
Hi All

I have written a custom function in VBA (Excel) to which an Excel range is passed an a range argument. This range can contain any type of data.

Whilst this function works ok with VBA I am noticing that as the size of this range goes beyond 5000 rows and 40 columns, the perfomance becomes pretty slow. All the processing (searching and summing) for the function is done in memory using VBA arrays.

I was wondering whether rewriting the same in VC++ (using a VC++ DLL) would markedly improve the perfomance of the same. If yes, could someone guide me to an information source to write A VC++ DLL and be able to pass an array of variants to the function.


Best regards


Deepak Agarwal

Writing To Excel
Does anyone know how to make visual basic write to excel?

Writing To Excel
I am using the below code to write to excel, this works fine, but when i click he button again, the new data overrides the old, this is as to be expected, but how do i make it so that it automatically appears on the line below.


dim SSheet As Object

Set SSheet = CreateObject("excel.application")

'Show Excel On Screen
SSheet.Visible = False
'Add a workbook
SSheet.Workbooks.Open (App.Path & " esting.xls")
'Set the title

SSheet.Cells(2, 1).formula = txtDuration.Text
SSheet.Cells(2, 2).formula = 2
SSheet.Cells(2, 3).formula = "=B2*A2"
SSheet.Cells(2, 4).formula = 20
SSheet.Cells(2, 5).formula = "=SUM(C2:C9999)"


SSheet.ActiveWorkbook.Save
SSheet.ActiveWorkbook.Close
SSheet.Quit

Set SSheet = Nothing

Writing To Excel
i want to write to an .xls file from visual basic but i have a problem. I know how to do the columns, if I do:


Code:
Dim filenum As Integer
filenum = FreeFile
Open "book1.xls" For Append As #filenum
Print #filenum, "1"
Print #filenum, "2"
Close #filenum


then i open this file whch i have just written it comes up with a spreadsheet where "1" is in cell a1 and "2" is in cell a2. but i do not know how to do the rows.
i changed the sheet in excel so that "1" was in cell a1 and "2" was in cell b1 i saved this and then opened it in notepad and i get:

ab

the thing between a and b is as big as faur spaces but is all one character, EDIT: wait a minute it was as big as 4 spaces when i pasted it in the box where you write your post but when i post it it turns to as big as 1 space?? anyway this must be the character that excel uses to know that it it needs to go to the next cell on the row. i thought hey if i tell vb to write "1" then write this symbol then write "2" ten '1" should be in a1 and "2" should be in a2, but when i copy this symbol into vb it changes it into 4 spaces which doesn't work. what can i do?

Writing In Excel From VB
Hi,
I want to create an excel file. put some data in it and close it . all from a VB program . I guess its a easy thing do. but the problem is i have never done that before. so i need a simple guideline on that -

1. how to create an excel file from VB (do i need to add any reference/component needed in the process) ?
2. how to create and write in a particular cell of a particular worksheet ?

if i can do the above things i'm sure closing the file wont be a matter.

will anyone please help me with the simplest code snippet to do that ?

please please please

Asheq

Writing To Excel
Hi guys -

I got the following from vb5 help:

Dim ExcelSheet As Object

Set ExcelSheet = CreateObject("Excel.Sheet")
ExcelSheet.Application.Visible = True
ExcelSheet.Cells(1, 1).Value = "This is column A, row 1"

I get an error on the last line: "Object doesn't support this property or method." What's wrong?

Please help.

Writing To Excel
I have a button on my form called "Export to Excel". When the user clicks it, I would like a dialog box to display asking them where to save the file, and then use code in VB to write to specific cells in Excel.

I have the "Save" dialogue box working, but how do I create the Excel file where the user saved it, and how do I write to that sheet from my code in VB?

Writing VB In Excel
I'm farely new to vb. I'm trying to write some code in an Excel spreed sheet for a staffing model. Here is my scenario. I have 4 columns: rank, hire ability, hires and number hired. There are for sites. I'll call them blue,red, green and yellow. I want put a number in hires and then have the sheet calculate based on rank. The hirability determines the number of staff that can be hired in each site. I want hire 100 and the hire ability for each site is 25 then the number hired for each site would be 25. I need it to place the number available in rank 1 and then loop through the other 3. I would need a remainder box if after all sites are filled there are still some left.

Thanks

Writing Into Excel
Hey Ravindran,

Yes, it can be done. It is easier if you are in Excel, writing a macro (Alt-F11 gets you into the editor).

Do you have an array of values? In that case it would be


Code:
Public Sub test()
Dim Colum as Integer 'Column is protected, I guess
For Colum = 1 to myArray.Count
workbooks(#).sheets(#).cells(myRow,Colum).value=myArray(Colum-1)
next Colum
End Sub


In cells(row,column).value you can insert anything, for expressions access the .formula-String instead.

Hope I helped you out...

Writing To Excel From VB
I know it can be done, but...

How do I open up an Excel spreadsheet and update a cell or cells in that spreadsheet ( add to a cell's value, replace a value, etc) from a VB program?

Thanks in advance.

Bob

Writing To Excel From Vb
Hi ,

I am trying to write into excel sheet the data fetched from sql server through vb.Please tell me how to go about it.

Thanks.

Writing In Excel By VB
Hi all,

Could anyone please help me with this. I'm trying to write data in columns in Excel. Instead of getting neat separated columns, I got all my data in one column .
What coding will give what I need? In case of *.txt files what the coding will be?

With my regards,
Thanks in advance.

Writing On A Certain Row In Excel From VB6
When I use this code to write to a certain column on the next empty row in an excel file it just writes to the next empty row out of all the columns:
Code:
    Row = myworksheet.UsedRange.Columns("C").Rows.Count + 1

  For Row = Row To Row + 1
    myworksheet.Cells(Row, "C") = "Call #" & Caller3 + 1
  Next Row

How can i make it write on the last used cell in the specified column? It does it in the right column just the wrong row. Here is the whole code:
Code:
Option Explicit
'FIXIT: Declare 'myexcel' with an early-bound data type
Dim myexcel As Object
'FIXIT: Declare 'myworkbook' with an early-bound data type
Dim myworkbook As Object
'FIXIT: Declare 'myworksheet' with an early-bound data type
Dim myworksheet As Object
'FIXIT: Declare 'tmpSheet' with an early-bound data type
Dim tmpSheet As Object
Dim Row As Integer
Dim col As Integer
Dim i As Integer
Dim Caller3 As Integer

Private Sub cmdHardware_Click()
    Caller3 = 0
    myexcel.Visible = False
    myexcel.DisplayAlerts = False ' will prompt user to save file when it quit among other alerts.
          
    Row = myworksheet.UsedRange.Rows.Count + 1

  For Row = Row To Row + 1
    myworksheet.Cells(Row, "C") = "Call #" & Caller3 + 1
  Next Row
End Sub

Private Sub Form_Load()
 Set myexcel = Excel.Application
 Set myworkbook = myexcel.Workbooks.Open("c:Call.xls")
 Set myworksheet = myexcel.ActiveWorkbook.Worksheets("Sheet1")
End Sub

Private Sub Form_Unload(Cancel As Integer)
    
    myworkbook.SaveAs FileName:="C:Call.XLS", FileFormat:=xlWorkbookNormal
    myexcel.Quit
    Set tmpSheet = Nothing
    Set myworksheet = Nothing
    Set myworkbook = Nothing
    Set myexcel = Nothing
End Sub

Can you Please help!

-Billy

Writing To EXCEL From VB6
I am trying to print a Packing Slip. I wanted to use a template Excel sheet that already has the Company Logo, Header, etc. I was wondering if there is a way to open that sheet everytime and "fill out the blanks" from my VB form and How.
How can I write to a specific cell (column/row) on excel?

Thanks,

Leo077

Writing To Excel
I'm writing av VB program that writes a lot data to an excel file. Everything works just fine for a while, and then everything just locks up. I've gone through my code a million times (and it's quite uncomplicated), so I'm suspecting that there is some form of a memory leak when writing to excel. Do you know of any way to get past this. Can I somehove emty the memory that is not being used or what?

Writing In Excel From Vb
I have a problem with formatting

the recent backcolor of a cell is blue.


How can I set it to white???? from vb to my activesheet


thx to the best

Save Recordset To Excel Without Having Ms. Excel Installed
Dear All,

Any body could help me, how to save recordset to excel without having Ms. Excel installed?

many thanks in advance

Regards

Winanjaya

Writing To Excel File.. Please Help...
okay i have gotten to where i can import data into excel but the problem is when i first open up the excel file it says the file is read only and will not show the data that has been imported until i end the vb6 program.. then it will popup a box and say that the excel file is free and then it goes to import wizard...
how do i make it so that when i open the file it is read/write not just read???

VB To Excel - Need Help With Writing Data
Hey there,

First off thanks for all the wonderful information on this site. I found it to be very useful so far... Here are my issues.

I am writing an VB application for my research thesis that will write all the field data collected to an excel spread sheet for later use.

I have found the following tutorial Automating Excel from VB 6.0 which has put me in the right direction as far with opening and writing to an Excel file.. But I need help finishing it up... (I am brand new to VB… but I do have a background in web develop and PHP)

In the tutorial it explains (illustrates) how to open and close an Excel file while writing some given data to that file... What I need to do now is use the same Excel file many times, adding a new line to the file as I test each participant in my study... How do I have the program open said Excel file… scan for the next available line then write the data?

My next issue has to do with an error I have been getting when running the application on a machine that does not have Excel loaded. I received an error:


Quote:




Compile Error: Can’t find project or library




Is there a way to load the file needed onto any machine in order run he application.. or does the file get built into the application once the program is compile for production use. I’m not clear how this works…

Sorry for the essay like post… but I closing down on the deadline for this application and I need a little help…

Thank you in advance -

Help Writing Txt File From Excel.
Hi,
I wish to write records in a txt file, from macro excel.
I need to not have field separator. Like this

AAAAAAAA BBBBBBBBB CCCCCCC
DDDDD EEE FFF

This is my code but I find all record saved in only one line, like this

AAAAAAAA BBBBBBBBB CCCCCCC DDDDD EEE FFF

my code:
Public Type clientrecord
rec1 As String * 5
rec2 As String * 10
End Type

Dim recfile As clientrecord

Open "C:TMPTESTFILE.txt" For Random Shared As #1 Len = Len(recfile)
RecordNumber = 1
recfile.rec1 = "aaaaaa"
recfile.rec2 = "DDDDDDDDDDD"
Put #1, RecordNumber, recfile
RecordNumber = 2
recfile.rec1 = "cccc"
recfile.rec2 = "ffffffffff"
Put #1, RecordNumber, recfile
Close #1

Any idea?
Thanks in advance for your kind cooperation.
Regards,

Giovanni

Writing To An Excel File?
I'm trying to write on an excel file that should have about 10 coumns. What I've been getting is that instead of 10 neat columns of data, everything is lumped into one. How can I write to an XLS file and put each piece in the correct column? Can't say I've ever had to write to an XLS file before, so I'm pretty much shooting in the dark here.

Thanks.

Vb App Crashes After Writing To Excel
The code below works great with Windows XP and newer version of Office. When I run the same code on Win 2K w/ Office 2K, my VB APP crashes and creates an error log containing:

"The application, , generated an application error The error occurred on 10/21/2004 @ 15:08:28.666 The exception generated was c0000005 at address 1F467D6A (<nosymbols>)
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Application exception occurredpid= 548).Exception number: c0000005 (access violatation)"

This means nothing to me... Please Help!!



Code:
Sub export_Excel(excel_rs As ADODB.Recordset)

Dim intcounter As Integer

Dim objExcel As New Excel.Application

datevar = Date
datevar = Replace$(datevar, "/", "-")

objExcel.Workbooks.add
objExcel.Sheets("sheet2").Delete
objExcel.Sheets("sheet3").Delete
objExcel.Sheets("sheet1").Name = datevar

For intcounter = 1 To excel_rs.Fields.count

objExcel.Sheets(datevar).Cells(1, intcounter).Value = excel_rs.Fields(intcounter - 1).Name

Next

introw = 2

While Not excel_rs.EOF

For intcounter = 1 To excel_rs.Fields.count
objExcel.Sheets(datevar).Cells(introw, intcounter).Value = excel_rs.Fields(intcounter - 1).Value
Next
introw = introw + 1
excel_rs.MoveNext

Wend

objExcel.Visible = True

If Form4.Combo3 = "-- Export to Excel (T.O.)" Then
datevar = "H:HOMEKENCOILSTATSHIT LIST" & datevar & " ORDERS"
ElseIf Form4.Combo3 = "-- Export to Excel (T.Q.)" Then
datevar = "H:HOMEKENCOILSTATSDAILY ACTIVITY" & datevar & " QUOTES"
End If

objExcel.ActiveWorkbook.SaveAs (datevar)

Set objExcel = Nothing

End Sub

Writing Macros For Excel In VB6
Hi All

I am trying to format numbers in excel e.g. having two decimal places, or applying a function to a certain column.

Anyone with any ideas or code examples plz help.

Example of what I mean

Product Selling_Price Cost_Price

Calamari 7899 1422
Venison 1234 1011
Prawns 1563 1112


I want it to look like this after running my function in vb

Product Selling_Price Cost_Price Profit

Calamari 78.99 14.22 64.77
Venison 12.34 10.11 2.23
Prawns 15.63 11.12 11.12

Writing Data From VB To Excel
Here is my problem...I am writing a program for my wife to use at work for managing time sheets for pay. What I've written is a time time clock and payment calculator. I want to send that data to an excel spread sheet-each time just continuing with a new row. I can find the last row in the spreadsheet but I can't use a variable to tell the program where to send the info. Example- I want the Date to go to column "A" but the row number will be different each time so I need to place a variable there-this is where I am stuck. By the way I am a novice and any help would be greatly appreciated!!

Writing To An Excel File
Hi Guys! Anyone of you here who can show me a simple code that can write to an Excel file? The data should come from SQL Server and writes to the columns of Excel file(i.e. Membership_ID, First_Name, Family_Name, etc.).

Thank you.

Writing A 2D Array To Excel.
I'm having trouble writing a 2D array from a VB application to an excel datasheet.

First some info on the program. Basically it displays a form whereby the user has to click a command button in order to gain points. There are 3 presentaions of the same form (separated by another but I don't think it's relevant) and the program saves the time between each click to a 2D array.

I've set up a 2D array (below) that stores the response time of the user.


Where clickcount = the number of clicks in that particular presentation of the form, VRTrialcount = the form presentation number (1 to 3) and RTime = the time between the current click and the last one.


VB Code:
ReDim Preserve VR_Response_Time_Array(1 To 3, 1 To clickcount)    VR_Response_Time_Array(VRTrialcount, clickcount) = RTime


The number of times the button is clicked on in each run of the form may vary but an example of the kind of output I need is below.

Form 1, Form 2, Form 3
ClickNo1 1s 2s 9s
ClickNo2 4s 2s 4s
ClickNo3 1s 5s
ClickNo4 3s

The array seems to work fine during the VB program but when the excel file opens at the end of the program it doesn't write the full array to excel. Instead, based on the above example I get:


Form 1, Form 2, Form 3
ClickNo1 1s 2s 9s
ClickNo2 4s
ClickNo3 5s
ClickNo4 3s

The code I'm using to write the array to excel is:

VB Code:
For c = 1 To VRTrialcount ' c = idex of the 3 forms            For x = 1 To VR_ClickTrials(c)                With xlsApp                .Cells(1 + x, 2 + c).Value = VR_Response_Time_Array(c, x) ' x = number of clicks made in c                End With            Next x        Next c



Can anyone help me out with this? Where the devil am I going wrong?!

Thanks.

Please Help..! Writing A VB Exe And An Excel Macro
Hi everyone,

Someone asked me a this question... What's the difference between writing a VB program and a macro using in-built VB editor? How should I answer him accurately and professionally?

In addition, I understand that we need to create setup.exe to distribute our VB program so that all component and library files are copied to the user's system folder. Do we need to copy these files to user machine for an Excel macro to run properly?

Thank you in advance.

Writing To An Excel File
hi
i'm trying to write a simple stock tracking program that will read and write to an excel spreadsheet.

i've got the reading part down.. i cant find any examples on how to write to a excel spreadsheet.

any suggestions, links, or ideas?

thanks
darrell miller

Writing To Excel Worksheet
Hi,

I urgently need your help. I am currently working on a project in which I have to let the user view data in an excel worksheet from Visual Basic. I would also like the user add a record to this worksheet. How would I accomplish this.

Your help is greatly appreciated.

Thanks,
Tim

Excel- Writing A Macro
Hi,

I need to copy data from Sheet A (NewPrices) to Sheet B (Prices). I will need to search for the exact text match (row 2 in Prices) in NewPrices. The number from NewPrices Column 2 will need to be copied in Prices (a new row will need to be inserted).

This will need to be done for the 4 text strings (PCM,ART,VOD & MIL) as per Prices

How easy is this to do?

Can anybody forward a program or solution??

Prices will have 5000 columns and NewPrices will have 5000 rows. So quite a large sheet will be required!!

Thank you

Writing To A File From Excel
What I want to do is open a text file and write a string of text to it based on conditions in my excel spreadsheet.

For example, if a spreadsheet cell contains a "1", my script would open a text file and write a line to it, copying data from my spreadsheet to the text file.

This is my first VB script, so please keep it simple . Thanks!

Reading/writing Excel Via ADO
I am hoping someone can point the way to reading and writing to an Excel file via ADO. Am using an automated test tool who's scripting language is VBA (Compuware's Test Partner) - I can do these things by instantiating various Excel objects (workbook, worksheet, etc.) but it's tightly coupled to specific things being in certain columns so everything is referenced as (row,column) - so if the spreadsheet layout changes it is hosed up. I know www.connectionstrings.com has an example of connecting to Excel via ADO, I just don't know what things are like after that point....

any thoughts appreciated,

fv

Writing Excel Macro Help
This is my current excel SaveAs macro. Write now it takes a name (store at A2) and adds an underscore then appends a time. These are all stored in the CDH_Patients directory.

Sub FileSaveAs()
ActiveWorkbook.SaveAs fileName:="I:SURGERYCDH_Patients" & Range("'Sheet1'!A2") & "_" & Range("'Sheet1'!B2").Formula, _
FileFormat:=xlNormal, _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

What I want to do instead, is first see if there is a directory in I:SurgeryCDH_Patients with the name of the patient in A2. Then, if there is that directory, save in that directory. Otherwise, I want to create a directory with that name, and then save.

It seems like this would be pretty simple. Any help? Code?

Thanks.

Writing Function For Excel
1. can i write a function of my own definition to be used in the excel chart?

2. as i am a laywoman in VB, how would i write a function where i choose an array of data (lets say, a row) and then the function calculate the average of every 3rd number:
if i choose a row A1:I1, it does: average (A1,D1,G1).

i would really appreciate your help.

Writing To Excel Spreadsheet
I have previously written code to read data from an Excel spreadsheet using an ADO approach with a connection set up as follows:
CODE    With SSConnect
      .CursorLocation = adUseClient
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & SSFile & _
                          ";Extended Properties=""Excel 8.0; HDR=No"";"
      .Open
    End With

Writing To A Excel Spreadsheet
Hello all Guru's
Listed below is my code to open a excel spread sheet and it needs to write to it. I can open the excel but I cannot write to it.

Please help

Tristan Wolf helped alot with the code, but the example he gave did not prove the writing to excel part.

Option Explicit
Private Const conPath As String = "C: empTest.xls" ' ADDED LINE

Private app As Excel.Application
Private wbk As Excel.Workbook ' ADDED LINE
Dim mExcel As Excel.Application

Private Sub cmdWrite_Click()
    Set app = New Excel.Application
    Let app.Visible = True
    Set wbk = app.Workbooks.Add
    Call mExcel.Selection.TypeText(txtInput.Text) **********>>>>This is where it fails
    wbk.SaveAs conPath ' ADDED LINE
    Call mExcel.Quit
End Sub

Private Sub Class_Terminate()
    Set mExcel = Nothing
End Sub



Edited by - fbarba on 6/12/2003 9:59:07 AM

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