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.
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
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.
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
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) = ""
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
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.
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.
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
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
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
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
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
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.
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:
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)
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.
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..............
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.
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................
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
