Over 1 million tech questions and answers.

Solved: Macro Error when launching Excel as OLE Automation Object

Q: Solved: Macro Error when launching Excel as OLE Automation Object

Hi Folks

I have an Excel 2007 .XLSM file containing the following macro that performs a Fourier analysis
Code:
Sub Macro1()
Application.Run "ATPVBAEN.XLAM!Fourier", ActiveSheet.Range("$C$1:$C$4"), ActiveSheet.Range("$E$1"), False, False
End Sub

The macro works fine when I launch the XLSM file manually. However, if I launch the file from a VB program (in Microsoft Access 2007), I get the following message:

'Cannot launch the macro ". The macro may not be available in this workbook or macros may be disabled'

The code I use to open the workbook and relevant add-ins:
Code:
Set objXL = CreateObject("Excel.application")
Set objBook = objXL.Workbooks.Open(MyPath & "Template1.xlsm")
objXL.Workbooks.Open (objXL.Application.LibraryPath & "ANALYSISATPVBAEN.XLAM")
objXL.Workbooks.Open (objXL.Application.LibraryPath & "ANALYSISANALYS32.XLL")
objXL.RegisterXLL "Analys32.xll"
I then go to the worksheet and try to manually execute macro1 but I get the above error message. Note that:
"enable all macros" is set in Developer/Macro Security
"trust Access to the VBA etc." is turned on. in in Developer/Macro Security
I tried placing the macro in my PERSONAL.XLSB file to no avail (not sure this was worth it, but I found a suggestion to that effect on another forum)
I tried executing macro1 from the VBA program as follows but got the same error:
Code:
objXL.Run "Macro1"
or even:
Code:
objXL.Run "ATPVBAEN.XLAM!Fourier", objSheet.Range("$C$1:$C$4"), objSheet.Range("$E$1"), False, False
Again, the macro works just fine if I launch the file template1.xlsm manually

Finally, note that the error message does not refer to Macro1 itself, which is found and correctly launched by Excel, but to the Application.run command within the macro. That is the line of code where the debugger stops.

Any enlightened soul out there has any idea what I am doing wrong?

Thanks - Mike

RELEVANCY SCORE 200
Preferred Solution: Solved: Macro Error when launching Excel as OLE Automation Object

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Macro Error when launching Excel as OLE Automation Object

found the answer on:
http://support.microsoft.com/kb/270844

I had omitted the auto open macro. Statement:

objXL.Workbooks("atpvbaen.xlam").RunAutoMacros 1

Read other 1 answers
RELEVANCY SCORE 90.4

I am not an expert but I have built several databases over the years. This time I started with a template from ms access 2010 called projects. I had no problem changing it all up the way I wanted until last Friday and now I've been searching for a fix ever since. One form that I built just like all the others is giving me this error: The object does not contain automation object 'ActiveControl' you tried to run a VB procedure to set a property or a method for an object. However, the component doesn't make the property or method available for Automation operations. Check the components documentation for information on the properties or methods it makes available for automation operations.

Other forms with this same code in it work just fine. This one does not and any others that I try to build now do not.

The code is very simple. It's a combobox asking to search for and find a record. Even using the combobox wizard which auto creates this code will not work: After Update embedded macro where condition: ="[ID] = " & Str(Nz(Screen.ActiveControl,0))

The other forms have much larger code but receive the same error message. Things I've tried - debugged, made the location trusted, created new database and recreated just one form/table to be sure there is nothing wrong with my current database, found a list of other ways to write the same code (sort of)="[ID]=" & Str([ID])
ID=Str(Reports![YourReportName]![ID])
Form![Information]![ID]=Str(Reports![... Read more

Read other answers
RELEVANCY SCORE 89.2

Overview:

I have a form with a subform. When I try to add a record in the subform, I get the error in the title.
Here are the specifics:

The purpose of the main form is to show the financial information related to a specific project, which was chosen on the previous form. The subform on the main form displays each of the payments associated with the project (there can be infinite payments per project, and the relationships have indeed been set in the Relationships window).
The main form data source: qry_all_project_info_by_project_id (pulls data from tblProjects table)

Subform data source: tblAdditionalPayments (this is where I want to add in new payments associated with the project.

Subform master link field: tblProjects.__pkProjectID

Subform child link field: _fkProjectID (from tblAdditionalPayments)
The exact error, which pops up when adding new record in the subform: "The LinkMasterFields property has produced an error. 'The object doesn't contain the automation object 'tblProjects.'"

I've been looking for a solution for hours in over a dozen different forums, but I can't figure out what's wrong, as most people have simply been forgetting to set their master/child field or fixing the problem by re-creating the tables on which the forms are based. Neither approach has worked for me. Any help would be HIGHLY appreciated. Thank you so much in advance.
 

A:Solved: Access 2007- Object doesn't contain the automation object

Like everyone else who's had this problem, instead of actually solving the problem, I managed to find a work around. The actual cause of this problem continues to be completely illusive.

What I ended up doing was completely deleting the content of subform's Master and Child link fields (which let me enter new records, but not associated with any specific project). Instead of relying on this link to make sure I was making new records associated with a specific project, and instead made created a hidden field that had a default value of the correct project ID, then I linked that hidden field to the field in the table that holds the foreign keys.
 

Read other 1 answers
RELEVANCY SCORE 84.8

Hi All,
Hope someone can lend a helping hand. Every month I login into an online portal which provides access to the invoices of our customers (I work for a large organisation). For one of our most important customers I need to enter each of their invoices (on average they have 30 different invoices per month) by clicking on the invoice number hyperlink, copy the data and paste it onto an excel file. As you may imagine, this task is tedious and time consuming, so would really like to automate it. Anyone has got idea as to how to do it using the Excel macro functionality? Thanks a lot in advance! Fabrizio
 

Read other answers
RELEVANCY SCORE 81.2

Hi,
I am submitting one form While clicking save button i am getting "Error:automation server can't create object" could not resolved it. Any one please help me on this.

Regards,
Prashant amaiy

Read other answers
RELEVANCY SCORE 78.8

I have a Lotus Notes application where drawings are embedded into Notes documents. There is an agent to extract the embedded file and save to a specified location but it has now stopped working at the point where it does a saveas

Set rtitem = drawing.GetFirstItem("BodyText")
Set embed = GetEmbeddedObject(rtitem)
Set handle = embed.Activate( False )
Call handle.SaveAs(sFileName)
Call handle.Close()

the embed.Activate loads the AutoCad software, I changed the False to True to show the application, when the code gets to the call handle.SaveAs() line it comes up with an Automation object error. I have tried using handle.Save instead and that comes up with the same error. The autocad software being used (autocad LT) has a SaveAs and a Save function.

Anyone got any ideas?
 

Read other answers
RELEVANCY SCORE 78.8

I have a simple program called "test.js" with two lines code.

The content of test.js is:

var fso
fso = new ActiveXObject("Scripting.FileSystemObject");

The command of running this program on the 64-bit Windows 7 is :

cscript test.js

If I run the program from 32-bit cmd prompt (c:\windows\sysWOW64\cmd.exe), it gave me an error:

Microsoft JScript runtime error: Automation server can't create object

However, if I run the program from 64-bit cmd prompt (c:\windows\system32\cmd.exe), it ran OK.

Does anyone know the solution?

Read other answers
RELEVANCY SCORE 73.6

I am trying to write Excel VBA code to update a table in Microsoft SQL and when I run the code it gives me an Error message "VBAProject --> Object variable or With block variable not set". It then fails to update the table as intended. I am just not able to see what I have done wrong here, so perhaps one of the geniuses on this forum can spot it for me? I would be very grateful for any help.
The problem occurs when it gets to the part I have commented as
'UPDATE Record in the COMPUTERS TABLE for Asset
here is my code:
Sub subInsertData2(intCurrentRow As Integer) 'Update NEW DATABASE Records
On Error GoTo ErrorHandler
Dim rsInsertNew2 As ADODB.Recordset
Dim rsInsertNew3 As ADODB.Recordset
Dim rsUpdateStatus2 As ADODB.Recordset
Dim rsUpdateStatus3 As ADODB.Recordset
Dim strSQL2 As String
Dim strSQL3 As String
Dim strCOMPUTERCI As String
Dim strRECORDEDDATE As String
Dim strUSERNAME As String
Dim strBUILDING As String
Dim strOFFICE As String
Dim strINCWO As String
Dim strFields2 As String
Dim strFields3 As String
Dim strValues2 As String
Dim strValues3 As String
Dim strAREA As String
Dim strSTATUS As String
Dim strUpdateStatus As String
Dim strDATE As Date
strAREA = Range("F4").Value

strCOMPUTERCI = Sheet5.Range("B" & intCurrentRow).Value
strRECORDEDDATE = Sheet5.Range("H4").Value
strDATE = Format(Now(), "yyyy-mm-dd")
strINCWO = "AUDIT"
strSTATUS = "DISPOSED"

'Set The Location Fi... Read more

A:Solved: Issue in Excel VBA - Object variable error

When you run it and it fails, you get the option to debug. What line does it highlight?
 

Read other 3 answers
RELEVANCY SCORE 72

Running W2000 & IE6. When opening any web page, we get a "CS Host message" window which states "name = Error number = -2146827859 description = Automation server can't create object message = Automation server can't create object"

This message appears 3 times in succession when first opening IE and then reappears each time we go to a new page.

Any help will be appreciated.
 

A:IE6 error "automation server can't create object"

Do you have Java enabled? Go to tools, internet options, advanced tab. Scroll down to Java and check your settings.
 

Read other 3 answers
RELEVANCY SCORE 70.4

This post is related to an earlier one, which has now been solved and closed:http://forums.techguy.org/business-applications/955009-excel-2007-lookup-formula.html. The macro outlined in this thread works fine as long as there are 4 values in each row the macro references. If there are less than 4 values the formula errors when trying to return the

Code:
WorksheetFunction.Large
value. Could somebody please edit the macro so that if it causes an error due to too few values, that it will then break out of the code instead of keep running? The ideal situation would be that it outputs any values it finds, and skips past and carries on if the function errors.
 

A:Solved: Excel Macro Error

No problem - all sorted now A bit of out of the box thinking was all that was needed!
 

Read other 2 answers
RELEVANCY SCORE 70.4

Hi there,
I've cobbled below code together (I'm game but probably not even a pedestrian in VBA, maybe more of a snail) to take some information from an Excel sheet and put it into an e-mail. I also want the active workbook attached to the e-mail and if possible another file with the same name but in pdf (haven't looked at that yet).

The ErrorCatch says it's an object or applications driven error.

I'm not sure about this line: .Attachments.Add ActiveWorkbook.FullName but really have no clue where the problem is.

Any help would be greatly appreciated. Thanks.

Christine

Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String

On Error GoTo Errorcatch
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi Leon" & vbNewLine & vbNewLine & _
"" & vbNewLine & _
"Attached invoice for " & Cells(C14) & ".Can you please approve for payment?" & vbNewLine & _
"Supplier: " & Cells(E43) & vbNewLine & _
"Invoice Number:&quo... Read more

A:Solved: Error 400 in Excel Macro

Read other 14 answers
RELEVANCY SCORE 69.6

The following macro works perfectly on the Mac I have built it on. But when I transfer it to my server - or try to run it on a Windows machine - it returns a 400 error.

Does anybody know why?

<code>
Sub NextInvoice()
Range("K9").Value = Range("K9").Value + 1
Range("F34:K40").ClearContents
Range("F7:F11").Value = "Name/Address"
Range("K7").ClearContents
Range("F29:K30").Value = "Personal message..."
Range("G21:K21").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = Range("B3").Value
ActiveWorkbook.SaveAs ActiveWorkbook.Path + "\" + NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
ActiveWorkbook.Close SaveChanges:=True
End Sub
</code>
 

A:Solved: Problem with an excel macro 400 error

Read other 16 answers
RELEVANCY SCORE 69.6

Hi,

Every macro enabled file that I try to use on my computer gives me the same error: Runtime error 9 subscript out of range.

I get this error on files that work on other computers, including files I wrote on my computer, they don't work anymore and the all give me the same error.

Even writing a very simple macro I get this error, for example:

Sub MyInfo()
Workbooks("CropTool").Worksheets("RawData").Range("A1").Cells(2,4) = "hi"
End Sub

I have checked about 10 times to make sure that the workbook is indeed called CropTool, and the worksheet is called RawData.

This error has nothing to do with what I write in the module....

Please help.
 

A:Solved: excel 2007 macro error

Read other 7 answers
RELEVANCY SCORE 69.6

When I try to compile the Excel Macro, I get the following error message:

"Compile error: Procedure too large"
 

A:Solved: Excel Macro - Error Message

Read other 15 answers
RELEVANCY SCORE 69.2

Hi,
Below message appears when the customs.ru site is accessed and also error code Error 0x80090008 



I tried the below troublehooting steps and it did not help


1. Added the customs.ru  to Compatibility Settings in UE
2. "Initialize and script active x controls is not marked safe for scripting" is enabled in IE under Security Custome Level
3. Added the site in Trusted Sites 

https://answers.microsoft.com/en-us/ie/forum/ie7_6-windows_xp/automation-server-cant-create-object-prompt/4d744c7f-0ed1-4ac1-939d-5ba19b1aeb27



Regards, Boopathi

Read other answers
RELEVANCY SCORE 69.2

All the sudden after years of working Revo Uninstaller now won't start, it gives me the following error after double clicking the icon:

I have tried running Revo Uninstaller as Adminstrator, guest, and my local admin account but I always get the same error. The funny thing is a Google search surprisingly doesn't turn up many results so I am kinda stumped.

Anyone know what this means or better yet how to fix it?

Thank you,

J

A:Automation object not found - Windows 10 Pro 64

Appears to be related to Visual Basic.
Have you tried running
SFC /Scannow
from an elevated command prompt?
(Windows key + X, click Command Prompt (aAmin) and enter the above command.

Read other 0 answers
RELEVANCY SCORE 69.2

i have winxp pro i got it from a computer show so i cant get on the automatic updates it fails the product id anyway i need to know if there is anyway to fix this problem myself kinda new to computers but learned alot learn more just reading some of this site thanks, the problem is a couple of them first the welcome screen does not come up, it use to but i changed how i connect to the internet ,i think did that ,and i cant restore it to when it did i was trying to make room on the disk and i erased the backup on the cleandisk wish i didnt do that now and when ever i go to start -control panel- users account, i get Autonation server cant creat object, if i click home on it, i get 'g_Navigator' is null or not an object , it been that way since i had the computer i have to go to start- administrative tools- computer- management-local users and groups i have created a account there but still dont know what im doing i did that when the welcome window was comming up i created a guess account but know i cant get in it and the box i do logon to says window security login information myscreen name is logged on as my screenname\screenname logon date i also have to many accounts open or not open i dont know but i would like to get rid of all but 1 administror 1power user 1 guest i hope you can figure out what the problem is thanks first time on site ill be back you have alot to learn here oh the only two accounts i can get into administator and a i think power user... Read more

A:Automation Server Can't Creat Object

If your computer came with Windows XP cd, then I would consider reinstalling the Windows Operating System. i cant get on the automatic updates it fails the product idMany times computers purchased in 'flea market' environments do not come with cds, nor do they even come with legitimate versions of XP. Yours might in this category as Microsoft will not allow you to update an illegitimate version on XP. Just a guess.

Read other 1 answers
RELEVANCY SCORE 68.8

I have attached a spreadsheet. The first tab (Setup) has buttons 1,2,3,4,5. The purpose of these buttons is to have different years investments. I have a tab (Sheet1) that is hidden in the spreadhseet. Each macro unhides, then changes about 3 formulas on the Investment Analysis Tool tab, simply changing the cell reference. Only it does't ' change. It errors out with the above error. I've never see anything like this happen! Thanks in advance for the help.
 

A:Solved: Excel: Run-time Error 1004 During Macro

Monty75, I am sorry but the spreadsheet that you posted does not have any buttons visible.
The Code is there and I have looked at and run it and the problem is that the Code is not on Sheets("Investment Analysis Tool").Select
This is a quirk of Excel, You need to put activesheet. in from of your "Range selection" like so
ActiveCell.FormulaR1C1 = "=Sheet1!R[31]C[-2]"
 

Read other 3 answers
RELEVANCY SCORE 68.8

Hi All,

I included the macro that was developed last week for this "AIR Log." However, the creator is on leave and I only know how to read and interpret...thus giving me an error that I cannot fix:
I would also like to do the following...

In Column O have a formula... =CONCATENATE(Lxx,Mxx,Nxx)
Hide columns L-N
Remove Table Style
Set font to Arial; size to 9; Make sure all text is Middle & Center Aligned.
Row 1 has a fill color: Blue, Accent 1, Darker 25%
Option Explicit

Sub oooFormatAIRLog()

ActiveWindow.ActivateNext
Sheets(1).Name = "AIRLog"
Columns("P:Q").Delete Shift:=xlToLeft
Range("Table_AIRLog").Select
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Clear
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Risk/Issue/Action Item?]") _
, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Issue,Risk,Action Item", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Criticality]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, CustomOrder:="High,Medium,Low", _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("AIRLog").ListObjects("Table_AIRLog"). _
Sort.SortFields.Add Key:=Range("Table_AIRLog[Due Date]&quo... Read more

A:Solved: Excel Macro: Run-time error '1004'

With the sheet and the charts it's a needle in a haystack.
The error messag is that you're adressing a range or sheet which is either misspelt or hidden or non existant.
You will have to go through the macro step by step.
Open the VBA editor, loop for the macro select it and press F8 so the code will be executed line by line and it will stop when the error occurs.
Check that line and see if what it addresses exists.
Trouble shooting takes time an patience.

Good luck
 

Read other 2 answers
RELEVANCY SCORE 68.8

I have a macro I wrote in excel 2003 (by recording small steps as a chain of macros), I just upgraded to a new machine and Office 2010.

When I try and run the macro it comes back with error message "Run time error 438, Object doesn't support this property or method."

When I run debug the code line that is flagged is:

Selection.End(X1 to Left).Select

Are there any commands or options which have changed between 2003 and 2010 and is there a conversion list anywhere?

George intermediate user running Windows 7 64bit, Office 2010
 

A:Solved: Excel macro 2003 to 2010 error 438

Read other 16 answers
RELEVANCY SCORE 67.2

I am working in Excel 2003, within XP and have the following situation.

For approximately 18 months, I have been running the macro listed below without issue. Now, for no apparent reason, the macro fails to execute in its entirety. When executing the macro from within Excel, the execution starts and proceeds a few lines and then just stops. No error, no warning, nothing, the macro just stops.

If I open up the Visual Basic editor screen from within Excel and attempt to execute the macro with F5 or step through line by line with F8, execution will proceed to the end of the Selection.TextToColumns command line, and the insertion point goes back to the start of this same command once executed. If F8 is pressed a second time, the insertion point goes all the way back to the Sub line at line 1 of the macro and tries to run the macro again. The behaviour is consistent, and no error code or indication occurs. The code has not changed.

If I break up the macro into a series of smaller macros at each error point, and run them sequentially, I get the desired outcome.

I have tried renaming, copying and pasting the text into different macro files, exporting the macro and re-importing into different worksheets (this one is currently sitting in personal.xls which is still sitting in my /xlstart subfolder.

I have 8 similar macros that now all do the same thing - very puzzling.

Would welcome any suggestions you may have. Thanks all.

ps. for all you real VBA folks out there, no this isn&... Read more

A:Solved: Excel 2003 macro halts during execution - no error message

Read other 7 answers
RELEVANCY SCORE 66

Is there any known reason a user of workbook would get the error code 91, but someone else does not?
I cannot really debug the code since I am not getting the error and it's not practical for me to be at the users' machine, nor for me tell them which step to take to help me debug it.
 

A:Solved: Excel VBA error code 91: Object variable or with block variable not set

Read other 9 answers
RELEVANCY SCORE 64.4

After I opened a spreadsheet in MS Excel, I received this error message:

A Runtime Error has occurred. Do you wish to Debug?

 

Read other answers
RELEVANCY SCORE 64.4

Hi Everyone,

I'm new, so please don't flame me if I'm not posting this correctly

My fiance recently started a new job in a secretarial position and after her first day, I've decided her office REALLY needs to upgrade the way they do things.

First off, it's a personnel company. So they will advertise job positions and then receive applications for those jobs.

Here is the current workflow:
- email applications come in
- sec. (secretary) then opens cover letter (which is usually a .doc or .pdf)
- copies in the email body text (which is actually an auto generated one created by seek.com etc) into the new doc + page break
- opens the persons CV and copies that into the cover letter doc.
-- So basically you end up with a document that has the original email on the first page, then the applicants cover letter, followed by their CV.
- this doc is then printed
- sec. writes the date on the front page, the consultant who is looking after those applications & also L1 or L2 (see below).

THEN

Depending on if the application is a reply to an advertised job, then
Letter1 option:
- sec. replies to the email (to the applicant),
- opens the L1 template email they have saved,
- copies this template into the reply email
- edits the applicants name, the job description and the consultants name
- sends email.

OR if the application is just a general application asking for whatever work is going:
Letter2 option:
- sec. replies to the email (to the applicant),... Read more

A:Macro / Automation possible across multiple applications?

If they have PDF software installed for Printing to PDF ( there are free versions like CutePDF)

Not sure if all email programs will do it, but Outlook...when you open an email and go File >Print...select PDF printer ( works for normal paper print also)...click options tab and check off linked documents...and OK...it will print the email first and then the attachments to a single PDF file...problem might be the order in which they are attached?
you could also look a using Adobe PDf maker software where you might be able drag files from the email

http://www.adobepress.com/articles/article.asp?p=698126&seqNum=3

http://www.a-pdf.com/merger/

just giving you idea's, this doesn't sound like a perfect solution to what you need, but it may spark another method to streamline the process.
 

Read other 2 answers
RELEVANCY SCORE 64

In Excel 2002.
A user received the message "Cannot shift objects off sheet".
This is a spreadsheet with macros that's been used for months without this problem.
The macro calls for a column insert.
She can't manually insert the column either. The 'insert' selection is greyed out.
There is no protection on the sheet, although there is protection on the workbook. (Old password that she doesn't remember doing).
Any suggestions???
 

A:Excel error-cannot shift object off sheet

You have data entered in the last column of the speadsheet.

Column IV

When you try to insert a new column excel is trying to push that data over to the right, only there is no more room to the right for the data to be pushed, therefore the "can not move data off sheet error".

Domer
 

Read other 2 answers
RELEVANCY SCORE 62.8

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

A:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

Read other 1 answers
RELEVANCY SCORE 62.8

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

A:Solved: Excel macro to extract Word form data into Excel

Read other 7 answers
RELEVANCY SCORE 62.8

I’m using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

A:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

Read other 7 answers
RELEVANCY SCORE 62

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

A:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

Read other 1 answers
RELEVANCY SCORE 61.6

Hi Guys:

I have created a macro( 2 line macro actually) that goes through my workbook and it supposed to clear the value from cells b1:b68 on main page. When I try to run the macro with the sheet protected I get a pop up that just says "400" in it with a red circle that has a white "X" through it, whit the sheet unprotected works perfectly !. Because it is a survey I need to protect teh content, to be manipulated by many. Can somebody give a clue? Here is the code:

Public Sub Clear()
Sheet1.Cells.Range("B17:B68").ClearContents
End Sub
Windows 7
MExcel 2010

Many thanks

Rod
 

A:Solved: error "400" running an Excel macro

Read other 8 answers
RELEVANCY SCORE 61.6

I have created a macro that goes through my workbook and is supposed to clear the interior color from cells A1:I900 on each page. When I try to run the macro I get a pop up that just says "400" in it with a red circle that has a white "X" through it. Here is the code:

Sub Clearcolors()
Dim ws As Worksheet
Dim RngH As Range
Dim RngHD As Range
For Each ws In ThisWorkbook.Worksheets
ws.Select
Set RngH = ws.Range("A1:I" & Range("I900").End(xlUp).Row)
For Each RngHD In RngH
RngHD.Interior.ColorIndex = xlNone
Next RngHD
Next ws
End Sub

What can I do to fix this problem? Any suggestions?
 

A:Solved: VBA error "400" running an Excel macro.

Read other 15 answers
RELEVANCY SCORE 60.4

Hello,
I have a problem after compiling a program and making an install disk. I am using Windows 7 32 bit professional and had given this program to a user that has XP on his computer. After installing and keying the .EXE, he got this runtime error.
After some thought, am I correct in thinking that he got this error because the program was compiled in Windows 7 and not XP??
If not, please explain then how or why he keeps getting this runtime error.

Thanks,

Doug

 

A:Solved: Runtime Error: 430 Does not support automation

Read other 8 answers
RELEVANCY SCORE 60.4

Hi.
I created a macro for one of my co-wokers that:

Refreshes data using MS Query to pull data from an Access database.
It then formats fonts and headers and stuff correctly on that worksheet.
It then creates a pivot table on a new sheet (using the wizard) based on this refreshed info (it's actually based on a cell range 8 columns wide and 10,000 rows long excluding the header, just in case we get a huge number of claims on one ship).
It then formats the pivot table correctly and renames the worksheet.
Finally, it moves the worksheet to where it should be (still in same workbook).

This worked the first time, and still works now, except for the last part, where it moves the sheet. I now receive an error:

Run-time error '1004':
Application-defined or object-defined error.

I hit Debug to see where the problem was (I know very little VB) and this is what was there. The Bold part is what was highlighted in yellow in the VB window:

Sub Refresh_Pivot()
'
' Refresh_Pivot Macro
' Macro recorded 04/09/01 by Liz Clark
'
Range("A10").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A10:H10").Select
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 11
With Selection.Font
.Name = "Times New Roman"
.Size... Read more

A:Excel 97 macro error

Read other 6 answers
RELEVANCY SCORE 59.6

I can run this macro on my own machine but when I transfer it to another computer, I get the following error: Run Time error 1004 Cannot open PivotTable source file 'headcount by area' - any ideas what's wrong??!
 

A:Error running a macro in Excel

Read other 6 answers
RELEVANCY SCORE 59.6

=IF(ISERROR(100),"NA",100) appearing in Excel 2007 shortcut key macro?

First, my disclaimer is that I am not familiar with Excel or VB!​

That being said, one of my users is getting an error when running a macro in Excel 2007 from his personal.xlsm.​
The macro is a simple color change which we have re-recorded several times but when he attempts to run the macro with a shortcut key the field populates with =IF(ISERROR(100),"NA",100) where 100 was the test value of the original cell. If you run the macro from the menu it works correctly. ​
Macro is:​
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
End Sub​
Any suggestions are greatly appreciated. ​
Thanks!​

 

Read other answers
RELEVANCY SCORE 59.6

Hi there,

I'm trying to do a simple macro that opens an Excel template. Copies fields from a row in an open Excel sheet to the newly opened template. Prints the template. Puts a date into that row. Stop.

I got an error at r = ActiveCell.Row until I deleted Option Explicit. Also got an error at the copies code that I turned into text. Now I get a Compile Error: Named argument not found at IgnorePrintAreas:=False

I tried to delete that line but then I get an error for the entire print code.

Does anybody know where the error sits? I'm trying to print to the default printer.

Thanks, Christine

Code:
Sub ProcessServiceInvoice()
Dim BkSrc As Worksheet, BkDest As Worksheet
Dim FilePath As String, FileName As String
Dim blnOpened As Boolean
Dim DestBook As Workbook
'Set Source Workbook
Set BkSrc = Workbooks("D:\Documents and Settings\305015724\My Documents\admin\Accounts\2010\_Service_Invoices_2010.xls")

r = ActiveCell.Row

'Open Template
Workbooks.Open FileName:="D:\Documents and Settings\305015724\My Documents\Templates\AP coding template_General_NZL.xlt"
Set BkDest = ActiveWorkbook
'ActiveWorkbook.SaveAs FileName:=BkSrc.Cells(r, 3).Value
'Copied from Zack Baresse, I think I will need this
'Call ToggleEvents(False)
'Set ws = ThisWorkbook.Sheets("CodingTemplate")
'If WbOpen(BkDest) = True Then
'Set wkb = Workbooks(FileName)
'blnOpened = False
'Else
'If Right(FilePath, 1) <> App... Read more

A:Compile Error in Excel VBA macro

I have worked a bit more on this and above problems seem to be resolved. But, big but, I now get a run-time error '9'. Subscript out of range.

I looked it up and it says it means one of the workbooks isn't open but I'm working with 2 books, one which I have open to start with and the other one I do open.

Any ideas? Thanks, Christine

Sub ToggleEvents(blnState As Boolean)
'Originally written by firefytr
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState Then .CutCopyMode = False
If blnState Then .StatusBar = False
End With
End Sub

Function WbOpen(wbName As String) As Boolean
'Originally found written by Jake Marx
On Error Resume Next
WbOpen = Len(Workbooks(wbName).Name)
End Function
Sub ProcessServiceInvoice()
Dim BkSrc As Worksheet, BkDest As Worksheet
Dim FilePath As String, FileName As String
Dim blnOpened As Boolean
Dim DestBook As Workbook
'Set Source Workbook
Set BkSrc = Workbooks("D:\Documents and Settings\305015724\My Documents\admin\Accounts\2010\_Service_Invoices_2010.xls")

r = ActiveCell.Row

'Open Template
Workbooks.Open FileName:="D:\Documents and Settings\305015724\My Documents\Templates\AP coding template_General_NZL.xlt"
Set BkDest = ActiveWorkbook
'Copied from Zack Baresse, I think I will need this
'http://www.mrexcel.com/forum/showthread.php?t=292030
Call ToggleEvents(False)
Set ws = ThisWorkbook.Sheets("CodingTemplate")
... Read more

Read other 1 answers
RELEVANCY SCORE 59.6

I have a spreadsheet with macros that I use for work which works flawlessly on Win XP, but when you try to run the macros on a Vista/7 machine it gives this error:

"Compile error: Can't find project or library"

It seems to be hanging up at "Trim" on this line:

NewSheet = Trim(InputBox(Mesg, Title))

Our tech support seems lost, any help?

Thanks!

Read other answers
RELEVANCY SCORE 59.2

Hi folks.

I'm looking for help here on automating a daily task on Excel. I deal with 'financial reconciliations' for a large company and I receive a spreadsheet each day with a large amount of data on it. From this data I pull the info I need and then allocate it out to my staff via another spreadsheet. What I would like to do is automate this. So what I'm aiming to do is this:

Have a button on the work allocation spreadsheet that does the following:

Takes todays data and compares with yesterdays data
Picks out the new data and automatically inputs this onto the work allocation spreadsheet
The work allocation spreadsheet then compares the 'new data' against the data already held within itself and removes duplicates

I do this manually each day but it would be great to automate it.

Can anybody help?

Thanks,

Peaker
 

A:Excel Automation Help

Posting sample workbooks with sensitive data removed would be helpful so we can see exactly what needs to be done.
 

Read other 1 answers
RELEVANCY SCORE 59.2

I have a new Dell laptop with Windows ME & Office XP. When I go to record a new macro, I can enter the description and other info, but when I click 'OK' to start recording the Macro, I get an "Unable to record" error message. The error message has no help button or details button, just an 'OK' button. I have everything for Office installed.....Please help, thanks
 

A:Unable to record macro error in EXCEL >:-(

Read other 7 answers
RELEVANCY SCORE 59.2

Hi,

I have a bix excel file with lots of macros written in it. While running a particular macro, I get "Subscript Out of Range" error in some specific rows.

The same workbook when used by others work well.

Can anyone help?
 

Read other answers
RELEVANCY SCORE 58.8

First of all, thank you all for having me here.

I'm trying to make my own POS system using Excel and got to activate the userform using a command button but i would like to copy the text input by the user in a particular text box on the user form, call the textbox with a tag and then copy its contents into a list on a hidden worksheet then delete the content of the text box to make room for new input. how can i do that? im just a begginer tho and would really appreciate an example on how to write the script. Thank you & Peace to all!!!
 

A:Automation for Micosoft Excel

This - and the link there - might help you get started
http://www.mrexcel.com/forum/showthread.php?t=339095
 

Read other 3 answers
RELEVANCY SCORE 58.8

Dear Gurus
First thanks in advance for your help.
I have worked on a c++ project which has an automation connection with the excel app( create an excel app, then open a workbook, keep sending data to a range of this book every second). Some part of the code is as follows:

/********************************************/
if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Couldn't start Excel and get an application 0bject");
return;
}
app.SetVisible(TRUE);

lpDisp = app.GetWorkbooks(); // Get an IDispatch pointer.
ASSERT(lpDisp);
books.AttachDispatch( lpDisp ); // Attach the IDispatch pointer

// Open a workbook.
lpDisp = books.Open("c:\\Data\\test.xls",
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional);
ASSERT(lpDisp);

// Attach to a Workbook object.
book.AttachDispatch( lpDisp ); // Attach the IDispatch pointer

// Get sheets.
lpDisp = book.GetSheets();
ASSERT(lpDisp);
sheets.AttachDispatch(lpDisp);

lpDisp = sheets.GetItem( COleVariant((short)(1)) );
ASSERT(lpDisp);

// Attach the lpDisp pointer to a Worksheet object.
sheet.AttachDispatch(lpDisp);

lpDisp = sheet.GetRange(COleVariant("A2"), COleVariant("A2"));
ASSERT(lpDisp);
range1.AttachDispatch(lpDisp); // Attach the IDispatch pointer
// to the range object.

range1.Clear();
range1.SetValue (COleVariant("test"));

/**********************... Read more

Read other answers
RELEVANCY SCORE 58.8

Hi all, very niche question here ...

I have a bunch of excel spreadsheets, with a bunch of worksheets within each one. Basically, April 2013.xls, May 2013.xls - with every day of the month inside.

I need to somehow 'export' the VIEW of the worksheets, to another format like PDF, for the sole purpose of presenting it as a digital signage solution. Exporting the data from spreadsheet is not feasible, as the "calendar view" is lost.

Alternatively, anyone know a room booking solution that's cheap/free for corporate use?
 

A:Excel file automation

St1ckM4n said:





room booking solutionClick to expand...

A possible alternative might be an Appointment System.
 

Read other 1 answers
RELEVANCY SCORE 58.8

i need to set up a link between access and excel.

i need make a macro to export certain values from my access database into an existing spreadsheet, prompt a macro i have made to save the spreadsheet.

i then need to make another macro to transfer the data back, activate a second macro to reset my spreadsheet and then close the spreadsheet.

i am really struggling so if anyone could help it would be much appreciated.

thanks
 

A:automation from access to excel

Hello, can you tell me why it is necessary to do this Transfer from Access to Excel?
I can probably create the VBA code to do this but I would need a lot more information and copies of the database and Excel workbook.
 

Read other 2 answers
RELEVANCY SCORE 58.4

Hi,
I have a monthly report I run which outputs single sheet "Invoices" from my workbook that is populated by a months worth of sales. The macro goes through a list of names, pulls the details for that person, populates the pivot table on the invoice,
then saves as both a single excel sheet and a pdf. up until today it has worked fine. the code is, and the error occurs when it reaches the date line. It states "Compile error, can't find project or library":
Sub Save_Sheets_To_New_Books() 'INVOICES
Const strWbPath As String = "D:\Accounts\EA Letters\2020 09 September\"
Dim strDate As String 'todays date
'save the sheets to new books within the active folder and print them
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
strDate = Format(Date, "yyyy.mm.dd")     '(Date, "yyyy.mm.dd")
THIS IS WHERE THE ERROR OCCURS the word DATE is highlighted
ActiveSheet.Copy
Call DeleteNamedRanges
Call PrintAreaAndPasteSpecial
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strWbPath & Name_of_Artist & "_" & strDate & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False         
ActiveWorkbook.SaveAs FileName:=strWbPath & Name_of_Artist & "_&quo... Read more

Read other answers
RELEVANCY SCORE 58.4

Hey everyone,
So I used this code to copy a range of cells from multiple workbooks and paste it into one workbook. It used to work fine but now it gives me the 400 error. I've been adding files to the directory that it pull the files from and it was working fine, but all of a sudden it's causing this error. Can someone please help me out?

Dim xcell As Range
Dim ycell As Range
Dim sheetname As String
Dim wblist() As String
Dim i As Integer
Dim wbname As String
Dim j As Integer

i = 0
j = 0
FolderName = "C:\Documents and Settings\s.k\Desktop\CRs\LOG"
wbname = Dir(FolderName & "\" & "*.xls")

Application.ScreenUpdating = False

Do While wbname <> ""

i = i + 1
ReDim Preserve wblist(1 To i)
wblist(i) = wbname
wbname = Dir
Set ycell = Range(Cells(i + 3, 2), Cells(i + 2, 28))
Set xcell = Range(Cells(2, 3), Cells(2, 28))
sheetname = "loging form"

ycell.Formula = "=" & "'" & FolderName & "\[" & wblist(i) & "]" _
& sheetname & "'!" & xcell.Address
Loop

Do While j < 100
Cells(j + 3, 1).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[6],4)"
'ActiveCell.FormulaR1C1 = "=LEFT[RC[6],4]"

Cells(3 + j, 1) = Val(Cells(3 + j, 1))
Cells(3 + j, 2).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[CR Status.xlsx]Sheet1'!R3C1:R189C3,3,FALSE)"

If Cells(3 + j, 1).Value = 0 Then
Cel... Read more

A:Excel VBA Macro Problem - Code used to work, now 400 error

Read other 7 answers