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

How To Insert Line In Excel Using VBA

I want to place a horizontal line in Excel using VBA.Actually iam working with Excel object in VB.Iam able to insert a vertical line with some code but with other options iam not able to insert horizontal line.Can anyone please help me out with this.
Here is the code for the vertical line

Worksheets("Sheet1").Range("A1:G1").Borders(xlDiagonalUp).Color = RGB(255, 195, 0)

Also i want to insert page numbers in the footer like

Page i of n.

Using VBA can anyone tell me as what code to write to display the page numbers.

I will be very much thankful to you if you can give solution to these.



View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
How To Load Excel Data Into ASP Page Den Insert Each Line Into Access One By One?
how to open an existing excel file with multiple columns and rows and den insert it into ASP form den upload each record into database. Had to do it in this way cos when upload to database, it require to perform other tasks.

Or how to open an excel file and load its contents directly into an existing access table?

Please provide me the code to teach me. Thanks!

Need VB Code To Insert A Row For A Subscripted Line In Excel & Code To Search For Amounts On One Sheet And Apply Them To Certain Area On Another Sheet
I am in the process of developing an Excel spreadsheet and need assistance
in adding visual basic code to automate it.

The first thing I am trying to do is to create a macro where a user can
click on a certain button, and the program will insert a row at a specific
row and will copy all formulas associated with the row above it. For
instance, if you have a spreadsheet that has 10 rows and 3 columns in it,
you can insert a subscript line below row 5 and call it 5.1. When the
subscripted row "5.1" is added, it will include the same formulas as in row
5, which would add columns 1 and 2 in column 3.

The second thing I am trying to do is have some code that if a user enters
information on Sheet B that needs to also be included on Sheet A, the code
will search Sheet B to see it an entry made on Sheet B should be carried to
a particular row in Sheet A.

I would greatly appreciate any feedback anyone can give me regarding this.
Thanks in advance.

Insert Line In RTB
I've searched the Forum and there's a lot of info on inserting lines of text but none that do what I need. Perhaps someone knows how.

I'm loading an .htm file in a RichTextBox. What I want to do is insert MyString on it's own line just before the <Title> tag like this:


Not like this:

Anyone know how? Thanks!

Insert Line In Next Available Cell

I have now tried a million things without result so I really hope someone can help me.

My roadmap is split into four quarters; "Qu1","Qu2","Qu3" and "Qu4". I need excel to find for example "Qu4" (which moves as I delete/insert lines - so it has to search the content of the cell) then find the next available cell under it and then insert a new line.

Does anyone have an idea?

Thanx in advance,

Insert Line Like An Object
ive read how to draw a line in a form with the finction Line, but im looking to insert an object line , like i do in ms office.
do someone know where i can find a drawing toolbar?

thanks all
greetigs from argentina


HoW To Insert A Line At Run Time??

i have a problem here in my program..
i need to insert a line (and deal with it as an object) at the run time by clicking on a command button for example..

and at every time the user click on the command button, a line appears in my form.. its parameter is taken from text boxes in other form(x1,x2,y1,y2)...

in other word:
how to insert an infinete number of control boxes(a line
for example) to my form at run time by the user..??

i know it is a hard problem but please help???
i need it for my project!!

Insert New Line In Text Box
Is there any way to force a new line in the text box? I tried using 'Chr(13)' but all it did was put in a black box ! Please help!

HoW To Insert A Line At Run Time??

i have a problem here in my program..
i need to insert a line (and deal with it as an object) at the run time by clicking on a command button for example..

and at every time the user click on the command button, a line appears in my form.. its parameter is taken from text boxes in other form(x1,x2,y1,y2)...

in other word:
how to insert an infinete number of control boxes(a line
for example) to my form at run time by the user..??

i know it is a hard problem but please help???
i need it for my project!!

How Can I Insert A Line In A ListView ?
How can I insert a line in a ListView ?

Is it possible to make it without shift all the lines after where I would like to insert the new line ?
By using SendMessage ?


How To Insert A Line In Word?
Hi All,
Please solve the following problem for me.

I want to insert a new picture of line in my word document through word. how can i do that ??
is it possible for me to format the content of the data as follows??
name : Ramanjaneyulu Narra
father's name : Hanumantha Rao
address : Inida
__________________________________________________(this is the line picture i want to insert)
Education : B.Tech
Branch :CSE
Year of Passing :2005

when i tried to do this using the spaces i was not able to do it completely.

Please solve the above 2 probelms for me as soon as possible.
I will be greatful to all those who responds to this mail.

Ramanjaneyulu Narra

Insert A Line Break
Hi All,

I am trying to make a string formula to be used in my report. This contains of a client's address. This address field has 2 fields that i need to use.

This is how i am making the formula:

Code:Shared StringVar textAddress;
        textAddress:=textAddress + ', ' + {QRY_MORTCASEPROGRESS.PROPERTYADDESSLINE2};

in the second line instead of " + ', ' + " I want to insert a break in line. Something like vb's vbcrlf .

Can someone tell me how to go about it?


I Cannot Find The Problem With This INSERT INTO Line...
Set recordset1 = database1.Execute("INSERT INTO clips(Name, Size, Format, Source, Filename) VALUES('" & strname1 & "', '" & strsize1 & "', '" & strformat1 & "', '" & strsource1 & "', '" & strfilename1 & "')")
all of the variables are strings.. and all the names match exactly to the database.. am i missing something small?

How To Insert Bar(line) In Rich Text Box
please help me how to insert bar in the rich text box
thank you

Modify And Insert Line In Listbox
I don't know if this is possible but I'm trying to modify all existing entries in a Listbox and add them after each entry.

So if I have these entries:

It would modify and add after each entry and the results would be this:

If anyone can help, thanks

How Can I Insert A Letter Or Character At End Of Each Line.
can some one please help me?

I have a bunch of text files that I am trying to clean up junk out of. I have found a script that will remove any line that has a certain word, i am looking for a script to remove specific text out of single or a bunch of text files. i am also looking for a script that I can enter any character at the end of each line in a text file.

I am VERY new to scripting and programming, so take it easy on me.

thank you in advance for your help

Matching Line In 2 Sheet And Insert In Other...


RATE and SERVIZIO have a unique index into column AC to identify a line.


If a line in RATE, indentified with index AC, not is present into SERVIZIO with the same index, delete the line from RATE and insert into DEFINITE
Matching for entire range of line
this for all line...

Insert The Horizontal Line In A Popup Menu
Im trying to figure out how to insert the horizontal line in a popup menu (like when your in your browser and click on Tools. The line between Windows Update and Messenger) and how to underscore the alt commands.

Thanks All,

How To Insert Text At Given Column And Line Number?

I my cursor is at line 7 , and text1.selstart = 77, I have blank line at 6 and then text line at 5...... I want to insert some text exactly above the line7 at same selstart. How can I do that?
I did as follows
I got Col and line from Sendmessage API and now I want to insert my text string at that point? how can I do that?

j = CurrentColumn(box)
i = GetTextboxLine(box)
textbox1.SelStart = x - 40

box.SelText = "sfsdfsf"

I tried random number mine from old X. but its not solution.

Please help


VBScript - Insert Text At Beginning Of Each Line
I have a file with the following example data


I would like to be able to insert "02," at the beginning of each line. Is this possible and if so, can some example code please be provided?

Insert Mark Of The Web Into First Line Of HTML Pages
Hey there, I have several hundred HTML pages that need to have the MOTW code inserted into the first line of the HTML page. So far I haven't been able to do that successfully. Any suggestions?
Here's the code I'm using now:

Code:Const str = "<!-- saved from url=(0014)about:internet -->"
Set objFSO = CreateObject("Scripting.FileSystemObject")
strComputer = "."
Set objWMIService = GetObject("winmgmts:\" & strComputer & "
Set colFiles = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='<YOUR PATH HERE>'} Where " _
        & "ResultClass = CIM_DataFile")
For Each objLogFile In colFiles
    If objLogFile.Extension = "html" Then
        strFile = objLogFile.Name
        Set objFile = objFSO.OpenTextFile(strFile, 1)
        line = objFile.readLine
        If InStr(str, line) = 0 Then
         Set objFile = objFSO.OpenTextFile(strFile, 1)
         text = objFile.readAll
         Set objFile = objFSO.OpenTextFile(strFile, 2)
         objFile.Write str & VbCrLf & text
        End If
      End If
MsgBox "Done"

The bolded line is where it keeps ending due to error. I'm not sure how to get around this problem.

Word Automation - How To Insert A Line Break?
I am using Word Automation to create a Word document on the fly.
I do this by using a Word template and replacing keywords with text from my VB6 app.

Dear [customer],

My app replaces [customer] with the actual name and it becomes:

Dear Mr Smith,

This works fine but I have a need to replace some of the keywords with multi-line text.
Again, this works ok in the body of the document by simply using vbCr's for line breaks.
My problem is that if I use a vbCr in a Word Table, it interprets the vbCr as an unprintable character and displays an empty square character!

Does anyone know a way round this?!

My customer doesn't like having continous text and I don't particularly want to have to lose the table.



Can I Query Data From Multi Excel Tables And Insert Them Into Another New Excel Table
Hi everyone,

Can I query data from multi excel tables and insert them into another new excel table via VC?

i.e. I have excel table A, and excel table B, they have one same column "num"

I hope I can join these two tables into one new excel table via this public coulmn, all the steps should be finished via VC (please tell me if you have other suggestion about the program tool ^_^)

Seems that the function of "Microsoft query" in the Excel is OK, But I hope I can do a good UI and user don't need to write sql by himself, so it is better if you can tell some reference about how to query from muti-tables (I think it should be muti-datasource) and insert this result to another excel table,Thanks a lot :-)

AND how to implement it? please give me more doc if it is possible, Thanks!

I'm not familar with sql syntax about excel, could you please tell more about this? thanks!


** RESOLVED ** Insert Blank Line In Middle Of Flexgrid ?
If yuo read thru a flexgrid, line by line, is it possible on change of text in column 0 to add ablank line at that point, rather than at the end of the flexgrid ?

For example ....

I have :


...... and I want it to be :



Is it possible to add lines in the middle of a populated flexgrid ? or am I stuffed ?

Insert New Line In Tab Dialog Control Caption (unresolved)
How can I split the caption for a tab in the Microsoft Tab Dialog Control (SSTab1)? I want to have a tab's caption on two seperate lines but when I put
sstab1.TabCaption(0) = "Hello" & vbnewline & "you"

Instead of a new line being put in the tab's caption, the vbnewline ASCII character is returned // and the caption becomes "Hello//you"

Is there any way around this? I'm thinking there isn't, but you never know!

Edited by - Marce22 on 5/2/2005 10:49:58 AM

How To Insert A New Line Into An Alphabetically Ordered Text File
Hi there,

I have an access database (2000) and one of the functionalities I must provide is to allow a user to insert a line into a text file that is alphabetically ordered. I have many, many cfg (text) files that contain information on each line in alphabetical order. At times, I need to insert an additional line to these files, but they must remain in alpabetical order. I am trying to figure out the best way to do it, and am wondering if this would work:

1. Open the .cfg file for input
2. read each line and insert it into a temporary table
3. insert my new line into the temp table as well
4. select from the table in alphabetical order and re-write each line in the file

So this is where I'm stuck... I'm not sure how to do this. I know I can have a SELECT statement ordered alphabetically, but I'm not sure how to take each item returned from the select statement and write it to the file...

I really hope this makes sense to someone out there!!

Thanks so much in advance if anyone has any ideas!!!

Count Number Of Lines In A Project And Insert Line Numbers
Please do not post anything if you don't want to download it.

Please test my Add-in Dll to vb which counts the number of lines, adds Line numbers to module and also removes them.
You can select which module you want to add the lines and also which modules you want to count.
Just download the dll and register.

Please post any feedback.

Thanks for testing.

PS Anyone posting REAL feedback will get the final release for free.

How To Insert A Small Picture/text Line In The Middle Of MDI Form
I want to insert a small picture or a text line in the middle of a MDI form. Can someone suggest me the way?


Read Line By Line Of Text File And Move Content To Excel Record By Record
   i am trying to move the selective information in the text file to Excel sheet record by record using VB.Can u help me in this regard.

The contents in text file is like:


The above information need to be moved in Excel sheet record by record as:           

Please do help in this regard as there are more than 1000 information that has to moved in the above pattern.



Insert From Excel
I was wondering if there is a way of reading a textual Excel file. More specifially this file will constin a list of names which I want to enter them in my database. Carefull though! I do it through VB ( and not through Access where I can use macros) .

Everything should be simple. But not simpler! Al. Einstein

Insert Row In Excel From VB6
First, thanks Si_the_geek for the awesome tutorial on automating Excel through VB6.

In my code, I want to insert a new row on a excel sheet. This wasn't covered in the tutorial, and after some searching I couldn't find it in other posts.

I recorded a macro in excel of inserting a new row at row 2 (this is where I want it to go).

Here is the code it gave me, but I can't seem to get it to work from my code in VB6.

Any suggestions?

Sub InsertRow()'' InsertRow Macro'' Rows("2:2").Select Selection.Insert Shift:=xlDownEnd Sub

Insert A Row In Excel
I am attempting to update an Excel spreadsheet by inserting a row. I have tried a couple of methods that are contained in this forum to no success. I have a memory variable called RW in which the active row number is in, but when I attempt to use the variable in an insert, I get an error message about not being able to support the operation. Basically the few lines I have problems with are:

If (xl.Application.Workbooks(1).Worksheets("task_table1").Cells(rw, s)) = " " Then
xl2.Application.Workbooks(2).Worksheets("SCHEDULE START").Activate
xl2.Application.Workbooks(2).Worksheets("SCHEDULE START").Cells(rw, s) = " "
Selection.Insert Shift:=xlDown
End If

Any help?

Excel/insert Into...
hi all,
i have got an excel file wich contents datas. i have to insert those datas into a table in sql server2000 via VB.
is anyone has an idea how to do ir? (i used to it with a text file but i don' t to do with an excel file)
thankxxxxxxxx 4 ur help

Insert User Name In Excel
I can't figure out how to automatically display the user name in Excel. In word it is easy, just insert a field displaying the user name. The user name I am refering to is the user that Excel and Word are registered to. This can be found under Tools-Options-General Tab.

Does anyone know that code to assign a cells value to the user name?

Insert Macro In Excel From Vb
Hi all

Can anyone give some ideas or tutorials on how to insert a macro into excell from vb without making the excel app visible.

I want to build a report with excell using data from vb. I want the macro to format the excel worksheet to look like the report i want to print.

Any ideas or links to tutorial would be grteatly appreciated.

Thanks in advance

Insert Data To Excel

i have a recordset with some data from an sqlserver

i want to insert that data to an excel sheet..

the excel sheet already contains some data in the first two

rows. i want to insert the data from recordset without losing

the exisiting data.

please help

Insert Rows Into Excel
Hi all,

Please show me how to insert row(s) after the current row into Excel sheet using VB code.

Thanks a lot.

Insert Formula In Excel With VBA
I want to use VBA to insert a formula in a cell. It depends on the value of another cell wether or not to formula should be inserted.
The formula works just fine when i manually insert it in the cell., but i always get an error when i try it with VBA.

My code is something like this:

Do while cells(2,i).value <> ""
cells(1,i).value = "=VERSCHUIVING($A$1;VERGELIJKEN((D3);$A$2:$A$1000; 0);1)"
if i>100
then exit do
end if

When i use a simple formula like =sum() then i get no error.
It's just with more complex formulas that i receive this error.

Any help is welcome

How To Used Excel VB To Doing Auto-insert X(+1)
how to used Excel VB to doing Auto-insert x(+1)

How to easy used EXCEL Visual Basic to doing this :

TextBox1 = CountID = "3"
TextBox2 = enter value
TextBox3 ...
TextBox4 ...
TextBox5 ...
TextBox6 ...
CommandButton1 = Confirm
CommandButton2 = Next Page

When I enter some data (TextBox1,2,3,4,5,6...) and last press (CommandButton1)
Can automatic return that (TextBox1,2,3,4,5,6...) to Sheet5! x 3
Sheet5! x C & (TextBox1)

TextBox1 = Sheet5!C3
TextBox2 = Sheet5!D3
TextBox3 = Sheet5!E3
TextBox4 = Sheet5!F3
TextBox5 = Sheet5!G3
TextBox6 = Sheet5!H3

When I Press (CommandButton2)
When I enter some data (TextBox1,2,3,4,5,6...) and last press (CommandButton1)
Can automatic return that (TextBox1,2,3,4,5,6...) to Sheet5

But now is push to Sheet5! x 4
TextBox1 = Sheet5!C4
TextBox2 = Sheet5!D4
TextBox3 = Sheet5!E4
TextBox4 = Sheet5!F4
TextBox5 = Sheet5!G4
TextBox6 = Sheet5!H4


Any idea Please!

Excel Insert A Row Automatically
Hello Everyone,
I have a problem with Excel automation.
I have huge spreadsheets of database. which are all related.
My problem is when I insert a row and type some informaion in one sheet for ex: Sheet3,
the same shoud be done in another sheet ex: Sheet2 automatically, without me going and doing it manually.
I tried it with Macros but it's not that effective.
Any suggestions and guidance is highly appreciated.
Thanks in advance.

Can We Insert A Picture Into Excel?
I wanna insert a picture into cells(1,1) and I created a macro like this:
When I run the macro, the dialog pop up..then I don't know how to code after that...Can anyone Help me Please!

Insert Form Excel
I was wondering if there is a way of reading a textual Excel file. More specifially this file will constin a list of names which I want to enter them in my database. Carefull though! I do it through VB ( and not through Access where I can use macros) .

Everything should be simple. But not simpler! Al. Einstein

Insert Borders In Excel Using Vb
hi all,

im trying to insert borders around the data that i will paste from my vb grid.
im using this code to paste the data on my grid to excel...

VB Code:
Clipboard.Clear                With grdProject                .Col = 1                .ColSel = 1                .Row = 1                .RowSel = .Rows - 1                Clipboard.SetText .Clip                End With                With xlSheet                .Range("A3").Select               .Paste                Clipboard.Clear                End With

how can i use the function

VB Code:

and border around all the data that i will paste?



Insert Pic Into Cell In Excel
Hi again

How can i insert a pic into a certain cell in my excel sheet? I can insert text using this code

VB Code:
oApp.Sheets(1).Range("D" & Iiloop).Value = "Hello"

But what would be the code for inserting an image?



Excel Vb Insert Text
i have a spreadsheet

i want to insert a ' into every row in column a

i guess i have to use a loop like

do until end

insert '


but i dont really know what objects i can use

Insert Cell Into Excel In VB
Hi all,

I am currently trying to delete and insert cells into excel via VB when a certain condition is true.

I am able to delete a cell using

XLcells(row, col).delete

but when i use the same principle for inserting a cell

XL.cells(row , col).insert

it doesnt work. I do not wish to enter anything into that particular cells, but to just leave it blank.


Insert Row In The Sheet Of The Excel

How do I insert a row in the Excel using VB and keeping the configuration of the cell that is the cursor ?

thank you in advance

Insert An Excel Macro From VB
Hi all !!

Im creating a new xls file. I need to insert a macro on it from vb before saving and closing the file.

Heres how I create the object:

Dim X as Object
Set X = CreateObject("Excel.Sheet")

HELP !!!
Any ideas???

How To Insert Timer In Excel
Can i activate a timer in excel to make a flashing title bar because i was trying out one of the vb hints "how to make the title bar Flash" and i could not find a timer in excel.

I am new to VB and would appreiciate all the help that u could offer me cheers.

How To Insert Blank Row In Excel Via VB?
Hi all, I have folowing code, and I want it to insert 2 blank rows before new bank name appears in excelsheet. Output should be like this in Excel:

blank row
blank row
blank row
blank row

Dim p As Integer
Dim getTT As String

' select all bank names from BANK_ACCOUNT table

Set rs = cn.Execute(getReport)

Do While Not rs.EOF ' Loop the Bank name from Bank_Account

For p = 0 To rs.EOF

getTT = rs.Fields(p)

Dim rs2 As New ADODB.Recordset
Dim getReport2 As String

' get all data(s) from REPORT table according to bank name

Set rs2 = cn.Execute(getReport2)

.Cells(6, 1).CopyFromRecordset rs2 'Copy recordset to Excel file starting on row 6 and col 1.

' Make every columns autofit to the data(s)
.Range(.Cells(4, 1), .Cells(20, 8)).Select

' here should insert two blank rows, butit doesn't work
.cells(i+6, 1).entirerow.insert



Copyright 2005-08, All rights reserved