Solved

Is this possible, what I'd like to do with Excel spreadsheet with VB?

Posted on 2004-08-13
10
139 Views
Last Modified: 2010-05-02
Hi all,
I currently do this manually and I was wondering if I can automate it.
------------------
What I do manually:
* export a txt file with 3 types of rows (active, pending, sold) from VB program.
* open the txt file, tell it what delimiter etc into a spreadsheet
* open a Template Excel spreadsheet with formatted titles, formulas etc.
* copy "active" type rows (0 to N rows).
* paste those rows to a line following the title "active" in column A
* copy formula line to those rows just pasted
* repeat for other 2 types of rows (pending, sold).

100 points for a believable good yes/no answer with some details.
Name your points to actually give me some code to do this.
Thanks,
Phil
0
Comment
Question by:spoowiz
  • 5
  • 5
10 Comments
 
LVL 8

Expert Comment

by:SNilsson
ID: 11795563

You can try this program (30 days free) and see if it can mimic your steps:

http://www.macroexpress.com/
0
 

Author Comment

by:spoowiz
ID: 11795914
thanks snilsson, but i don't think it'll work.
i have an unknown number of rows to copy/paste.
i think what i need is a VB code to manipulate the "template spreadsheet".
0
 
LVL 8

Expert Comment

by:SNilsson
ID: 11796017


Maybe you can open the exported text file via VBA (Excel) and insert the row's in the .Xla file, do you know how to code in VBA ?
0
 

Author Comment

by:spoowiz
ID: 11796129
no, don't know vba, but willing to learn.
however, seems like if vba can do it, i would think vb can also; and directly from recordset values instead using an intermediary txt file.
0
 
LVL 8

Expert Comment

by:SNilsson
ID: 11796526

"i would think vb can also; and directly from recordset values instead using an intermediary txt file."

Absolutely, no problem there, post the code that create the textfile and I can start modify it to Excel export.


You say 'recordset values' seems like you have access to the database.
If so you can get the values directly from the database, there really is no need to involve VB, unless other operations is done in the VB program.



0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:spoowiz
ID: 11796839
Yes, I get values from the database, but need to do calculations etc for the spreadsheet.
Since the end-result spreadsheet needs a lot of formatting and formulas, I think the "template" idea would work best.
What I'd need to do using VB is:
1. Open the template spreadsheet
2. Search for a row with string "Active" in column A.
3. Insert as many rows as there is from the database/calculated values under that row.
(I'd need to save the row numbers so I can apply the formatting/formulas to those rows, hopefully from another row in template.)
4. Copy a row from the template and "paste special... formatting and formula" to the rows added in #3.

Repeat #2-4 with string "Pending" and "Sold".

Name your points if you can give me a basic code able to accomplish the above. This will not only be useful, it'll be a great learning sample for me. Thanks.
p.s. Need to leave for the afternoon.
0
 
LVL 8

Accepted Solution

by:
SNilsson earned 100 total points
ID: 11798917

Okey, first you will need a reference in VB to  have a reference to the Microsoft Excel Object.


Dim oExcel as Excel.Application
Dim lngRetVal As Long
Dim strfile As String

strfile = "MyTemplate.xls"   ' xla maybe ?

Set oExcel = New Excel.Application

oExcel .Workbooks.Open strfile

____________________________________________________________

Next you need to find the row with the value 'Active' in col a (on sheet ?)

____________________________________________________________
Dim oCell As Excel.Range
Dim lRow As Long
Dim iCounter As Integer

For Each oCell In Range("A:A")
    If oCell.Value = "Active" Then
        oCell.Select
        lRow = oCell.Row
        Exit For
    End If
Next


Do Until myRecordset.EOF
    lRow = lRow + 1
    Range("A" & lRow).Activate
    ActiveCell.Value = myRecordset("MyDBcolumn").Value
Loop


Let's start with this for now, and once you got it to work we move on to the copy/paste and the other columns, I would really like to have a look on the vb code that create the text file also, so I can help you integrate the new code.
(I dont have VB installed right now, so be patient in case of errors I cant test it)
 
0
 

Author Comment

by:spoowiz
ID: 11800752
sn, thanks for taking the time. i'm getting ready to go away for 1.5 weeks, so can't do anything now. will do when i get back.
0
 
LVL 8

Expert Comment

by:SNilsson
ID: 11800920

Allrighty, have good trip and see you then.
0
 

Author Comment

by:spoowiz
ID: 11991919
snilsson, i'm back, however, as i was away, i decided to accomplish my task using another method. i'm giving you points anyway because i've learned something from your code. thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now