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

Manipulating Embedded Excel Worksheets In Access

I'm having some problems populating & manipulating an Excel worksheet embedded in an Access form & wondered if anyone might be able to help.

I've got an Access form 'frmMS' with an embedded worksheet 'objDisplay' that was created by Insert >> Object >> Create New >> Microsoft Excel Worksheet

The code I've got right now is:

Private Sub butAbout_Click()

Dim objXL As Object
Dim X As Integer
Dim Y As Integer

Set objXL = Me!objDisplay.Object

For X = 1 To 60
For Y = 1 To 5
objXL.Worksheets(1).Cells(Y, X).Value = "Q"

Set objXL = Nothing

End Sub

I spent all day reading Office integration articles (this is my first attempt at integration) without finding anything I could use, & the above code is really just something that I hit on with trial & error after a few frustrating hours. I did see a lot about early & late binding, but everything was referring to linked sheets or opening instances of Access.

It seems to work, & lets me work with the sheet, but there are a few problems:
1) It's really, really, really slow. Exponentially slower as the X range increases.

2) Whenever I first switch the form to design view, it tells me the object is locked & any changes will be discarded. I haven't noticed anything being lost, but this scares me.

3) It seems to do all the operations, then update the sheet. I haven't seen any references to manually controlling when the embedded sheet refreshes, but it's kind of necessary for certain parts of the larger program.

4) I don't know why it works, which just bothers me.

If the larger scheme of things matters at all, the basic purpose of this is getting an embedded sheet that can handle certain graphical elements of an Access project. A simple example would be to picture the sheet like one of those scrolling displays at baseball games, where a user could hit a button & the sheet, by changing the background color of certain cells, would create a message, image, etc. I know this is a stupid way to approach it, but it's unfortunately necessary, & it just drives me nuts that I can't figure it out.

Thanks for any help you can give.

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Manipulating Excel Worksheets
I have finally got this somewhat working; I find "Office" automation via VBA very difficult.

Anyways, what this code is supposed to do is:

1. create Excel Workbook
2. read from Access database and determine how many Excel Worksheets are required
3. create a project specific "template" Worksheet; this will be different for each job
4. create "N" copies determined from Access db
5. fill in all Excel worksheets from Access data

Below is a simplistic version of the above:

I have two major issues:

1. Excel determined Worksheet indexes

I find it very confusing to keep track of these; Is there a trick to this?

2. In my xlsMakeCopies sub I had to create a separate loop to rename all the Worksheets, as trying it in the first loop caused errors.

What am I doing wrong here? - it should work in the first loop.

Any comments again are welcome.

Form Code:

Option Explicit

Private Sub Command1_Click()

' this array to be filled in from Access Database
Dim ProjectCodes(1 To 6) As String
ProjectCodes(1) = "A1a"
ProjectCodes(2) = "A1b"
ProjectCodes(3) = "A2a"
ProjectCodes(4) = "A3a"
ProjectCodes(5) = "A3b"
ProjectCodes(6) = "A3c"

Call xlsOpen

'create a template sheet for THIS Project
Call xlsCreateTemplate

'create a SEPARATE Worksheet for each item in "ProjectCodes" array
Call xlsMakeCopies(ProjectCodes)

'update EACH Worksheet with SPECIFIC data
Dim i As Integer
For i = 1 To UBound(ProjectCodes)
Call xlsUpdateSheet(ProjectCodes(i))
Next i

'select the Worksheet to be displayed when Workbook is opened
Call xlsSelectSheet(3)

Call xlsSave
Call xlsClose

MsgBox "Done"

End Sub

Module Code:

Option Explicit
Option Compare Text 'Database
Option Base 1

'Excel declares
Dim xlAp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Public Sub xlsOpen()

' create an Excel Application object
Set xlAp = New Excel.Application

' add a New workbook with 1 sheet
Set xlWB = xlAp.Workbooks.Add(1)

End Sub

Public Sub xlsSave()

xlWS.Application.DisplayAlerts = False
xlWS.SaveAs App.Path & "MySheet.xls"
xlWS.Application.DisplayAlerts = True

End Sub

Public Sub xlsClose()

' clean up
Set xlWS = Nothing
Set xlWB = Nothing
Set xlAp = Nothing

End Sub

Public Sub xlsSelectSheet(index As Integer)

'select the sheet to be selected when xls file opened
Set xlWS = xlAp.Sheets(index)

End Sub

Public Sub xlsMakeCopies(CodeArray() As String)

Dim i As Integer


'make correct amount of copies (based on "template")
For i = 1 To UBound(CodeArray)

xlWB.Sheets("template").Copy After:=xlWB.Sheets(xlWB.Sheets.Count)

'xlWS.Name = Codearray(i) ' rename it

Next i

'rename the sheets
For i = 1 To UBound(CodeArray)
Set xlWS = xlAp.Sheets(i + 2)
xlWS.Name = CodeArray(i)
Next i

End Sub

Public Sub xlsCreateTemplate()

'add a new Worksheet
Set xlWS = xlWB.Sheets.Add(, xlWB.Worksheets(xlWB.Worksheets.Count), 1)

Set xlWS = xlAp.Sheets(1)

Set xlWS = xlWB.ActiveSheet
xlWS.Name = "template" 'rename it

xlWS.Cells(1, 1).Value = "template text"

End Sub

Public Sub xlsUpdateSheet(strCode As String)

'first SELECT sheet strCode
Set xlWS = xlAp.Sheets(strCode)

xlWS.Cells(1, 1).Value = "Change text for Worksheet " & strCode

End Sub

Manipulating With Access And Excel Via Vb 6
Hi Everyone
i have a vb project in which i have got information in excel sheet and now i want to store this information in access data base .. i have searched and this code kinda worked for me

VB Code:
Option Explicit'Reference added to microsoft activeX libraryPrivate Sub Command1_Click()    Dim oRs As ADODB.Recordset    Dim oCnn As ADODB.Connection    Dim i As Integer    'Connect to your Access db    Set oCnn = New ADODB.Connection    oCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:Working.mdb;User Id=;Password=;"    oCnn.Open    'Create your recordset    Set oRs = New ADODB.Recordset    oRs.Open "SELECT * FROM Table1;", oCnn, adOpenKeyset, adLockReadOnly, adCmdText    'Add to your current workbook and add the field names as column headers (optional)    For i = 0 To oRs.Fields.Count - 1        Workbooks("Book1").Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name    Next    Workbooks("Book1").Sheets(1).Range("1:1").Font.Bold = True    Workbooks("Book1").Sheets(1).Cells(2, 1).CopyFromRecordset oRs    'Clean up ADO Objects    oRs.Close    Set oRs = Nothing    oCnn.Close    Set oCnn = NothingEnd Sub

(thanks to robdog888 FAQ for this code ) .. but what this code is doing is that its copying excel values to already created access data base ... but in my case i want this Working.mdb to be created aswell at run time , also a table with any arbitrary name is created .. and then copy values from excel to acess ( this is what i inferred from this code . i might be wrong )

I have my data in Data.xls in worksheet "Results" , stored at same path as application , data is in columns A , B , C , G , H ... Field names are in first row and field values in cells beneath it ...

Regards , Waiting

[Access VBA] Adding WorkSheets To Excel
I am trying to programatically add worksheets to Excel from Access.

This will be a different number of sheets depending on the job.

I am doing this:

Set xlAp = New Excel.Application

' add a new workbook
Set xlWB = xlAp.Workbooks.add(1)

For i = 1 To 5

' add a sheet
Set xlWS = xlAp.Sheets.add(, , 1)
Set xlWS = xlAp.Sheets(i) ' make the active sheet
xlWS.Name = "TestSheet" & i 'rename it

<write stuff in WorkSheet>

Next i
...but what I am getting is 5 worksheets, but I am overwriting the same sheet at each time thru the loop.
(the 4 others are blank)

I think I am not properly setting the active sheet with this line

Set xlWS = xlAp.Sheets(i) ' make the active sheet

Help to get this working would be appreciated.

Manipulating Access Modules From Excel
Good Morning everyone!

I need to get a reference to a Module object in an Access db so that
I can manipulate its contents. As it happens I need to do this from
Excel. In general the idea is: generate text that needs to go into a
specific module in the database, opend the db, find
the module I need and delete existing lines and insert new ones.
In other words simple, basic editing but under programmatic control.

Right now I am experimenting to find the way to get a reference to
the Module object I'm after. In the code below the xs object is
referencing the correct database and it is open as it should be.

If I look at the xs object in the Watch window I find that it does
indeed have a Modules collection and that modules collection has
a number of Items associated with it. They are all valid modules
but when I try to Set md equal to a specific module I get a Type 13

Sub Tester()
Dim xs as new Access.Application
Dim mds as Modules
Dim md as Module
xs.OpenCurrentDatabase "C:dbadirdbname.mdb"
set md = xs.Modules("a_module_name") '&lt;&lt;error 13 (Type mismatch)
End Sub

'This doesn 't work either:

Sub Tester()
Dim xs as new Access.Application
Dim mds as Modules
Dim md as Module
xs.OpenCurrentDatabase "C:dbadirdbname.mdb"
set mds = xs.Modules '&lt;&lt;error 13 (Type mismatch)
set md = mds("a_module_name")
End Sub

What am I missing here?

Thanks Plumer

Access Report, Separate Excel Worksheets
I have an Access report that's separated by Location (two business center locations) and supervisor name. If I export the report into Word, it shows up nicely. However, when I export it to Excel, the report look is stepped and very difficult to read. Is there a way to split the data into separate worksheets? (i.e. Location A's data goes in Sheet1, Location B's data goes into Sheet2) I've tried to re-make the report to block format and ignore the Location field, but now it doesn't group correctly. I would even settle for the report to run twice (once for Location A's data, once for Location B's) and export both into separate sheets and combine them, but even that process escapes me. Any help would be greatly appreciated!

Importing Excel Into Access (multiple Worksheets)
i have written code that will import multiple excel worksheets from multiple excel files. but i have to name the specific worksheet im wanting to import from.

is there a way to import multiple excel worksheets without having to name the specific worksheet?

i have excel files that may have 1 or more worksheets and they will have different names.

i tried searching and did not find anything that would help.


Import Data From Excel Worksheets Into Access
Hello all,

I have to read data from an Excel workbook into Access table. This workbook has around 30 worksheets. I used the transferspreadsheet method and loaded data from 1 worksheet into Access. But how do I look thru each worksheets and read the data into access. Is there any other method besides transferspreadsheet method?

The columns for every worksheet is the same but the number of rows are different.



Manipulating Other Sheets In A Workbook From Embedded Controls In A Different Sheet
can anyone please tell me why this code keeps crashing????...

i have used code like this about 332 thousand times and now i get a RT 1004...

i using excel with embedded controls from a sheet called command form and i'm trying to manipulate another sheet in the same book called expense tracker... when i move to the expense tracker sheet the code works perfectly...
however i'm trying to run it from the command form...

if i add a sheets("Expense Tracker").select
it of course works great... but i would rather not have to select the sheet to manipulate it...HELP!

Private Sub CommandButton3_Click()

With Sheets("Expense Tracker")
.[J201].End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=12
End With

End Sub

Importing Excel Worksheets Into Access Tables Programatically

I'm trying to import excel worksheets into existing Access tables using VB6
(not Access' or Excel's VBA). I have used ADO to connect to Excel
successfully, as well as to connect to the Access DB. What I want to do is
take the individual sheets of the XL workbook and import them into the
matched-format tables in Access.

There is a wealth of info on doing this in Access' VBA (which I can't use
for this project), and doing it manually is pretty easy as well (which is
not the point!), but there must be a way to do it in VB!

I'm thinking that a recordset containing the contents of a sheet should be
easy to read into the corresponding Access table, but I can't find an
example of this approach anywhere. Does anyone have an idea how I might

Thanks in advance! Have a good weekend!


Here's the function to gain entry into the Excel workbook (it works):

Private Function GetExcelConnection(ByVal Path As String, _
Optional ByVal Headers As Boolean = True) As Connection
Dim strConn As String
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"Extended Properties=""Excel 8.0;HDR=" & _
IIf(Headers, "Yes", "No") & """"
objConn.Open strConn
Set GetExcelConnection = objConn

End Function

And here's the code that cracks into the Access DB (just populating 6
comboboxes for now, it also works):

Private Sub LoadMyCBOs(MyCBO As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim countit As Integer
Dim oCBOControl As ComboBox 'new System.Windows.Forms.Control.
Dim cboStringName As String
Dim dbPathTo As String

dbPathTo = "C:projectsfor_alPPE_BUILDERvbcontract_builde r.mdb"

Dim cString As String
cString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & dbPathTo &

Set cn = New ADODB.Connection
cn.Open cString
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM PPE_CATEGORY", cn, adOpenKeyset, adLockReadOnly
If rs.RecordCount > 0 Then
Do While rs.EOF = False
cboCategory1.AddItem rs!ContractCategory
cboCategory2.AddItem rs!ContractCategory
cboCategory3.AddItem rs!ContractCategory
cboCategory4.AddItem rs!ContractCategory
cboCategory5.AddItem rs!ContractCategory
cboCategory6.AddItem rs!ContractCategory
End If
Set rs = Nothing
Set cn = Nothing

End Sub

Saving An Embedded Excel Object From Access OleObject To A File
The following code fragment is my attempt to extract embedded documents from an Access database and save them as seperate files.
I am working in Access 2002, where the code works but it doesn't work in Access 2003 where I get an Excel 1004 Error. Ultimately, I want the file in Access 2000 format so it can be used by 2000-2003 users. When it breaks into Debug mode and I step through line by line it works fine.
Does anyone have any ideas? I've tried several ways of calling the Save As and keep getting the same error?
Open to better ideas, I need to repeat this process for Word and Powerpoint documents as well.
Also does anyone know why Access 2002 - 2003 doesn't let you embed Word documents into a table?

Private Sub Command0_Click()
'On Error GoTo err_Section

Dim objOLE As Object
Dim rs As Recordset
Dim strSQL As String
Dim strAppName As String
Dim strFilename As String
Dim wkb As Excel.Workbook

strSQL = "SELECT FileObject FROM tblFileObjects"
Set rs = CurrentDb.OpenRecordset(strSQL)
Do While rs.EOF = False
'Identify file type
Me.OLEBound = rs!FileObject
Set objOLE = Me.OLEBound.Object
strAppName = objOLE.Application
MsgBox objOLE.Application
'Filename assigned
strFilename = GetFileName()
If strAppName = "Microsoft Excel" Then
Do While Dir(strFilename & ".xls") <> ""
strFilename = strFilename & "1"
strFilename = strFilename & ".xls"
Me.OLEBound.Action = acOLEActivate
Set wkb = Me.OLEBound.Object.Application.Workbooks(1)
'wkb.SaveAs strFilename
'Tried this line too - same result
Forms![frmOleform].OLEBound.Object.Application.Workbooks(1).SaveAs "strFilename"
Set wkb = Nothing
Exit Do
End If
'If strAppName = "Powerpoint Presentation" Then
'Similar process for Powerpoint
'If strAppName = "Word Document" Then
'Similar process for Word.
End Sub

Populating An Excel Worksheet Embedded In An Access Report With Data
IS IT POSSIBLE TO MANIPULATE DATA INSIDE AN EXCEL WORKSHEET THAT IS EMBEDDED IN AN ACCESS REPORT??? I'm beginning to think not. I've been trying for 2 days with no success.

I inserted worksheets in various ways, but let's say I insert a worksheet by doing the following:

In report design mode --> Insert --> Object... --> Create New --> Microsoft Excel Worksheet.

Now, I have a query. I want to populate the embedded, or linked, excel worksheet with the results of my query.

Access cannot create diagonal column headers which my client REQUIRES, and the "Rotate Text" ActiveX control is not clean enough. So, I am using an Excel worksheet for the formatting only. I would import data into the Excel Worksheet from Excel rather than Access, but unfortunately, since my query is a complex crosstab query with a parameter, I get errors when I try to write the SQL statement in Excel. Therefore, I need to embed the worksheet in my Access Report and populate it with data programatically. So far, all my attempts have failed.

At this point I've removed all the unnecessary complexity and all I have is this:

1 Table with two columns (id, name)
1 Query getting names from the table
1 Report with an embedded Microsoft Excel Worksheet

Can somebody please tell me how to get the names into the worksheet in the report?

Populating An Excel Worksheet Embedded In An Access Report With Data
IS IT POSSIBLE TO MANIPULATE DATA INSIDE AN EXCEL WORKSHEET THAT IS EMBEDDED IN AN ACCESS REPORT??? I'm beginning to think not. I've been trying for 2 days with no success.

I inserted worksheets in various ways, but let's say I insert a worksheet by doing the following:

     In report design mode --> Insert --> Object... --> Create New --> Microsoft Excel Worksheet.

Now, I have a query. I want to populate the embedded, or linked, excel worksheet with the results of my query.

Access cannot create diagonal column headers which my client REQUIRES, and the "Rotate Text" ActiveX control is not clean enough. So, I am using an Excel worksheet for the formatting only. I would import data into the Excel Worksheet from Excel rather than Access, but unfortunately, since my query is a complex crosstab query with a parameter, I get errors when I try to write the SQL statement in Excel. Therefore, I need to embed the worksheet in my Access Report and populate it with data programatically. So far, all my attempts have failed.

At this point I've removed all the unnecessary complexity and all I have is this:

1 Table with two columns (id, name)
1 Query getting names from the table
1 Report with an embedded Microsoft Excel Worksheet

Can somebody please tell me how to get the names into the worksheet in the report?

Excel Add-ins - Making With Modules Only - Calling From Excel Worksheets Via Vba
I ave 2 questions;

Question ONe

How do I make a add-ins for excel with modules only. Excel says that I must have atealst one worksheet - can I make a add-ins which are not COM dlls without worksheets, ie simply se existing excel modules ?

Question Two

How do I can a function or sub procedure from a module contained in my add-in ?



Copy Excel Worksheets To New Excel Document
As the title suggests, I am trying to get one worksheet from an excel sheet and copy it into a new excel document.

I am a complete beginner with VB but have a good idea about using COM object and opening and entering data into excel, word etc. But I've never copied a who worksheet. Is it possible and where would one start?

is there a .copysheet or some kind of function I could use?

Any help appreciated.


Worksheets In Excel
For some reason my insert worksheet option is not working in excel. Even I can't unhide worksheets that I have previously hidden. Some how anthying to do with worksheets is turned off. I can't figure out what's the reason. I can't even go to file/properties anymore. This is just in one of my excel files. All my other excel files and any new ones I'm creating are not causing this problem. Does anyone know why this is happening? Thank you,


Manipulating Excel Through VB
Where to start...

I need to:
1) extract data from an SQL database,
2) manipulate and place that data into an Excel spreadsheet, &
3) run a macro in the same spreadsheet.

I have no problem with any one of these steps, but when I try to do 2) AND 3), I get headaches. The main problem is that I extract the SQL data, poke it into Excel, and display it as an OLE ok... but then I try to run the macro and it opens a different version of the same workbook and runs the macro w/o the new SQL data AND doesn't show it in the OLE. Does any of this make sense?

Below is the code I've written (w/ a small piece taken from a very helpful Mike Wardle):

Private Sub Command4_Click()
Dim oExcel As Object
Dim oWorkbook As Object
Dim sWorkbookPath As String 'Path to Workbook
Dim prod$(100)
Dim amount(100)
Dim count(100)
prod$(1) = "CC20"
prod$(2) = "CCF"
prod$(3) = "CCF-BS"
prod$(4) = "CCF-CC"
prod$(5) = "CCS"
prod$(6) = "CCS-BS"
prod$(7) = "CF7"
prod$(8) = "GNARM"
prod$(9) = "GNIIR"
prod$(10) = "GNR"
prod$(11) = "GNS"
For i = 1 To 100
amount(i) = 0
count(i) = 0
Next i

Form2.OLE1.Class = "ExcelWorksheet"
Form2.OLE1.SourceDoc = "F:DepshareMRKTINGPubQRMQRM VBPIPELINE.xls"
Form2.OLE1.SourceItem = "R1C1:R50C15"
Form2.OLE1.Action = 1
Form2.Label3.Caption = "Reading QRM Data Base......"
Form1.Text14.LinkMode = 0
Form1.Text14.LinkTopic = "EXCEL|F:DepshareMRKTINGPubQRMQRM VB[PIPELINE.xls]Sheet1"

Do Until Form1.Adodc2.Recordset.EOF = True
If Trim(Form1.Adodc2.Recordset.Fields("CLOSE_FALL")) = "" Then
quantity = Form1.Adodc2.Recordset.Fields("QUANTITY")
hit = 1
For i = 1 To 11
If Trim(Form1.Adodc2.Recordset.Fields("O_PCENTR")) = prod$(i) Then hit = i
Next i
amount(hit) = amount(hit) + (quantity * 1000)
count(hit) = count(hit) + 1
End If

Form1.Text14.Text = Date
Form1.Text14.LinkItem = "R1C3"
Form1.Text14.LinkMode = 2
For i = 1 To 11
Form1.Text14.Text = prod$(i)
Form1.Text14.LinkItem = "R" & i + 3 & "C1"
Form1.Text14.LinkMode = 2
Form1.Text14.Text = Format(count(i), "###,###")
Form1.Text14.LinkItem = "R" & i + 3 & "C2"
Form1.Text14.LinkMode = 2
Form1.Text14.Text = Format(amount(i), "#,###,###,##0.00")
Form1.Text14.LinkItem = "R" & i + 3 & "C3"
Form1.Text14.LinkMode = 2
Next i

sWorkbookPath = "F:DepshareMRKTINGPubQRMQRM VBPIPELINE.xls"
Set oExcel = CreateObject("Excel.Application")
Set oWorkbook = oExcel.Workbooks.Open(sWorkbookPath)
oExcel.Run "Skank" 'name of my public macro in Excel
Set oWorkbook = Nothing
Set oExcel = Nothing

End Sub

Any help would be greatly appreciated. Keep in mind, I'm a bit of a dolt... having never taken any instruction in VB.

Excel Worksheets
I have an application that will scan a table and sort the contents into an excel workbook. I need to be able to add sheets as I go along, but if the sheet does not exist then I need to create it, I have looked at scanning all the sheets but that can take time, is there a way of checking quickly if the sheet exists or not, maybe trying to activate it and if it does not activate use the error to try and create it. Any help would be appreciated.

Excel Worksheets
Somehow im posting a lot round these days...
So the question: How can you put values of textboxes into excel worksheets?? And print the sheet afterwards??


Worksheets In Excel
I have done some research and found that the number of worksheets allowed to be created in a workbook is based on the systems memory.

I have an automated workbook that can, and will, create 200+ worksheets.

Now, once i get to a certain amount (120 the last time) it just stopped...ven the copy function was looping, no pages were created.

BUT, when i closed out the workbook, and then re-opened it, and started again, it created the remaining worksheets. So, I am guessing it was 'holding' on to memory as it was going along.

IS there a way to release any memory it is using when it creates a worksheet, so i don't have to have to close it out, each time, and re-open it?

FYI: I already have DoEvents in all of my LOOPs.

I also have this function:

Public Function Holding(Sec As Integer)
Dim i As Long

If Sec = 0 Then Exit Function

For i = 0 To Sec
Call Sleep(1000)

End Function
And i use it after each 'grouping' of sheets to be created, wth 5 seconds as the value passed.

Excel Worksheets
I can read data from a spreedsheet, but i can't work out how to make a worksheet with in a workbook active. If i have a worksheet called "Worksheet 1" (which is the active worksheet) and want to read data from "Worsheet 2" how do i tell VB to use this other worksheet ??

Manipulating With Excel Through VB 6
Hi everybody , i am infact interested in storing data in excel fields for further manipulation .. and i am doing this in my present code .. given below are the changes i have made ( only those parts are qouted which i added for sake of excel )

VB Code:
Private Sub Command1_Click()' some other declarations here ...dim count as integercount = 2Dim oXLApp As Object         'Declare the object variablesDim oXLBook As ObjectDim oXLSheet As Object  Set oXLApp = CreateObject("Excel.Application")    'Create a new instance of Excel  Set oXLBook = oXLApp.Workbooks.Add    'Add a new workbook  Set oXLSheet = oXLBook.Worksheets.Add    'Application.ScreenUpdating = False        With oXLSheet    .Name = ("Results")    .Columns("A:H").AutoFit      End With' some other code here 'Print #fff, rsiteval, duration    'printing these values in an external file as well                     Results.cells(count, 7) = rsiteval                     Results.cells(count, 8) = durationend sub

Whas wrong till now , because when run it throws error " Run Time Error '424' Object Required "
Regards and Waiting

Manipulating Excel Through VB
Hey everyone.

Here's what I'm trying to do. I have an Excel workbook containing about 25 sheets, and I need to scan each sheet cell-by-cell and check for it's background color and text contained within. What I need help with is the following. (I have done this about 8 months ago in VBA but do you think I can find the file. ) I'd like to put it into VB anyways.

Also, I did it with half the code everyone gave me on support forums. Can we keep it short and simple please? )

What I'm really stuggling with is this....

1) How do I open this workbook through VB so that I can even begin scanning through it.

2) Once the workbook is open, how to I scan from sheet to sheet?
3) How do I scan from cell to cell within a given range?

4) How do I close the workbook so I can open it on it's own when the VB Program isn't running. (Early trys at opening the file result in it not getting closed properly)

The way I did this before involved 2 loops I think. One loop to scan through the worksheets and then inside that loop I have a loop to scan through the cells.

Basically it looked like...

Loop Through Worksheets
If not new sheet then
Loop through Cells
Next Worksheet

Thanks in advance for any help!

Excel Worksheets
I am trying to create a .csv file that has 2 worksheets. I can create the file, write to the first work sheet, create a second worksheet but when I try to write to the second worksheet the program overwrites the first worksheet. How do you write to the newly created second worksheet!???


Add Worksheets To Excel Via VB
I need to create multiple excel sheets into one workbook. Doing one sheet has not been a problem, but when I try to add sheets to the same workbook I get all kinds of garbage and errors. Can anyone help?

Excel Worksheets

I have created a time clock program to track employee hours. The program saves the employees time in and time out to a .dat file. I then have VB call excel to create a report and add the employees hours for a time period. Currently I have one pre-formatted worksheet in Excel, and the program writes just the selected employees hours. I want to make it so VB copies the main formatted worksheet for all selected employees, so I'll be able to list each employee to a seperate worksheet. Any help will help.

Thank you,

Larry B.

Print Excel Worksheets In VB6
Hello everybody.
I need to be able to print an excel worksheet in my application written using VB6.
In a thread of this forum i found a chunk o code that lets me do that, which is:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Private Sub Command1_Click()
ShellExecute Me.hwnd, "Print", "c:/test.xls", vbNullString, "C:", SW_SHOWNORMAL
End Sub

- I need to put an header and footer on the page before and after the excel worksheet
- I need to be able to let the user choose which worksheet he wants to print (choosing from several available in the excel file)

Can anybody help me?

Thanks in advance


Listviews To Excel Worksheets
I have a SStab which has five tabs on it. Each tab has it's own listview. I want to take the listviews and create an excel file from it. I want each different listview to be it's own worksheet within one excel file. I've figured out how to do it for one. I'm not sure how I take the code I have now and change it so I can make numerous worksheets within the same excel file. Help please.


Private Sub goThough()
Dim intIndex As Integer, counter As Integer, strwork() As String
Dim itmWork As ListItem

With lstBooks
ReDim strwork(.ListItems.Count, .ColumnHeaders.Count)
For Each itmWork In .ListItems
strwork(counter, 0) = itmWork.Text
For intIndex = 1 To .ColumnHeaders.Count - 1 'Go though headings
strwork(counter, intIndex) = itmWork.SubItems(intIndex) 'Loop though subItems
Next intIndex
counter = counter + 1
Next itmWork
End With
Call writeXL(strwork)
End Sub

Public Sub writeXL(strwork() As String)
Dim I As Integer, J As Integer, R As Integer, C As Integer
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add 'Create WorkBook
Set xlSheet = xlBook.Worksheets(1) 'Set WorkSheet to 1
For I = 0 To UBound(strwork, 1)
For J = 0 To UBound(strwork, 2)
'Debug.Print strwork(I, J)
xlSheet.Cells(I + 1, J + 1) = strwork(I, J)
xlApp.Visible = True
End Sub

Counting Worksheets In An Excel App
How do I return the number of worksheets in an excel workbook that was created programmatically?

Thanks for you help!

Manipulating Fonts In Excel
I have a VB program that is creating a spreadsheet. I want to change the font and cell background color on certain cells.
I am having trouble achieving this. I have tried the following:

InvXLSheet.Cells.Font.Color = vbYellow (Didn't work)
InvXLSheet.Rows.Font.Background = vbYellow (Didn't work)
InvXLSheet.Rows.Font.Bold = True (Didn't work)
invXLSheet.Cells(1,X).Font.Color = vbYellow (Didn't work)

Nothing I do seems to be working. I'm obviously going about this the wrong way and I am wondering if anyone lead me in the right direction.

Naming Worksheets In Excel
i'm looking to import data into a worksheet and i need to rename the worksheet dependent on the name of the file being imported.

for example: if i import 'test' i need the worksheet it imports onto to be callled 'test'

but i don't want to have to create coding for each individual report, simply because we need users to add their own imports and such.

any ideas?

Excel: Saving Certain Worksheets
I have an Excel workbook where a program collects data from the comports. If data comes in on com1 it writes the transaction to sheet106, if data comes in on com2 it saves data to sheet206 and the same for com3 and sheet306.

Currently after 300 transactions i save the spreadsheet with a name that is time and date stamped, then i clear the transactions. My problem is when i do a ActiveWorkbook.SaveCopyAs (filename) I save the vba program and everything, what i would really like to do is just save the data in a new spreadsheet.

Is there a way to save just the worksheets where the data resides without having the vba program exist in the new excel spreadsheet.

Moving Worksheets In VBA (Excel)
I am trying to move a worksheet from one workbook into another, and then saving that workbook. so far i'm using:

sub move_sheet()

Workbooks.Open FileName:=AADirectory(LoopMeAround) & "" & ExcelFileName(j)
ActiveSheet.move Before:=Workbooks(TempWorkBookName).Worksheets(TempSheetName)
Workbooks(ExcelFileName(j)).Close SaveChanges:=False

end sub

well its that sort of syntax. - Using the VBA Move command
(the above might not work, as i'm just typing this off the top of my head). Now what i'm using now, works , but i am creating a lot of worksheets and loads of new workbooks (about 50 workbooks, and each will contain about 70 worksheets.)
My problem is, that Excel keeps on crashing (i get a dr. watson error). I need to build more stability into the coding, perhaps via this API thing i've heard of?
or am i better off creating a worksheet object?
comments, suggestions, and the relavent coding would be most appriciated.

Many Thanks,

PS ho do i use the vb tag/scrip commands to get different colours on this post?

Using VB To Create Excel Worksheets

I have a VB app, which creates multiple workbooks in one folder.

I would like to take each workbook (they ony have one sheet in each) and combine them into one workbook.

So far I have found this code from another site.(

It seems to work fine if I use it in excel, but I would like to use it from VB.

i have the references setup, but when it loops through the first time it then crashes out.

Any ideas? please

Dim oExcel As Excel.Application
Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long

Set oExcel = New Excel.Application

Application.ScreenUpdating = False
With Application.FileSearch
.LookIn = "c:xlsdata" ' This is the folder containing the workbooks
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks 'msoFileTypeExcelWorkbooks

If .Execute() > 0 Then

Set basebook = oExcel.Workbooks.Open("c:xlsdataSummary_AllAreas.xls") 'ThisWorkbook to hold all sheets
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name
Next i
End If

End With
Application.ScreenUpdating = True

thank you

Re-number Excel Worksheets
Is there a way to re-number the worksheets in VBA? I am using a sub that hides each of my worksheets, except for the last one, but it does this by adding 1 to an integer variable, and then hiding sheet(i). Unfortunately, I have removed quite a few sheets, so my worksheet numbers are no longer in consecutive order. For instance, when I look at the project explorer, it looks like this:

Sheet1(Name of Sheet 1)
Sheet10(Name of Sheet 10)
Sheet2(Name of Sheet 2)
Sheet5(Name of Sheet 5), etc.

You see that Sheet3 and Sheet4 are no longer there, so when I run the sub that I mentioned above, I get a "out of range" error, because it gets as far as Sheet2, then it adds one, so that it can hide Sheet3, but Sheet3 is no longer there. Is there any way to re-number them, so that they will run in consecutive order again?

I even tried creating a new workbook and copying all of the sheets (which turned out to be a giant pain in the ***, because it screwed up all of my references, cell names, etc.) and each sheet maintained the sheet number that it had in the old file.

Manipulating Excel From VB (Newbie)
I'm trying to use Excel from VB.
I've created a basic VB program which produces two lists of numbers.
I would like to put these two lists in two coloumns in an Excel file, create a graph using them
(histogram), then view the graph, all clicking only one button on my form.
I've read the tutorial "Automating Excel from..." but I don't know how can I order VB to create the graph
and let me view it while the VB program is running.
Help me please...
Sorry for my bad english...I'm Italian!

Manipulating Excel From VB (Newbie)

I am also trying to use Excel from VB.

I can open, read and write to the Excel file but only as long as I am in the same subroutine.
I want to be able to open the Excel file in the form load module then manipulate it from other subroutines. What am I doing wrong?

Here is my code so far

Option Explicit
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim va As String

Private Sub Form_Load()

Set xl = CreateObject("Excel.Application") 'creates application object

xl.Visible = True 'opens xl file in the background only

xl.Workbooks.Open FileName:="\Murr-server1ACGNew project DB
eference.xls", _
UpdateLinks:=3 'opens xl file and updates links without prompting

Text1.Text = xl.UserName 'places username in the right place on the form

'ActiveWorkbook.RunAutoMacros Which:=xlAutoClose 'closes xl without saving
End Sub

Private Sub Command1_Click() 'close the excel file
Set xl = CreateObject("Excel.Application") 'creates application object
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose 'closes xl without saving

End Sub

Manipulating Excel From VB (Newbie)
I'm really new to VB.NET, so this may seem a dumb question. But I want to simply manipulate cells in a specific sheet(s) from a VB program.
Also creating a new one on the fly would be great too.

I'm somewhat overwhelmed ! Help.

Compare Worksheets In Excel
I need to write a VB app that will take two excel worksheets and compare them and show the differences.

Worksheet 1

Compare columns A and B with the same columns of worksheet 2 and show the differences.

Is this a very difficult task or can anyone point me in the right direction?

Iterating All Worksheets In Excel
This is to be working for me just fine for the FIRST workbook I open, then if I try to open another, the For Each s in Sheets throws this error:
Method 'Sheets' of object '_Global' failed

I've done quite a bit of interfacing to Excel but I always get confused with the Workbook, Worksheet, etc. Declarations. Maybe I need another?

VB Code:
Option Explicit Dim objExcel As New Excel.Application Private Sub Command1_Click() On Error GoTo ErrHandler         Dim s As Worksheet   Dim Filepath As String   Dim SheetNames(25) As String      dlgCommon.DialogTitle = "Open File"   dlgCommon.Filter = "Excel Files(*.xls)|*.xls"   dlgCommon.ShowOpen   Filepath = dlgCommon.FileName   With objExcel      .Workbooks.Open Filepath      For Each s In Sheets         .Sheets(s.Name).Select         GetSum      Next            .ActiveWorkbook.Save      .Workbooks.Close   End With   MsgBox ("DONE") ErrHandler:   Debug.Print Err.Description   Debug.Print Err.Number   objExcel.Workbooks.Close   objExcel.Quit   Set objExcel = NothingEnd Sub Private Sub GetSum()      Dim Cell As Integer      With objExcel      .Range("E100").Select      .Selection.End(xlUp).Select 'ctrl      Cell = Right(.ActiveCell.Address, 1)      .Cells(Cell + 2, 5) = "=SUM(E1:E" & Cell & ")"   End WithEnd Sub Private Sub Form_Unload(Cancel As Integer)    objExcel.Quit   Set objExcel = NothingEnd Sub

Iterating All Worksheets In Excel
How can I iterate all the worksheets in Excel with the Macro->Visual Basic? Or does this matter if it's a Macro or not. Not totally familiar with VB6 so bear with me.

Reading Excel Worksheets
I have data to import from a users excel file.

In my utility the user says what they are going to import and points me to an xls file

Without using the excel object (which means excel has to be installed on the pc) is there any way of reading from a specific sheet in an excel file without having to hard code the name of the sheet into the application?

Some users change the name of the sheet as it better suits them

Naming Worksheets In Excel
I'm looking to import data into a worksheet and i need to rename the worksheet dependent on the name of the file being imported.

for example: if i import 'test' i need the worksheet it imports onto to be callled 'test'

but i don't want to have to create coding for each individual report, simply because we need users to add their own imports and such.

any ideas?

VB Code:
Sub Auto_Open()'' Macro1 Macro' Macro recorded 25/08/2005 by User  ' Deletes Sheets 2 and 3 in Workbook Sheets("Sheet3").SelectApplication.DisplayAlerts = FalseActiveWindow.SelectedSheets.DeleteApplication.DisplayAlerts = TrueSheets("Sheet2").SelectApplication.DisplayAlerts = FalseActiveWindow.SelectedSheets.DeleteApplication.DisplayAlerts = True ' Imports Data into Sheet 1 With ActiveSheet.QueryTables.Add(Connection:="TEXT;C: est.csv", Destination _:=Range("A1")).Name = "test".FieldNames = True.RowNumbers = False.FillAdjacentFormulas = False.PreserveFormatting = True.RefreshOnFileOpen = False.RefreshStyle = xlInsertDeleteCells.SavePassword = False.SaveData = True.AdjustColumnWidth = True.RefreshPeriod = 0.TextFilePromptOnRefresh = False.TextFilePlatform = 437.TextFileStartRow = 1.TextFileParseType = xlDelimited.TextFileTextQualifier = xlTextQualifierDoubleQuote.TextFileConsecutiveDelimiter = False.TextFileTabDelimiter = True.TextFileSemicolonDelimiter = False.TextFileCommaDelimiter = True.TextFileSpaceDelimiter = False.TextFileColumnDataTypes = Array(1, 1, 1).TextFileTrailingMinusNumbers = True.Refresh BackgroundQuery:=FalseEnd With End SubEdit/Delete Message

Hiding Excel Worksheets With VBA
I am using the following code to save a chart as a GIF file and then to load it into an Image box within a Userform:

Set CurrentChart = Sheets("Create Charts Here").ChartObjects(1).Chart
Fname = ThisWorkbook.Path & " emp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"
' Load *.gif file into Image box
Image1.Picture = LoadPicture(Fname)

Then I try to use the code below to hide all but one of my sheets in the Workbook. But when "Sheets(i).Name = "Create Charts Here"" I get the error message - Method 'Visible' of object'_WorkSheet' failed. Any ideas why the sheet "Create Charts Here" is causing a problem?

Public Sub SheetVisible(ByVal unhideName As String)
Dim shtVisible As String
Application.ScreenUpdating = False

For i = 1 To Sheets.count
' Excel always needs to have at least 1 sheet visible
' First make all sheet tabs visible
If Sheets(i).Name = Sheets.Item(1).Name Then
If Sheets(i).Name <> unhideName Then
Sheets.Item(1).Visible = True
i = i + 1
End If
End If
' Then close all but target sheet
If Sheets(i).Name = unhideName Then
Sheets(i).Visible = True
Sheets(i).Visible = False
End If
Next i

If Sheets.Item(1).Name <> unhideName Then
Sheets.Item(1).Visible = False
End If

End Sub

Vb, Excel And Multiple Worksheets
the workbook i've been using in my vb app has just been changed from having 1 worksheet to having many worksheets. the problem i have encountered is when i do my application.calculate not all worksheets get updated. is there a solution to this?

Accessing Excel Worksheets
let's say i have about 5 worksheets in an excel file.....
now how do i get the names of each of the worksheets.....

for example :- January, February, March, April, May are the worksheet names

i'm trying to get these names and store them in a combo box so that a user may navigate through the worksheets that is displayed in an OLE control..... if possible can someone share a solution for scrolling in an OLE control and also switching worksheets?

this is quite a tough one and the forum doesn't seem to have many ppl who know the solutions

Copying Excel Worksheets
Would anyone have an idea how to copy the values from one workbook to another workbook using VB??? I know that it I use PasteSpecial, but it doesnt seem to be copying the data correctly.

Code is as follows:

Public Function RunMacro(ByVal strWorkbook As String, ByVal strMacro As String, ByVal strSaveAs As String) As Boolean

Dim objWorkbook As Workbook, objWorkbookCopy As Workbook
Dim sNew As Worksheet
Dim sOld As Worksheet
Dim i As Integer

Set m_appXL = New Excel.Application

With m_appXL
.Workbooks.Open strWorkbook, False, False
Set objWorkbook = m_appXL.Workbooks(1)
.Run strMacro
If strSaveAs <> "" Then
Set objWorkbookCopy = m_appXL.Workbooks.Add
Set objWorkbookCopy = m_appXL.Workbooks(2)

For i = 1 To objWorkbook.Worksheets.Count - 1
Set sOld = objWorkbook.Worksheets(i)
Set sNew = objWorkbookCopy.Worksheets(i)
Next i

End If
End With


Switch Between 2 Excel Worksheets
Hi I am writing a vb6 program to populate two different excel sheets. Here is my code for opening the workbook and setting my excel object to something:

Dim objXL As Excel.Application
Dim objSHEET As Excel.Worksheet
Dim objWBOOK As Excel.Workbook
Dim xlR As Integer ' Excel Row
Dim xlC As Integer ' Excel Column

'--Open Excel Report Template--
Set objXL = New Excel.Application

objXL.Workbooks.Open "P:QualityDataMeltshopReportsNewLMF.xls"
objXL.Visible = False

'--Point to Data Sheet--
Set objSHEET = objXL.Sheets("SUMMARY")

This code is fine and it works to populate the sheet called SUMMARY, but after I'm done populating this sheet, I would like to open a different sheet for population. My code for that is this:

Set objSHEET = objXL.Sheets("AlloyCost")

However, the bold line gives me an error saying subscript out of range. The sheet AlloyCost does exist in the workbook and it is spelled correctly with the right syntax. Is there a problem with my excel declarations or is there possibly a problem with protection on the workbook? Any help would be greatly appreciated. Thanks!


Sorting Worksheets In Excel
Hello all,
The workbook I am using has about 60 worksheets. I have found subroutines that will sort either by tab color or by worksheet name alphabetically. I would like to sort by tab color, then by name in one subroutine. Can this be accomplished?

Thanks for any help,


Problem With Excel Worksheets
I have an Excel Workbook with two existing Worksheets. I progammatically add another 4 sheets. Now comes the problem. I can't seem to make the sheet named "LWOP" active so that I can work with the sheet. Current code is as follows:

Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4

Sheets(3).Name = "LWOP"

Sheets(4).Name = "PTHRS"

Sheets(5).Name = "PAID"

Sheets(6).Name = "UNPAID"
'Create Headings

Worksheets("LWOP").Select '<<==== does not make the sheet active!!!

Range("A1") = "Member Surname and Given Name(s)"

Range("B1") = "01/01/1988 to 30/06/1988"

Range("C1") = "01/07/1988 to 30/06/1989"
What actually happens is that the 1st sheet (that was shown when the workbook was opened) cells A1, B1, C1 are modified instead.
The question is: How do I make a newly created worksheet active so that I can work with the cells by referencing them as above. I've also tried Worksheet("LWOP").Activate, but this didn't work either.

Copyright 2005-08, All rights reserved