Putting values on Excel Spreadsheet cells via VBscript 1

[SiouxCityElvis]

I'm trying to call the 2nd sheet in my workbook and populate it based off a loan determination completed in the 1st sheet of my workbook.

Basically, I'm using another engine that does all the math but the example they gave for amortization was using a standalone VB app. I want to use the Excel with VB.

Any way that I can pinpoint a cell from my VB script that works with my Excel? For example, I would like to assign a value to Row 13 Column 3? It seems like something rather simple in concept, but I don't know how to do that from my VB script.

Please help...
Thanks.
-David

 

[sfvb]

Here's an example that will create an Excel file at c:\vbsExcelTest.xls:
(It assumes that you haven't gone to Tools-->Options and changed the Sheets in new workbook: value on the General tab to just 1, and also, that you don't have a file called vbsExcelTest.xls on your C drive.)

Dim objApp
Dim objWorkBook

Set objApp = CreateObject("Excel.Application")
Set objWorkBook = objApp.Workbooks.Add

objApp.Application.Visible = False
objApp.Worksheets(2).cells(13,3).Value = "This is C13"
objApp.Worksheets(2).SaveAs("C:\vbsExcelTest.xls")

objWorkBook.Close

set objWorkBook = nothing
set objApp = nothing

 

[DrSmyth]

Been looking for some code like this for ages sfvb...

Is it possible to use a template for the new workbook and would the code be similar for creating a new word document. Basically, i've got people entering information into a worksheet which i want to insert into a word template...

 

[SiouxCityElvis]

I already have the Excel Document. I am writing the VBscript from within that Excel. It appears your code example above is for a VB standalone app where you first create the Excel document before you start writing to it.

I am just curious how to write something to a specific cell. Most of the posts I've looked through show how to highlight cells and work with cells that already have data in them.

What my challenge is, is to do a loan calculation and based off that calculation populate a sheet of amortization of payments which will be many many rows long.

The math part of the loan calc and amortization is taken care of for me, as I'm using a software engine that our company has; we have capability to connect to the engine through Excel. Problem is, I don't understand and haven't worked on Excel Macros ever. I can tell the learning curve on it so far is much tougher than Javascript ever was though.

Thanks in advance.
-David

 

[SQLBI]

Elvis,

To write to a specific cell, reference the Active worksheet and assign the cell a value.

Sheet1.Activate
ActiveSheet.Cells(x, y).Value = ??? 'Cells(Row, Column) A1 = Cells(1, 1)

It's really that simple.



Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[sfvb]

AmarilloElvis,

I thought you wanted a VBScript that wrote to a specific cell in Excel. The line from the VBScript I posted:

objApp.Worksheets(2).cells(13,3).Value = "This is C13"

writes to the cell and worksheet you specified.

If you wanted to use a macro in Excel, then leighMoore's example will work.

 

[sfvb]

DrSmyth,

Yes, the code for creating a Word is somewhat similar.
I'm sure you could code for a specific template, although I've never tried.

 

[DrSmyth]

Cheers, have posted a new thread to see if anybody knows about using a specific template....

 

[SiouxCityElvis]

Okay, I found that it was easier to use Sub rather than Function. And the above code works.

Thanks.

 

[xlbo]

AmarilloElvis - it is not just easier, it is the only way. Functions cannot change anything except the cell they reside in (if they are used in a worksheet) - only a sub can change a cell etc

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Phasler]

I'm interest in trapping "soft line" breaks where the text has wrapped without the user entering a carriage return, is this possible

 

[xlbo]

How on earth is that relevant to this thread - please start a new thread phasler

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Phasler]

Sorry, I post this to the wrong thread my appologies

 

[ahyinfa]

Hi,

Can somebody (maybe sfvb?) please direct me on how I can export values from access to an excel worksheet & then automatically create a graph for it using VBA?

Thanks,
RK

 

[xlbo]

ahyinfa - start a new thread - did you not even bother to look at the rest of this thread. There is already one response from me along exactly the same lines

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244