Questions About Excel Vba For Loop Through Rows

Maria asks…

How to import two rows data to powerpoint from excel file through VBA code.?

Dear VBA Experts,

First of all i would like to thanks all of you for providing me such a helpful VBA code to import image and its related information from two deferent place.

I had have a excel file containing list of product image and its all information, here I used to import product image based on file name which we call article number, during this period I has have a challenging job to make PowerPoint of all those product with the all information as I had in excel file, the job have been done by VBA code provided by a gentlemen through this service.

right now the ppt have been made using below written Vba code, what I want now is to import three rows information in each and every slide based on product image. Right now the code is importing one row information.

Please help me.

VBA Code.
Sub ImportABunchWithTextFromFile ()
Dim strTemp As String
Dim strPath As String
Dim strFileSpec As String
Dim oSld As Slide
Dim oPic As Shape
strPath = ActivePresentation.Path
Set fs = CreateObject( “Scripting.FileSystemObject”)
Set f = fs.OpenTextFile( strPath & “book1.txt”, 1, 0) ‘book1.txt need to be “Text(Tab delimited)(*.txt)” when saved in Excel

Do While f.AtEndOfStream <> True
picDesc = Split( f.readline, Chr(9))
Set oSld = ActivePresentation.Slides.Add( ActivePresentation. Slides.Count + 1, ppLayoutBlank)
Set oPic = oSld.Shapes. AddPicture( FileName:=picDesc(0), _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=0, _
Top:=0, _
Width:=0, _
Height:=0)
‘next 4 lines define TextBox(TB) position and dimensions in terms of those of slide
TBLeftFromSlideLeft = 60 ‘this can be changed
TBTopFromSlideTop = 400 ‘this can be changed
TBWidth = 600 ‘this can be changed
TBHeight = 100 ‘this can be changed
Set oDes = oSld.Shapes.AddTextbox( msoTextOrientationHorizontal, _
TBLeftFromSlideLeft, _
TBTopFromSlideTop, _
TBWidth, _
TBHeight)
With oDes
.TextFrame. TextRange.Text = picDesc(1)
End With

With oPic
.ScaleHeight 1, msoTrue
.ScaleWidth 1, msoTrue
End With

With oPic
Dim appssw, appssh
‘next 4 lines set the distance between the image display area margins and the slide margins, image display area width and height
imageTopFromSlideTop = 40 ‘this can be changed
imageLeftFromSlideLeft = 40 ‘this can be changed
maxImageWidth = 640 ‘this can be changed
maxImageHeight = 350 ‘this can be changed
appssw = maxImageWidth
appssh = maxImageHeight
.LockAspectRatio = msoTrue
If oPic.Width / oPic.Height > appssw / appssh Then
.Width = appssw
.Top = (appssh – oPic.Height) / 2 + imageTopFromSlideTop
.Left = imageLeftFromSlideLeft
Else
.Height = appssh
.Left = (appssw – oPic.Width) / 2 + imageLeftFromSlideLeft / 2
.Top = imageTopFromSlideTop
End If
End With
Set oPic = Nothing
Set oDes = Nothing
Set oSld = Nothing
Loop
Set f = Nothing
Set fs = Nothing
End Sub

Regards
Santosh

excelatvba answers:

With the code you got, replace this lot
With oDes
.TextFrame. TextRange.Text = picDesc(1)
End With

with this lot
For a = 1 To UBound( picDesc)
myText = myText & Chr(13) & picDesc(a)
Next
myText = Right(myText, Len(myText) – 1)
With oDes
.TextFrame.TextRange.Text = myText
End With
myText = “”

There is also changes in entry of the Excel file/Book1.txt, now image path(column A) is the same, column B is for 1st line of text, column C is for 2nd line of text, column D is for 3rd…and so on.

E.g. On the same row, say row 2, A2 is the image path; B2 is 1st line text; C2 leave it blank if we need a blank line between 1st and 2nd line of text; D2 is 2nd line of text; for E2, if we don’t need blank line between 2nd and 3rd line of text, then put 3rd line of text…etc.

The rest is the same, save the spreadsheet as “Text(Tab delimited)(*.txt)” , put it with the pptm file before running the vba/macro.

Please contact for more info and code modifications by using this email link http://answers.yahoo.com/my/message_do?kid=ll8pngUcaa .

Joseph asks…

Excel VBA – Delete rows of a range?

I have been having a hard time with this for a couple of hours now:
I have a range of cells on a specific worksheet that varies in length, but that length is always known when the procedure is performed. I want to go through this range, and if the cell in the first column is empty, delete that entire row, and then do this until all of the empty rows have been deleted. The problem, I think, is that I am using a variable to count and then control IF and DO WHILE loops, but that whenever a row gets deleted it messes up the count that I have.

Does anyone have a specific idea of how I can do this? Some specific pseudocode would be awesome.

Thanks in advance!

excelatvba answers:

The fundamental thing one must remember when looping through and deleting entire rows, is that you MUST begin at the ‘bottom’ and work up. Otherwise, Excel loses its place and the process does not function appropriately.

For example, the following BeforeDoubleClick event handler will evaluate all rows from the ‘bottom up’ (last used row to the second row), and delete all rows containing no data.

Notice the loop begins at the Lastrow and ends at row 2. The ‘step -1’ tells Excel that the next row to evaluate will be one ‘less’ than the current row number. That way the deleted rows are being deleted ‘behind’ each iteration, not ‘before’ it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim i, LastRow
LastRow = Application.Cells. SpecialCells(xlCellTypeLastCell).Row
For i = LastRow To 2 Step -1
If Application.CountA(Cells(i, “A”).EntireRow) = 0 Then
Cells(i, “A”).EntireRow.Delete
End If
Next
End Sub

To remove all blank rows, double click any cell.

James asks…

Excel VBA vs C++?

Hi there, I am not an IT person. I have asked someone to help me create a VBA add-in function to calculate statistical attributes of time series. The problem is that the process involved a loop calculation, and with a long time series computation becomes quite slow. Also I have hundreds of time series so each row would make a function call and computation would be that much slower again. My question is whether compiling this in C++ would make it markedly faster. What troubles me is that when I calculate this statistical attribute for each row through standard Excel formulae (effectively one for each loop on each row so an awful lot of cells), it is very fast, although the spreadsheet becomes very unwieldy with all these intermediate calculations in additional cells. Many thanks for your help.

excelatvba answers:

VBA is capable of extremely fast computations. It is more likely that your approach for producing these results is very inefficient – there is likely a number of ways to scrape the data from the spreadsheet and perform the calculations much more rapidly if you reevaluate how you are processing it. I have seen many inexperienced programmers use hundreds of thousands of loops and complex variable pushing when a few simple lines of code would have sufficed. Your problem may not be able to be simplified to such an extreme, but I find it highly unlikely that any reasonable business calculation running out of an Excel spreadsheet could become unreasonably slow if designed well.

Sharon asks…

Excel VBA column selected?

I am new to VBA and Excel. I am trying to make a loop that will cycle through all of the rows in a column then move on to the next column and do the same thing. For the rows I believe it should look something like this:

Do Until IsEmpty(cells(iRow, COLUMN SELECTED )

What do I put in the column selected? I can not put a static column name because it will be changing every time I finish going through the rows and start a new column.

excelatvba answers:

Sub CycleAll()

Dim x As Long
Dim y As Long
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Long

StartRow = Selection.Row
StartCol = Selection.Column

For x = 1 To Selection.Columns.Count
EndRow = Application.Min(Cells(StartRow, x).SpecialCells(xlCellTypeLastCell).Row, Selection.Rows.Count)
For y = 1 To EndRow
MsgBox(Cells(y, x).Value) ‘Code goes here
Next y
Next x

End Sub

Charles asks…

excel vba homework help?

I am writing a function that needs to use a “for next” loop and it needs to check each row in “sheet 2″ to see if they are equal to the corresponding cells in ” sheet 1″.

any ideas on how to write how this code?

i have previously written a function called “lastRow” that will run through rows till they stop having data in them.

excelatvba answers:

It sounds like you already know how to use a “for….next” loop and test values. So just test for cells being equal instead of empty. If you don’t know how to reference different sheets, you can get an idea by creating an entry in a cell: press “=”, switch to the other sheet, and click on a cell.

Powered by Yahoo! Answers