Visual Basic :: Integrate A Form In An Excel Worksheet

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!

Is This Possible: Excel Worksheet In Vb Form???
i have about 4 excel spreadsheets, and every one of them has a summary sheet.

what i want to do is create a program with a bunch of excel objects, and a timer.

at certain interval, the program will connect to the different spreadsheets and update its own excel objects...

can it be done???

thanks

Display Excel Worksheet In VB Form GUI
Hi,

If I want to display/load a Excel worksheet on the VB GUI, how could I do this. I tried the OLE but it does not do display.

My application is actually need to display the worksheet at the program GUI, user can do edit and save to the excel file.

Other altenative is please guide me on how to create a table on the GUI.

Thanks for helping!

Excel VBA - Form Controls On Worksheet
I have a form in a worksheet with several text box inputs(ActionItemForm). When I click AddActionItem button, I want it to fill in the data in the second worksheet(ActionItems).

The data in the second sheet is also pulling in values from a third worksheet so the columns aren't Contiguous.

The second sheet holding the values includes the following columns(ActionName, Description, Resolution, AssignedTo, Team, Status). I've found ways to add a new action item to the first row by using Get/Set method and named cells but, I haven't been able to add additional action items to the range(s). AssignedTo and Team are pulled from a seperate worksheet, I'm just trying to get the action item values right now. This should be able to hold 1000 action items and I should be able to retrieve these values to populate an update screen at a later date.

Thank you for any help you can provide. This is for a school assignment.

Link Excel WorkSheet Into A Form
Hi All,

I can achieve the above by using the OLE control in VB6, but I want to be able to edit the cells's direct from the VB form as well without having to load the Excel sheet seperately.

In an Access 2000 form you double click but again it opens a seperate instance of Excell to edit.

Is there anyway you can tab in the cell's etc and change figures on the Excell cells all from the form ?

Table In VB Form Behave Like Excel Worksheet
Hi, I want to make table in VB but the table must behaves like excel worksheet, for example : I can add, edit and erase text directly to every cell in the table.
Until now I make the table from textbox, but it will be difficult and confuse me if the table contains so many cells.
If anyone know the answer, please tell me.
Thank you

Excel VBA Load Values In Form From Worksheet
Sheet "Global" has a table of values, which I have load into a form (frmNew) after clicking a command button. Im using the below code to load the values from the sheet to the form, everything works great with the textboxes, but when it comes to loading the selecting the listbox values (lstAnswer1.Value, etc) it seems to miss about 3-4 of them each time and I cant figure out why.

Code:
frmNew.txtQ1A4.Text = Sheets("Global").Range("F5")
frmNew.txtQ2A4.Text = Sheets("Global").Range("F6")
frmNew.txtQ3A4.Text = Sheets("Global").Range("F7")
frmNew.txtQ4A4.Text = Sheets("Global").Range("F8")
frmNew.txtQ5A4.Text = Sheets("Global").Range("F9")
frmNew.txtQ6A4.Text = Sheets("Global").Range("F10")
frmNew.txtQ7A4.Text = Sheets("Global").Range("F11")
frmNew.txtQ8A4.Text = Sheets("Global").Range("F12")
frmNew.txtQ9A4.Text = Sheets("Global").Range("F13")
frmNew.txtQ10A4.Text = Sheets("Global").Range("F14")

If Sheets("Global").Range("G5") <> "" Then frmNew.lstAnswerQ1.Value = Sheets("Global").Range("G5")
If Sheets("Global").Range("G6") <> "" Then frmNew.lstAnswerQ2.Value = Sheets("Global").Range("G6")
If Sheets("Global").Range("G7") <> "" Then frmNew.lstAnswerQ3.Value = Sheets("Global").Range("G7")
If Sheets("Global").Range("G8") <> "" Then frmNew.lstAnswerQ4.Value = Sheets("Global").Range("G8")
If Sheets("Global").Range("G9") <> "" Then frmNew.lstAnswerQ5.Value = Sheets("Global").Range("G9")
If Sheets("Global").Range("G10") <> "" Then frmNew.lstAnswerQ6.Value = Sheets("Global").Range("G10")
If Sheets("Global").Range("G11") <> "" Then frmNew.lstAnswerQ7.Value = Sheets("Global").Range("G11")
If Sheets("Global").Range("G12") <> "" Then frmNew.lstAnswerQ8.Value = Sheets("Global").Range("G12")
If Sheets("Global").Range("G13") <> "" Then frmNew.lstAnswerQ9.Value = Sheets("Global").Range("G13")
If Sheets("Global").Range("G14") <> "" Then frmNew.lstAnswerQ10.Value = Sheets("Global").Range("G14")

frmNew.txtIncorrectQ1.Text = Sheets("Global").Range("H5")
frmNew.txtIncorrectQ2.Text = Sheets("Global").Range("H6")
frmNew.txtIncorrectQ3.Text = Sheets("Global").Range("H7")
frmNew.txtIncorrectQ4.Text = Sheets("Global").Range("H8")
frmNew.txtIncorrectQ5.Text = Sheets("Global").Range("H9")
frmNew.txtIncorrectQ6.Text = Sheets("Global").Range("H10")
frmNew.txtIncorrectQ7.Text = Sheets("Global").Range("H11")
frmNew.txtIncorrectQ8.Text = Sheets("Global").Range("H12")
frmNew.txtIncorrectQ9.Text = Sheets("Global").Range("H13")
frmNew.txtIncorrectQ10.Text = Sheets("Global").Range("H14")

Placing Data From Form Into OLE Excel Worksheet
I want to be able to place data from a textbox or list box into an excel worksheet. I have opened an OLE control and assigned my excel worksheet to it.
My question is how do I move my data from the form into specific cells on the worksheet.
I am using vb5 in win98.
any help would be appreciated.

Embed An Excel Graph And Worksheet In A Form
hi~

I would like to embed an excel graph on a form. Moreover, if a "show data" button was pressed, the data of the excel graph can be shown in a datagrid.

p.s. :The excel graph is generate at run time by OLE.

I have read some books of visual basic 6.0, there is an OLE icon in the toolbox; however in vb.NET, the icon cannot be found. Is the function of OLE embedding is being removed in vb.NET?

Editing Excel Worksheet On A VB Form And Saving It
Hi all,

Posted this yesterday, but I still cannot find out how to to the above, I have managed to use the OLE tool to embed the spreadsheet on the VB form, but there I can't see away of saving the spreadsheet back to its original .XLS file, only as an OLE data file ?

I can also use a reference to the Excel object library but this opens up an complete Excel Instance in a seperate window, same when using the Linked option on the OLE control. I can't believe its not possible to embed an Excel worksheet on a form and simple be able to edit it and save it back, surely . . .

Please can anybody help ?

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

Faster Access To Form Controls On Excel Worksheet?
ralan@charter.net

My client has worksheets with as many as a few thousand Form controls such as radio buttons on them. The radio buttons are organized within Excel form Frame controls.

At various times I need to find out which controls are related to each other (contained in the same frame). What I'm doing now is looping through the entire control collection looking for radio buttons whose GroupBox.Name equals whatever frame I'm dealing with. When there are thousands of controls on a worksheet and you're only looking for a handful, this is extremely time consuming and feels like a major performance problem to the user.

Is there anyway that I can somehow query excel to return to me the exact list of controls
contained in the frame of my choice without having to loop through the entire control collection?

This is an important issue on the project I'm on and a slick answer to this would be greatly appreciated.

Thanks in advance,

Rob Alan

Excel VBA - Passing Values Between User Form And Worksheet
Hi,

I would like to have a text box in a user form and allow the user to enter text into the text box, which will be passed to a label(lblName) in a worksheet("View Data").

Is this possible?

In addition, is it possible in Excel to select a button on sheet1 which will hide sheet1 and display another sheet i.e. sheet 2?

Many thanks in advance for any help or suggestions.
natalie

Help W/simple Transfer From Vba Form Field To Excel Worksheet Cell
Hey, I've got a real simple problem I can't get my head around. I'm just trying to copy or take data entered in text boxes (in a VBA form) and put it into a cell in a worksheet. Specifically, when the user clicks the "compute" command button I want the data from the text boxes copied or sent to excel cells.

Here's the code so far:

Code:
Private Sub cmdCompute_Click()

    Dim strFilePath As String
    strFilePath = "C: emp.xls" 'workbook to receive data from text boxes

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet

    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open(strFilePath)
    Set xlSheet = xlBook.Sheets("Sheet1") 'worksheet to receive data

    xlSheet.Activate
    Range("A1") = frmMain.txtDate.Text 'data being transfered is from main form and text box "txtDate"
    Range("B1") = frmMain.txtMajor.Text 'same as previous line, but different text box
    xlApp.Visible = True

End Sub

Thanks for your help.

Integrate Excel In Vb6?
Hi,
Is it possible to integrate excel in vb6 like a control? So I can work with excel like with flex grid.

Integrate Excel With VB
I am developing a costing system which involes a lot of formulas and calculations.
I would like to integrate Microsoft Excel into my VB applications.
I tried out the OLE object but it is very difficult to control and the interface does not look nice.
I plan to use Excel object instead. My problem is when I use the Excel object, it launch the whole Microsoft Excel application.
I do not want this.
I want the Excel application to open inside my VB applications (something like an MDI Child form). Can this be done?

Thanks for the help.

Integrate Excel Data In PPT
How can I access the information in the fields of an excel database and past it into text fields in a power point presentation (programmed in VBA)?

Integrate Sound On Form
How can I add a background sound on my form?

Integrate MIDI File And FORM In One EXE
Hello..
I plan to add my MIDI music file to my program.
But, is there a way to integrate MIDI file and FORM in one compiled EXE, so my MIDI file doesn't appear.

Thank you so much foe any help..

Best regards,
--maox--

Integrate Code Into A Single Form
I've been told by the person who gives me work to "Integrate code into a single form" in VB .NET Is this old school of way to do things? e.g. frmMain will now contain Account, Customer, and Attendant from other forms and modules. Wouldn't it be better to make classes out of these?

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

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

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

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.

Integrate A Form In An Excel Worksheet

hey,is it possible to embed a form into an worksheet, like you can do it with pictures?greets & thx ben

hey,
is it possible to embed a form into an worksheet, like you can do it with pictures?

greets & thx
ben