Visual Basic :: Chart Placement In Excel Worksheet

Excel: How Do I Move A Chart On A Worksheet?
I can't get my chart to move. Here is my code:
Code:Private Sub PlotChart(rng2Plot As Range, Title As String, xPos As Long, yPos As Long)
    Dim NewChart ' As Chart
'
' CreateChart Macro
' Macro recorded 21/04/2005 by 08504629
'

'
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
' ActiveChart.SetSourceData Source:=Sheets("Summary").Range( _
' "A1,C1:AU1,A17,C17:AU17"), PlotBy:=xlRows
    ActiveChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = Title
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
    End With
    With ActiveChart
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlValue, xlPrimary) = True
    End With
    ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
    With ActiveChart.Axes(xlCategory)
        .HasMajorGridlines = False
        .HasMinorGridlines = False
    End With
    With ActiveChart.Axes(xlValue)
        .HasMajorGridlines = True
        .HasMinorGridlines = False
    End With
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    ActiveChart.HasDataTable = False
    ActiveChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
    With Worksheets("GraphResults")
        ColBLeft = .Columns("B").Left
        '.Range("A1").Select
        ActiveChart.ChartArea.Left = ColBLeft
    End With
End Sub

The line in question is "ColBLeft = .Columns("B").Left" with the error "Error 13: Type Mismatch". The current selection is the chart. If I select a cell on the worksheet, the line works OK. But then, if I continue to run the code, I get the error "Error 91: Object variable or With Block variable not set". How do I do it?

Pls consider that I might already have charts on that worksheet and I don't know the chart name.

|
+--JDMils
|
+--VB6
+--VB Dot Net
|
+-- Navman GPS Forums @ http://forum.jdmils.com
|

Linking Office Chart 10.0 In A Form To An Excel Worksheet
I`m trying to create a chart on a form in excel that can be updated in real time (or at least at the click of a button) based on changes to parameters made in the form. I have inserted a microsoft chart 10.0 object and can make the chart just find using the chart wizard and copying and pasting the relevant cell ranges from my worksheet. However this does not obviously allow real time changes to occur. In all the help I have found on this topic, the code i seem to require is to set ChartSpace1.datasource = spreadsheet1, and then allocate categories and data ranges through that, however I do not know how to allocate my worksheet in the workbook as an object of type spreadsheet, as it seems they are two different things. Please could someone explain how to do this or point me in the right direction if im going completely the wrong way thanks!

Chris

Want To Save Snapshot Of Excel Worksheet (pasted Values And Chart Data Etc)
Hi there,

I am trying to save an active worksheet into a new workbook which I have the code for:
Code:
    Dim strFile As String

    strFile = "C:.......filename.xls"

    Worksheets("Sheet 1").Select
    Worksheets("Sheet 1").Copy

    ActiveWorkbook.SaveAs strFile
    ActiveWorkbook.Close

However the saved sheet uses references to the original sheet which will be changed (I want new saved sheet to hold original values).

I have played around using paste values and came up with a roundabout way of doing it but there are also graphs on the page that don't work with paste values.

Is there a way of taking a snapshot of the values and charts at that point rather than using the formulas and series data.

Edited by - rickp101 on 12/17/2003 7:21:08 AM

Problem: Placement Of Pie Chart
xiaofu83@yahoo.com

i face a problem in visual basic 6.0. i need to place a pei chart in excel worksheet using visual basic 6.0. Because of my data displayed in excel is dynamic. so, i couldn't know when it'll stop and where will be the last row in excel. can anybody help me to solve my problem?

for the line which is fix, i can use these comand to place the chart:

objWorkbook.ActiveSheet.Shapes("Chart 2").IncrementLeft -183
objWorkbook.ActiveSheet.Shapes("Chart 2").IncrementTop 606

how if i my data is dynamic? how can i place the graph after the last row?

Embedded Chart In A Worksheet And A Chart Exisiting On Its Own Sheet
what is the difference between a Chart embedded in a worksheet and a chart existing on their own sheets. i mean what is the difference between being embedded in a worksheet and existing on their own sheets.

i came across the statement on the MSDN site

Note Charts embedded in a worksheet are members of the ChartObjects collection, whereas charts that exist on their own sheets belong to the Charts collection.

How Could You Know If The Sheet Is A Chart Of Worksheet
I am going to make a loop like this:

For i = 1 To excel_wb.Sheets.Count

'my code here

next i

How can i know during this loop if the sheet is a worksheet or a chart? So it would be like this:

For i = 1 To excel_wb.Sheets.Count
if (sheet is a worksheet) then
'do this
else
'do that
end
next i

How To Copy A Chart From One OLE Excel Sheet To An OLE Excel Chart Object
Hi all,

Kinda stuck here and I have been searching for the way to do this all day. I have a spreadsheet with Charts in one VB OLE container (Excel.Sheet.8)and I want to copy one of the charts to another VB OLE container (Excel.Chart.8) and have it sized to fit in my OLE container. I cant seem to get it to work. Any help?

Where OLE1 = spreadsheets with charts
and OLE2 is a chart

Code:
OLE1.Class = "Excel.Sheet.8"
OLE1.SourceDoc = App.Path & "Data5.xlt"
OLE1.Action = 0
'... Work with the spreadsheet...
OLE2.Class = "Excel.Chart.8"
OLE2.Action = 0
Set OLE2.object = OLE1.object.Charts(1) 'Doesnt Work
OLE2.object.Height = 5000 'Doesnt Work
OLE2.object.Width = 10000

VB Code For Add Bar Chart Onto Existing Worksheet
Currently I am using VB6 for excel, which is to automatic generate a bar chart in a worksheet. I was using the "help" and working on the following code "
With Charts.Add
.ChartWizard Source:=Worksheets("sheet1").Range("a1:b13"), gallery:=xlBar, Title:="Customer Order"
End With" But I am not sure whether I am on the right track.
Hope someone could help!

Find The Position Of A Chart In A Worksheet
Hi there

I want to find the position of a chart in a worksheet (probably by finding the cell reference for it).

Is this possible? If so, how?
Note i have many charts on one worksheet and i will need to loop thru them all and find the position of each one!

Here is my attached code so far:

Function chartposi(chartname As String) As String
    Dim a As String
    Windows("CLEAR Cabinets.xls").Activate
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.ChartTitle.Select
    name = ActiveChart.ChartTitle.Text
    With ActiveChart
        .HasTitle = True
        .Position = ActiveSheet.Cells(a36) '<----- i dont think this line actually works!
    End With
End Function

Edited by - kristy2710 on 6/3/2004 1:38:48 PM

Why The New Chart Cannot Be Added After The Last Worksheet Of A Workbook...???
At first, I use the following code to create a new workbook in Excel2002.

Dim appExcel As Excel.Application
Dim wbExcel As Excel.Workbook
Dim shExcel As Excel.Worksheet

Code:Set appExcel = CreateObject("excel.application")
Set wbExcel = appExcel.Workbooks.Add
And then use the following to add a Chart after Worksheets(3), the last Worksheet in Workbook.

Code:appExcel.Charts.Add after:=wbExcel.Worksheets(3)
I have confirmed that there are only three Worksheets in Workbook, and they are Worksheets(1), Worksheets(1) and Worksheets(3), respecitely. Their arrangement from left to right is: Worksheets(1), Worksheets(2) and Worksheets(3).
However, the new Chart will be added before Worksheets(3) using the above-mentioned code. The same problem won't happen when I change Worksheet(3) into Worksheet(1) or Worksheet(2).

I know I can use Code:appExcel.Charts(1).Move after:=wbExcel.Worksheets("Sheet3") to solve the problem. But I am very curious of why the original code doesn't work.

Insert A Chart In A Worksheet That Has Variable Entries.
I am working on an excel spreadsheet (attached) and am having a problem with the "Insert Chart" Macro I am trying to create. I'm not really too sure how to approach this but if you look at the code I've written so far you may be able to tell me if I'm on the right track or not. The other macros in the sheet all work OK. When I run "insert new mechanic" and add data I need the chart to reflect this. Also I don't need the data in the second column ("B") to be included in the chart. As you can see I've fiddled around with the coding a fair bit but as yet have not come up with a solution. I would appreciate any help. Please see attachment.

Draw Bar Chart ,pie Chart In Excel Using Vb
i want to draw pie,bar chart in excel using vb. database is ms access. pls advise me.

Find A Particular Comment In An Excel Worksheet Using Excel VBA
If I can select all the comments in a worksheet using VBA: -

Set rg = Selection.SpecialCells(xlCellTypeComments)

why can't I cycle through all the selected cells to find out their individual values?

I want to home in on a particular cell (containing a comment) to do some processing. The above line manages to select all the comment-containing cells. But I cant seem to "find" the cell I want.

Remember we have to do it using VB Editor in Excel.

Thanks for going through the problem

Ali

Excel Chart - Paste Special As Picture In Excel Itself
Hi All,

I am working on a report and it needs to be generated daily. After generating and creating charts, I need to paste special those charts as "Pictures". Is there any way to do this? And also i need to paste special every single chart in the work book. I already done a code but it not working properly.. and the important point is i need to paste special the chart at the exact position where i intended to cut. Is this possible???

Pls treat this as emergency and advice me on this.

Thanks in advance..

Regards
Mohan

Excel 2000, Create Powerpoint Org Chart From Excel.
I want to create a powerpoint org chart from Excel using information held in Excel. I have found how to add an Org chart into Powerpoint but can't find anywhere that tells you how to change the details/options or add Subordinates Managers etc and alter those details.

Anyone have any ideas (I don't want to create seperate text boxes if possible.)

Ps if you can create a good powerpoint org chart from excel, any ideas on the best layout of information in the spreadsheet to work through and create it.

This is one of those, that would be good if you can do it (created them in the past and are time consuming pains), rather than has to be done last week ideas.

Regards.

rob.

Help Re: Copying Worksheet In Excel 97 V. Excel XP
I have a function that works perfectly in Excel XP, but crashes in (1004 runtime error, Copy Method) 97. All it is doing is copy an existing, hidden template worksheet to the same workbook and specifying which worksheet it should be after. Can anyone tell me what I am doing wrong?
Thanks very much.

Function CreateNewSheet(year, afterYear)
  'MsgBox year
    Application.ScreenUpdating = False
    Dim AfterY As Integer
    Dim SheetName As String
    Dim AfterIndex As Integer
    AfterIndex = 0
    AfterY = year - 1

    If afterYear = "" Then
        Sheets("datasrc template").Copy After:=ActiveSheet
    Else
        Sheets("datasrc template").Copy After:=Worksheets(afterYear) <-- error here
    End If

    ActiveSheet.Name = year
    InsertNewYearCells (year)
    Worksheets(year).Activate
    Range("M10:O10").Select
    ActiveSheet.Unprotect
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    ActiveCell.FormulaR1C1 = year
    Range("M10:O10").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.Font.Bold = True

    Sheets(year).Visible = True
    Worksheets(year).Activate
    Application.ScreenUpdating = True

End Function

Edited by - calbanese on 4/22/2003 11:19:20 AM

Copy Excel Chart From Excel To Word?
I need the code to copy an excel chart from an Excel sheet to a word doc.

Could it be something like:

ActiveChart.Copy ???

Excel Worksheet
I just use
Code:
Private Sub UserForm_Initialize()
to initialize a combo or textbox when using an excel form.
But how do I initialize a combo or text box when using a Worksheet (I want to use the worksheet for data entry). I have tried the Worksheet_Activate and Worksheet_Initialize with no luck.
The name of my worksheet is Interface and I am placing my text and combo boxes here to use this as my user interface. I can fill the combo boxes with the onclick event but this will duplicate the entry's if clicked more than once.

.mdb To Excel Worksheet To Web
How do I take data from a Mircosoft Access Database, put it into a spreadsheet format and display it on a web page.

I have a very simple db, 1 table with 3 fields. I want to display the data on a web page but look like a Excell spreadsheet. I will later want to make a graph with this data and also show it on a web page.

Thanks,

Mike

Name Of Tab In Excel Worksheet
I have worksheet 1 for Product A, worksheet 2 for Product B, worksheet 3 for Product C and etc. Every worksheet has a date on A1.

How to auto name the tab by combining the Product and Date (from A1) ? For example, if the date in A1 of worksheet 1 is 1/11/04, the macro will show "Product A - 1/11/04" on the tab.

Thank you.

Cheers,
James

Excel Worksheet
How can I open a workbook and print a worksheet from a vb form and command button and after printing worksheet clear the sheet?

Excel Worksheet Help
Good evening.

Can anyone assist me in finding online resources in how to use do stuff with excel worksheets in VB.

When a workbook is opened, i need to be able to find out how many worksheets are included in the workbook, what their names are and then how to skip from one worksheet to another.

Anyone have any advice?
I'm lost.

Ulysses

VB6 To Excel Worksheet???
I have a VB6 program collecting data
I have an Excel template file with one worksheet and one chart page
I want to open the Excel file from VB6, automaticallly enter data by field or by row, and save the modified file as a New file name.

I am currently saving a text file from the basic code module, which I have to import into the Excel application template file after my program has finished.

What is a simple means to do this.

Excel Worksheet
I have an excel workbook that hold production data. The data is divided per quarter. Every sheet calculates other things when numbers are enterd into certain cells.

I am trying to insert a new in that workbook at the begining of the new quarter. The sheet should be blank but i want to have all the forumals, columns and rows from the sheet from the last quarter.

Thanks

Excel Worksheet Help
Hi.

I'm working on an assignment which is about sending some data to an Excel worksheet (named "MyDataReport"), I used the following code to accomplish it.

================================
Dim XLS As Object
Dim XLSheet As Object

' Set the Application (Excel)
Set XLS = CreateObject("Excel.Application")

XLS.Visible = False

' To add a new workbook
XLS.Workbooks.Add

' To add a new Worksheet and then rename
XLS.Worksheets.Add
XLS.ActiveSheet.Name = MyDataReport
Set XLSheet = XLS.Worksheets("MyDataReport")

(... codes for putting data in cells. They're fine)
=====================================
And it works perfectly. (Thanks to the person who post this code for me)

However, I need to create another worksheet called "Report2" in the same Excel Workbook / file. And probaby I need Report3, Report4...etc too. I tried to use the code above to accomplish this, but it just overwrite "MyDataReport". Would you tell me how to fix this problem.

Thanks for Your Help.

Jacob

Excel Worksheet
I can't create an excel worksheet as the output for a function.

VB Code:
Public Function createWorksheet() As Worksheet Dim objExcel As Excel.Application ' Excel object Dim wrkBook As Workbook ' Worksheet object Dim wrkSheet As Worksheet Set objExcel = New Excel.Application ' Create new excel application Set wrkBook = objExcel.Workbooks.Add ' Create new workbook Set wrkSheet = wrkBook.Worksheets.Add ' Create new worksheet 'wrkSheet.Cells(1, 1) = "test" objExcel.Visible = True createWorksheet = wrkSheet ' **** ERROR HERE ***End Function

Add A Row To An Excel Worksheet Through VB
Folks,
Im importing a column of values into vb so I can run a query on those values with SQL Server. I need to name the column so that so I can create a recordset "Select (columname) from[sheet1$] "
Im thinking of inserting a row in the excel sheet and calling it 'serial_no' So that I can query "Select serialno from[sheet1$]"
How do I insert a row in excel from VB, or is there an easier way to do this
Cheers

Excel - WORKSHEET
I have 3 sheets in a workbook. I want to take only 1 sheet out of them and send it as an attchment in a mail. Is there anyway I can do that?????? Any ideas??

Excel Worksheet
Hello,

Can anyone tel me how the object "Microsoft Excel Worksheet" realy works.

Thanks

deju

Excel Worksheet Creation
I have the following problem

I am opening an excelfile with multiple worksheet, which has some Macros with VB-coding.

Wenn printing, I want to print one of the worksheets, and also want to save the entire file under a other map, with a other name, with only that one worksheet in it.

The way I worked this out was by copying the entire file to the new dir under a new name. Then I delete the worksheet. However wenn I now open the file, it will crash. Wenn I do not remove the worksheets, it goes fine.

I have tested the same code on 2 computers: On a XP laptop, on which it works without a problem, and on a Window2000 computer, on which the problem occures.

The Question:
So I am wondering about the following: WHat code could I use to copy a worksheet into a new empty file and save that, instead of making a copy of the old file.

THx.

COPY Excel Worksheet With VBA
Hello again,

Now I am trying to copy a VBA created Excel Worksheet and append multiple copies at the end of the Workbook.

I have come up with this:

Code:
' copy Worksgeet (Index # 4) and paste 15 copies
Set xlWS = xlWB.Worksheets(4).Copy(, 15)

but I get this error:

Quote:

Run-time error '1004':
Unable to get the Copy property of the Worksheet class

Any ideas to correcting this?

Converting An Excel Worksheet 2 A .jpg
I think my question is simple, but I could be wrong. I need to convert an Excel Worksheet file to a .jpg image. Now the funny part is I have done this once before using Excel, yet I seem to have forgotten what it is I did to make it happen. Therefore I came here to ask someone who knows alot more than I do about Excel. I hope you guys can help me out in my time of need. I look forward to reading your reply.

P.s This is my first time at your site and I gotta say the way you setup the forums to look like Microsoft is really cool...I really like the whole site's layout it works!

Thanks 4 the help,
-Bang

Save Excel Worksheet
DOes anione noes how to save an excel worksheets to a folder??

Printing Excel Worksheet
Hi.

I have an excel spreadsheet on my form and a click button to print the worksheet. Could someone help me out with some code to bring up the print dialog to print the spreadsheet (not the entire form contents).

Thanks in advance.

David

Opening Excel Worksheet From Vba
hello, i don't know if this thread already exists, anyway i really need to know how to do this, i have a command button in a form, when clicked, must open a new excel worksheet, and then fill it with data. Thanks.

Getting Handle For Excel Worksheet
Hi,

I'm trying to get the handle of a child window in Excel.
I don't have any problems getting the handle of Excel itself:

hExcel = FindWindow("XLMAIN",0)

I've been trying to get the handle of a certain child window "Cardata.xls" using

hWkbk = FindWindowEx(hExcel, 0&, ...)

My question is: what values do I put in the 3rd and 4th calls? I assume "vbNullString belongs in the 4th, but I'm not sure. Supposedly the 3rd entry is lpszClass, but what value do I set it? In the example at apilist, they use "BUTTON" for the start-Button. What do I need to use -- "CHILDWINDOW" or somesuch?

Thanks,

Richard

Excel WorkSheet Names
I'm reading the names of worksheets in an Excel workbook into a listbox. I have 1 workbook that causes an error when it gets to the tenth worksheet.

Runtime Error '40036'
Application-defined or Object-defined Error

Here's my code:

Code:
Set appExcel = New Excel.Application

With appExcel
.Workbooks.Open txtFile.Text
sheetcount = .Worksheets.Count

For i = 1 To sheetcount
strName = .Worksheets(i).Name
ListAllSheets.AddItem strName
Next i
Any idea what could be wrong with the Excel file? I'm assuming its the excel file because other files with more than 10 worksheets will read in fine.

Thanks,

Excel Worksheet As Attachment
I use a VB form to collect user info which is then written to an Excel worksheet and e-mailed as an attachment to another department, using MAPISession and MAPIMessage.

When the recipient opens the attachment, the worksheet is hidden and must be made visible with Window>Unhide.

Is there any way to make the worksheet visible when the recipient opens it? Code snippet is below:

MAPISession1.SignOn

With MAPIMessages1
.SessionID = MAPISession1.SessionID
.Compose
.MsgSubject = "Test Message"
.RecipDisplayName = "Ron Urbaniak"
.RecipAddress = "rurbaniak@glatting.com"
.ResolveName
.MsgNoteText = " "
.AttachmentIndex = 0
.AttachmentPosition = 0
.AttachmentPathName = strProjSetupFileName
.Send
End With

MAPISession1.SignOff

Excel Worksheet On A Form
Hi!

I know this topic has probably been up before, but I couldn't find what I was looking for... sorry..

I have a form where I want to embed an excel worksheet. The worksheet is not supposed to be editable by the user, only display information from my Access database. Normally I edit the cells of an external worksheet like this:

Code:
Dim db As Database
Dim rs As DAO.Recordset
Dim XLApp As New Excel.Application
Set db = OpenDatabase(App.Path & "Database.mdb")
Set rs = db.OpenRecordset("SELECT * FROM [Product]")
XLApp.Workbooks.Open (App.Path & "Data.xls")
XLApp.Cells(2, 2).Value = rs.Fields("CustomerName")

Can anyone tell me how to embed a new excel worksheet on my form, so that I can read values from my database and display it like in the example above?

Thanks in advance!

Adding A Excel Worksheet
I have the following code :

Public xlapp As Excel.Application

Set xlsheet = xlbook.Sheets("Sheet1")
xlsheet.Select
xlapp.Sheets.Add

This is the code that is going wrong.

I get the following error when it tries to add the worksheet

Run-time error 1004

Method 'Sheets' of object '_Application' failed

Create New Worksheet In Excel???
This is prob. a very easy question for most of you, but my VB knowledge is quite limited...

I wish to create a new worksheet with a distinctive name but all I can seem to do is open an existing worksheet...

Here is my "dirty" code so far...

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

oApp.Workbooks.Open FileName:="k:miscxxx_xxxx.xls"
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True

Two part question..is there a command such as Workbooks.Create???

and can I create a new file with the filename based on the data from a text box?

Thanks in Advance

Output In Excel Worksheet
Can I show the content of Recordset in Excel Worksheet???
If yes then how?

Displaying Excel Worksheet From VB 6
Hai, I want to display a particular worksheet based on certain conditions. I have about over 100 excel worksheets. These are categorised into course, step and worksheet number. That is for example, there will be one worksheet like Course - V-Make, step - Addition and Worksheetno - 2 . So in my VB if i select these categories through combobox i want to display the particular worksheet, so that i can view the worksheet and if necessary update it.

I dont know how to go about it. Please help me in this regard.

Thank you.

E-mail An Excel Worksheet
Anyone know how the code would look like to send via (cc) & (bcc) in the email?

Thanks,

PhiL

Quote:

Originally Posted by herilane

An array of strings is indeed needed. Example (assuming Option Base 1):

Code:
Dim strRecipients(2) As String
strRecipients(1) = "me@yahoo.com"
strRecipients(2) = "you@hotmail.com"
ActiveWorkbook.SendMail recipients:=strRecipients(), subject:="read this"

Scroll In Excel Worksheet OLE
Hi All,

I have an Excel OLE in my VB6 form and it is disabled. I do not want the user to edit it at all. I use it only to preview an Excel document.

How can I scroll my worksheet while the OLE is disabled?

I tried:

Code:
FinalOLE.object.Activewindow.ScrollColumn = HScrollBar_E.Value + 1
and it did not work! any ideas?

Thanks,

-George

Excel Copy From One Worksheet To Another ?
I'm trying to write a write a macro that will transfer each row of data in one worksheet to a second worksheet (for example data from each column 1 in a row on first worksheet will transfer to cell B5 on second worksheet, column 2 will transfer to E10, and so on), print the second worksheet, and then repeat for each row of data in worksheet 1. My question is, how can I write code that will make each row number on worksheet 1 a variable and each column number a constant, and each destination cell on worksheet 2 a constant. Other than that, I think I can just write code that will loop through each row.

I apologize for the length of my question, I'm kind of a rookie. Thanks for any suggestions.

Finding Last Row On Excel Worksheet
I want to use VB to search for the number of the last row of entered information in an excel worksheet. This was a previous thread from a few months ago (posted by someone else), which had a number of replies containing various snippets of code. I'm sure I had my code working but after returning to my program (having not touched it for a while), when I run it I get the following error:

"Method 'rows' of object '_worksheet' failed."

The code is as follows:

Dim xlapp As Excel.Application
Set xlapp = New Excel.Application
xlapp.Workbooks.Open (filepath)
Dim xlsheet As Excel.Worksheet
Set xlsheet = xlapp.Sheets(1)
Dim xlrange As Excel.Range

Dim x As Integer
Dim lastrow As Integer
xlapp.Visible = True

'Find last row- code from internet
If xlapp.WorksheetFunction.CountA(xlsheet.Cells) = 0 Then lastrow = 1 Else
lastrow = xlsheet.UsedRange.Rows.Count + xlsheet.UsedRange.Row
While xlapp.WorksheetFunction.CountA(xlsheet.Rows(lastrow)) = 0
Wend
MsgBox (lastrow)

I then go on to close and cleanup the excel application after writing some information to the worksheet.

I have tried so many things to try and solve this error but just can't work it out. The thing that is confusing me is that I'm sure I've had this piece of code working before. Am I missing something obvious here?

Thanks in advance for any help you can give!