Create Object, Work With Workbook
Oct 12, 2008
I'm trying to open a file and perform various tasks. This is the structure:
Dim xlApp As Object
Dim xlWb As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open(BackupFile) ' backupfile evaluates to c: esting est.xls
xlWb.Worksheets(1).Activate
Debug.Print xlWb.Worksheets.Count
Dim ws As Worksheet
For Each ws In xlWb.Worksheets
Debug.Print ws.Name
ws.Activate
Next ws
I think I'm totally missing something. The above works, except for the loop. None of the names show up in my immediate window, and none of the worksheets are activated. xlWB evaluates to nothing, which is where the problem has to be...but why would my first .Activate and Debug.Print lines work? Also, If I declare xlWb as Workbook then absolutely nothing works after the file is opened.
Basically what I want to do is open the file, do a bunch of a stuff with it, then save and close it. I have this all working without creating another object, but the opening of the file is still visible to the user. Seems like turning off ScreenUpdating doesn't truly work.
View 9 Replies
ADVERTISEMENT
Apr 21, 2009
I'm trying to write a remove duplicates sub that can be passed the worksheet name and columns on which to check. It's pasted below. Above the key line is a commented out line of code that worked. So it works to pass theh worksheet, but I'm hung up on how to pass varying columns to it.
Sub RemoveDuplicatesSub(wksht, cols)
'Remove duplicates.
'Assumes that the data range is in a table
'Assumes the header row starts at row 7
Dim WS As Worksheet
Dim TableName As String
Set WS = Worksheets(wksht)
TableName = WS.ListObjects(1).Name
'WS.Range(TableName).RemoveDuplicates columns:=Array(3, 4, 5, 6, 7, 8, 9, 10), Header:=xlYes
WS.Range(TableName).RemoveDuplicates columns:=Array(cols), Header:=xlYes
End Sub
View 9 Replies
View Related
May 27, 2014
Please see attached files.
Book1.xlsx
test.xlsm
I am trying to copy the emf image object from "Book1" into the "test" workbook whilst the test workbook is open and Book1 is closed. The code I have put together currently sort of works, I mean that it copies the text data over but does not copy the object which is what I require.
View 2 Replies
View Related
Jul 28, 2009
I copied a routine from this forum that would email a single sheet from a 10-sheet workbook to a recipient. This worked great for a long time, then all of a sudden I started getting the "ActiveX control can't create object" error. Here is my
View 14 Replies
View Related
Jul 13, 2007
I am having trouble with the following code... I get error # 429 Active X can't create the object...
Dim blnIOpened As Boolean
Err.Number = 0
On Error GoTo failed
blnIOpened = False
Set MyXL = GetObject(, Excel.Application)
failed:
If Err.Number = 429 Then
Set MyXL = CreateObject(Excel.Application)
blnIOpened = True
End If
If Err.Number 429 Then
MsgBox Err.Number
End If
View 9 Replies
View Related
Feb 7, 2007
I want to create a macro to open a webpage, and with that webpage fill out the fields on the webpage and then hit enter and move to next record. I am having great difficulties with establishing Internet Explorer as my object and maneuvering around inside of it. My data would being in column A so i would expect to use the string below when referencing my data.
Do Until Workbooks(2).Worksheets(1). Range("A" & row) = ""
View 9 Replies
View Related
Oct 16, 2008
I'm trying to learn about class modules and how to create object that i can manipulate within a sheet.
i'd like to create a simple cirlce object that i can visibley see on a sheet.
is this possible and if so any idea on how i start?
View 3 Replies
View Related
Apr 10, 2007
Create Range Object & Pass To A Subroutine
Sub Test(ByRef objRange As Range)
objRange.Value = "Hi"
End Sub
Sub TestTheTestMethod()
With ThisWorkbook. Sheets("Sheet1")
Set objRange = .Range(.Cells(1, 1), .Cells(i - 1, 3))
objRange.Value = "Hi" 'This works fine !
Test (objRange) 'But here... Getting ERROR 424 -- Object Required
End With
End Sub
View 2 Replies
View Related
Jul 11, 2014
Each row of the spreadsheet from a6:a25 is the planned production of one cellbuilder.
From this sheet I need to produce a work schedule of tasks to be performed by date.
Detailing, batch, cellbuilder number,weather 1st or 2nd graft etc, and the date available (for picking)
I would like the option to choose a daily or weekly schedule.
Is this possible ? and how would I even start to go about it. I just can't visualise how to do it.
View 1 Replies
View Related
Jun 9, 2009
Create a workpattern using something similiar to the Networkdays function. I want to be able to calculate how many working days are between 2 dates if the employee works a non standard Monday to Friday Week. For Example if someone works ever Mon, Tues, Wed how many working days are there for them in June.
View 11 Replies
View Related
Jan 26, 2010
I am trying to generate excel report output from access by click of a button. Part of the code is as follows:
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xlc As Object
Dim DBS As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
blnEXCEL = False
'If we do not want the first row we set Header to false
blnHeaderRow = False
'Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
I worked on development on a server and worked fine. However when I have moved the code and database to the desktop of mine I am having
the following error in the line
Set xlx = GetObject(, "Excel.Application")
Run-time error '429'
ActiveX component can't create object
View 10 Replies
View Related
Dec 8, 2012
Following bit of code runs fine if placed in Word VBA:
Code:
Public Sub TaskUsageInExcel()
Dim tsk As Task
For Each tsk In Tasks 'Gives RTE 429 when run through Excel
Debug.Print tsk.Name
Next tsk
End Sub
So I referenced 'Microsoft Word 12.0 Object Library' in Excel and ran the code and it gave me this error 429. Noticeable part was, no instance of word was running at that time.
So I modified the code as below:
Code:
Public Sub TaskUsageInExcel2()
Dim wdApp As Word.Application
Dim tsk As Task
[Code]....
View 4 Replies
View Related
Nov 11, 2009
Ive got a problem with a work book im working on at the moment, my company has various different documents created in excel, like a price list, cost price list & stock levels (all Independant) I have had the idea to join them all into a single shared workbook using an idea I found and modified over a year ago on this site, that had the promise of allowing multiple users to see the nessesary parts of the same document. the advantage of this being any new parts or prices or suppliers added would update all users at once. So ive got this workbook that when you open you get asked for a username & password this then hides/unhides, protects or unprotects to suit the users needs. This works great when unshared but when I share it the login box wont display
The Administrator username is jamie and the password is joshua
View 10 Replies
View Related
Nov 4, 2009
How do I create a date stamp in a work sheet, if lets say anything gets revised or updated the date gets updated as well?
For Example: I want to put the date stamp in A1. So when anything gets revised in the document, A1 will have the date when that document was revised.
View 9 Replies
View Related
Jan 4, 2007
VBScript code snippet below is being used in an ASP page but the call to GetObject never attaches to a running instance of Excel. Since visible property is set to True, I can see the instances accumulate in the TaskBar each time the ASP page is called, so there are obviously instances to attach to.
Dim excelApp
Set excelApp = Server.GetObject(,"Excel.Application")
If (err <> 0) Then
Set excelApp = Server.CreateObject("Excel.Application")
End If
excelApp.UserControl = True
excelApp.Visible = True
excelApp = Nothing
GetObject errors out with the following error: Error - Number:429 Source:Microsoft VBScript runtime error Description:ActiveX component can't create object
View 3 Replies
View Related
Aug 3, 2009
I know how to create a text link to other cells within a work book but how can I create a button which will do the same?
View 2 Replies
View Related
May 25, 2014
How to Create a Work Breakdown Structure (WBS) in Excel automatically with given data through macros.
In the attached excel sheet
work_breakdown_structure_.xls
Is there any way to do it for data automatically?
View 1 Replies
View Related
Oct 11, 2006
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.
Sub ConsDiscChart()
ActiveCell.Offset(29, 11).Range("A1").Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(0, -1).Range("A1:C24").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub
View 5 Replies
View Related
Jun 20, 2014
I am trying to create a spreadsheet that will automatically increase the work order number by 1. The cell will always be in the same place. The idea is that when the file is opened it populates the number. After being closed and reopened the number will be 1 higher than the previous.
The page will reused by various people to create and print work requests for my mechanics, I want it to assign the W/O automatically.
View 11 Replies
View Related
Aug 11, 2008
I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error:
Run-time error '429':
ActiveX component can't create object
and is stopping on the line
Set OutApp = GetObject(, "Outlook.Application")
FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error.
Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String
'Set OutApp = CreateObject("Outlook.Application")
Set OutApp = GetObject(, "Outlook.Application")
If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
View 9 Replies
View Related
Feb 24, 2013
How to include the workbook name in this piece of code?
Code:
sh1.Range(sh1.Cells(ii, k + 10)).Address
View 1 Replies
View Related
Jul 23, 2013
I have some user defined function in an .xlam file that works.
Right now since that .xlam file doesn't auto open. I have a button on specific workbooks that will open that xlam so all the udfs work.
Is there a way to embed the udfs into a specific workbook so that workbook is a standalone without the need for opening a separate xlam file?
View 7 Replies
View Related
Nov 25, 2012
When I first open a workbook most of the functions don't work. for example the vertical scroll bar does not move. When I close the workbook and then reopen it for the second time it works fine.
Happens with any workbook I have.
View 2 Replies
View Related
Aug 1, 2014
For some reason I'm consistently getting an error in the following line:
Code:
Public wkbk1 As Workbook
Public shtInput As Worksheet
Public i As Integer, iPass as Integer
[Code]....
View 6 Replies
View Related
Jun 18, 2006
I am trying to compare two workbooks and am running into the problem that since I declared my two workbooks, I am limited in the methods that I can use. Here is all of the code so far.
Option Explicit
Sub UpdateMasterFile()
Dim wbMaster As Workbooks
Dim wbEmailed As Workbooks
Dim wsPC As Worksheet
Dim Master As Long
Dim Emailed As Long
Dim intMaster As Integer
Dim intEmailed As Integer
Set wbMaster = Workbooks("Master Info.xls").Sheets("PlantsCom")
Set wbEmailed = Workbooks("EmailedData.xls").Sheets("NewInfo")
Master = Workbooks("Master Info.xls").Sheets("PlantsCom"). Range("a65536").End(xlUp).Row
Emailed = Workbooks("EmailedData.xls").Sheets("NewInfo").Range("a65536").End(xlUp).Row..............
View 2 Replies
View Related
Oct 11, 2013
I want this private sub macro to change work in all workbook. How it possible?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
With Target.EntireRow.Interior
.ColorIndex = 37
.Pattern = xlGray25
.PatternColorIndex = 24
End With
End Sub
View 4 Replies
View Related
May 29, 2007
I'm trying to get a Workbook.Open macro to work. I"m using the exact name for the location. For example:
Workbooks.Open ("O:AdministrationPM Weekly's2007PMWeeklyWE")
My other locations are working fine. For example I am using:
Workbooks.Open ("O:AdministrationDCS Week EndingStats"
View 9 Replies
View Related
Apr 21, 2009
I am using Excel 2003.
I tried to change the number of new sheets in a new workbook, e.g. from 3 to 1.
But the option has no effect on new workbooks. They still show 3 sheets.
View 9 Replies
View Related
Sep 24, 2009
I have the below macro that works well
Sub Button68_Click() 'Creates delivery note
Dim stIDSelect As String
Dim stC As String
Dim C As Range
stIDSelect = InputBox("Enter RMA number you want to create a delivery note for")
If stIDSelect = vbNullString Then Exit Sub
Dim idRow As Range
Set idRow = Intersect(Range("A:A"), ActiveSheet.UsedRange).Find(stIDSelect)
If Not idRow Is Nothing Then
Dim i As Integer................
View 9 Replies
View Related
Jul 9, 2013
How to delete another excel private module of the Workbook Object (ThisWorkbook) VBA codes of the workshook event.
This code to run from my PERSONAL.XLSB
Sub Macro1()
Windows("Book1.xls").Activate
ActiveWorkbook.Vbproject
Don't know what other codes I need to put after this
End Sub
This code to be deleted via Macro1 above code
Private Sub Workbook_Open()
Call CreateMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub
View 2 Replies
View Related
