Runtime Error 1004 When Opening Excel From Access Upon Second Execution

Jul 16, 2014

The following script runs fine the first time I click the command button on an Access form but after I close the Excel workbook and click the command button again to edit a new spreadsheet, I get the follow error message:

Run-time error '1004': Method "Columns' of object'_Global failed.

It fails here:

Code:
Columns("H:H").Select

The script is being run from Access 2007 and is opening a spreadsheet in Excel 2007 (but using xls extension)

Code:
Private Sub IdahotoExcel_Click()
Dim dlg As FileDialog
Dim idahofile As String

[Code].....

View 5 Replies


ADVERTISEMENT

Excel 2007 :: Runtime Error 1004 Incomplete Datasource

Mar 5, 2012

I am running excel 2007 thin client. in attempting to refresh a query, I have encountered this error message: "run time error 1004 Incomplete datasource". The four line macro is as follows:

Code:
Sub RefreshFamily()
Sheets("Family").QueryTables("Family_refresh").EnableRefresh = True
Sheets("Family").QueryTables("Family_refresh").Refresh BackgroundQuery:=True
Sheets("Family").QueryTables("Family_refresh").EnableRefresh = False
End Sub

Where the named range "Family refresh" is defined as :

Code:

=OFFSET(Family!$A$1,0,0,COUNTA(Family!$A:$A),COUNTA(Family!$1:$1)-2)

Why the macro thinks that my named range does not exist?

View 1 Replies View Related

Excel 2010 :: Setting Range - Runtime Error 1004

May 1, 2012

I am using Excel 2010. Why I do struggle with setting the range below

Code:
Set rng = Sheets("Data").Range(Cells(4, firstcol), Cells(lastrow, lastcol))

I get run time error 1004

Tried simple code from msdn and it return same error

Code:
Range(Cells(2, 3), Cells(10, 4)).Select

View 2 Replies View Related

Excel 2011 :: Mac Recorded Macro Does Not Work With Windows - Runtime Error 1004

Nov 20, 2013

I have been given a macro recorded on a mac and it works fine with Mac Excel 2011. Unfortunately, I need to make it to work on a windows machine, but it comes up with an error message "macro unable to set width property of the window class. Run-time error 1004". When I press debug it takes me to the line .Width = 1456.

Below is the chunk of the code that becomes highlighted when pressing debug.

With ActiveWindow
.Width = 1456
.Height = 795
End With

View 3 Replies View Related

Excel 2007 :: Runtime Error 1004 / PasteSpecial Method Of Range Class Failed

Jun 20, 2013

1.) I have excel 2007 and when I recorded the macro yesterday it worked just fine, but today it's coming up with the box to update values. The macro is set to open up the vendor assignment sheet and do a vlookup against the clerk and then return the information to the original sheet and then copy paste special values. 2.) Today it's also doing the calculating thing in the corner using 2 processors which it has not done before. 3.) Run-time error '1004': PasteSpecial method of Range class failed.

[code]' Keyboard Shortcut: Ctrl+r
'
Dim OriginalSheet As Workbook
Set OriginalSheet = ActiveWorkbook
Columns("B:B").Cut
With Columns("A:A")
.Insert Shift:=xlToRight
End With

[code]....

View 2 Replies View Related

Excel 2010 :: Runtime Error 1004 / Unable To Set Hidden Property Of Range Class

Jun 25, 2013

I am using this code (below) to hide certain rows or columns depending on what number is entered. Everything is working fine and I am at the point where I would like to share this excel sheet with others but I would like to lock certain cells so that others cannot alter the formulas. As soon as I lock the cells and then enter values into the unlocked cells, I get the "run-time error '1004': Unable to set the Hidden property of the Range class".

I am using excel 2010 x64.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("B3")) Is Nothing Then
If Range("B3").Value = 0 Then
Columns("G:P").EntireColumn.Hidden = True
Else
If Range("B3").Value = 1 Then
Columns("H:P").EntireColumn.Hidden = True

[code]....

View 3 Replies View Related

Excel 2007 :: Runtime Error 1004 - Select Method Of Button Class Failed

Sep 26, 2013

My excel version is 2007 and i am on WIN7 64bit

I have a workbook with VBA which was working fine earlier. Lately i have been getting

"Runtime error 1004" Select method of button class failed.

I am adding buttons dynamically in a loop in a sheet and what surprise me is , i go to debug mode when error pops up and it points to below line

MySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select

but my code is running in a loop and i can see that atleast 20 buttons were added and it fails to select when it try to add this iteration. It is completely confusing me.

My entire below code is in a loop

Code:
maWrk.Cells(xIt1, yIt2).Select
mySheet.Buttons.Add(ActiveCell.Left - 5, 25, 20).Select
Selection.OnAction = "callMe"

[Code]....

I also notice that when it fails, on select method .. the button was actually added to the sheet with caption/name as "Button 65536" but then fails to select it. What makes the select to fail after adding the button? Could there be anything special with Button 65536?

Is there any better way to code the adding button and setting action and name for excel 2007?

note before entering the loop i am deleting all the shapes with myShape.Delete which name matches "btRun"

I also want to highlight that i don't have 65536 rows in my sheet. I just have 200 rows. and when this error happens it is on row 150 so firstly i wonder why it is naming the button as Button 65536. I would assume it will start with 1 and go on increments.

What makes excel to fail to select an added button in VBA?

View 6 Replies View Related

Excel 2013 :: Copy Method Of Worksheet Class Failed (Runtime Error 1004)

May 17, 2014

I'm using Excel 2013 and I'm getting an issue in vba I can't figure out. (This is something I've done several dozen time before) But everytime I try to copy a sheet in a workbook,

Sheets("Sheet1").Copy After:=WB.Sheets(WB.Sheets.Count)

I recently copied in this sheet from another workbook, and deleted all of it's formula names, but I can't copy any other sheets now either.

The Run time Error 1004: Copy method of Worksheet Class failed pops up. What is weird is that I put in a msgbox and

MsgBox (WB.Sheets.Count)

returns a "1" though the sheet has about a dozen sheets within it. I've saved the workbook and even saved it as another name.

The sheet I imported has a sheet number of 77 while the previous last sheet was 23, could this be a cause?

View 2 Replies View Related

Excel 2010 :: Runtime Error 1004 - Autofill Method Of Range Class Failed

Jul 31, 2014

The macro I'm writing (Excel 2010) is a loop that inserts a column based on a variable location, enters in a formula in the first cell, enters another formula in cell 2, then should fill that second formula to the last row, then keep going until the loop ends. There is always a chance that there will be blank cells to the left and right so I didn't use xldown. I've tried writing in multiple ways, but I keep getting the error.

Code:
Sub colfrmadd()
Range("A1").CurrentRegion.Select
colcnt2 = Selection.Columns.Count

[Code]....

View 1 Replies View Related

Excel 2010 :: Macro To Send Email Now Fails With Runtime Error 1004 - Mail System Failure

Aug 23, 2012

I've been using a macro successfully for the last couple of years, but this morning when I went to use it, it decided to fail. I have a workbook which contains various spreadsheets. The macro that has failed performs the following tasks:

1. It copies a list of email addresss from an external workbook to a sheet in the current workbook (still works)
2. It copies the referral sheet I want to send to a new file, and saves it with an appropriate filename to an appropriate folder (still works)
3. The macro then creates an email with a standard subject line, attaches the new worksheet and emails it to each of the addresses as above (broken)

The error message is from Microsoft Visual Basic. "Run-time error '1004': Mail system failure. Check your mail installation."

I'm guessing there is a setting somewhere in Excel that has changed as part of an update.

I've been through a number of the options in the developer menu to remove any obvious restrictions (& reopened excel afterwards), but so far it hasn't resolved the problem.

For what it's worth, here is the macro code.

With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Run "PullInSheet1"
Dim oldbook As String

[Code] .......

In case you're wondering about the pullinsheet code, I'll add it below - but I probably grabbed it from this forum a couple of years ago (like some of the above) & just made some changes.

Code:
Sub PullInSheet1()
Dim AreaAddress As String
'''''' Sheet11.UsedRange.Clear
Dim ClRange As String
ClRange = "= 'L:ADMINEMPLOY SERVICES" _

[Code] ........

There are a couple of things I've wanted to do to improve the macro, but I couldn't justify the need to spend time working it out (since writing spreadsheets isn't really my job). Since it's broken at the moment, I can...

1. I'd like to create a subject line that reflects the name of the person being referred. For some reason though, anything other than text in the cell reference caused an error for me. eg, I tried using concatenate to create my subject line, but it didn't work.

2. I'd like the copied sheet to contain all of the formatting of the original sheet. Presumably there is a paste option that will do this and I just picked the wrong one.

The mailsystem we use is Groupwise 8. I couldn't find any settings in that program that have been changed, or that I could change.

I should change the extension from xls to xlsx in the code since I'm using Excel 2010 (but changing it doesn't fix the problem).

View 4 Replies View Related

Runtime Error '1004'

Jan 12, 2007

Is there a limit on the amout of pictures you can use with the statement me.pictures.visible? Reason being, I am trying to import 119 pictures into a spreadsheet. When the information is selected form a drop down list, it pulls up the 1 of the 119 pictures. I was able to get 54 pictures input. Everything was going great until the 55th. When it stared giving me an error...
______________________________________________
Runtime Error '1004':

Unable to set the Visible property of the Pictures class
______________________________________________

When I select Debug, it takes me to the line with Me.Pictures.Visible = False

Everything seems to work fine until I enter the 55th picture. If it is that, is there a viable work around?

View 9 Replies View Related

Runtime Error 1004,

Feb 22, 2007

Getting a error message when opening a workbook "Runtime error 1004" I dont know much about excel and the user explained it to me as best she could . Here is where the script bugs:

Sub auto_open()
fdist = ActiveWorkbook.Name
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Worksheets("f_cumul").Cells.ClearContents
Worksheets("f_cumul").Cells.ClearFormats
Worksheets("vtes_dj").Select
Range("a1").Select
Selection.End(xlDown).Select..............

View 9 Replies View Related

Keep Getting Runtime Error 1004

Apr 21, 2014

When I run the following code it keeps giving me a runtime error 1004.

Code:
Sub ImportData()
Dim fNameAndPath As Variant
Dim wb As Workbook
Dim cNextRow As Long
Dim Ans As String
NextRow = ThisWorkbook.Worksheets("Errors").Range("B" & Rows.Count).End(xlUp).Row + 1
cNextRow = ThisWorkbook.Worksheets("Compare").Range("A" & Rows.Count).End(xlUp).Row + 1

[code]...

View 3 Replies View Related

Runtime 1004 Error

Feb 15, 2008

I have this macro and I keep getting a 1004 runtime error 'PasteSpecial method of range class failed' on the highlighted line,

Sub Print_()
'
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Sheets("Print").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = xlAutomatic
End With

View 9 Replies View Related

Getting Runtime Error: Syntax Error Or Access Violation

May 3, 2006

I'm getting the error for the following piece of code.

Sub itconfandscratch()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Server_Name = "sturecord"
Database_Name = "Scratch" ' Enter your database name here
SQLStr = "SELECT stuname FROM dbo.sturec" ' Enter your SQL here
Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ""..............

View 2 Replies View Related

Runtime Error Code 1004

Jan 16, 2007

I am converting Excel Templates which were developed in office 97 to Excel 2003.The templates are running fine in office 97 ,but when I run the same template in off 2003 I get the error "Run time Error Code 1004 : Application or Object Defined " and when I tried to debug the cursor is pointing to a cell which contains a formula which add multiple cell values.Can any one throw some light on this why the template which works fine on office 97 is not working in office 2003?

View 11 Replies View Related

Runtime Error 1004 While Loading Add-In

Jul 6, 2009

Runtime error 1004 while loading Add-In. I have a workopen event as follows:

View 4 Replies View Related

Runtime Error 1004 With Use Of WorksheetFunction

Dec 14, 2009

Few hours ago, I wrote a simple procedure in order to get an average of a range of 10 cells (A1:A10). Within the code, I typed these following lines:

View 4 Replies View Related

VBA Runtime Error 1004 On Formula

Nov 21, 2012

Application-defined or object-defined error 1004

when I register the formula in the macro they work perfectly, when I run I receive this message, really do not know why I thought maybe I should substitute something but really is too complex for me to go inside that.

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(IF(AND(R[10]C[-2]0.01*R[10]C[-1]),INDEX(R[46]C[2]:R[49]C[2],RANDBETWEEN(1,4))&TEXT((1-R[10]C[-2]/R[10]C[-1]),"" #%""&"", ""),IF(R[10]C[-2]5,INDEX(R[55]C[7]:R[56]C[7],RANDBETWEEN(1" & _
" "",IF(R[27]C[5]>7,INDEX(R[55]C[8]:R[56]C[8],RANDBETWEEN(1,2))&"", "","""")))"
Range("D5").Select
ActiveCell.FormulaR1C1 = _

[code]....

View 1 Replies View Related

Runtime Error 1004 VBA Code

Feb 28, 2014

I wrote the below code, the error seems to be occurring on the ActiveSheet.Name line. At this line the debugger is giving the runtime error.

Sub ListWorkSheetNames()
Dim Sheetnames
Sheetnames = Sheets.Count
Sheets.Add
ActiveSheet.Name = 'SheetList'
Sheets(SheetList).Move after:=Sheets(Sheetnames + 1)
For i = 1 To Sheetnames
Range(A & i) = Sheets(i).Name
Next i
End Sub

View 5 Replies View Related

Vlookup Runtime Error 1004

Aug 14, 2009

I used the formula from this website to do a vlookup for pictures
www.mcgimpsey.com/excel/lookuppics.html

It was working great then I seem to have a problem currently I have 58 pictures on the spreadsheet and when I add the next one I keep on getting an error

Error reads

Runtime error 1004
Unable to set the picture property of the picture class

View 9 Replies View Related

Runtime Error 1004 When Username Not In List

Mar 13, 2014

I am getting an Run Time error 1004 Application-defined or object defined-error.

I am assuming this is occuring because the username is not in the list for the pivot table as I am using some code which includes Environ, some code that if user name is not in pivot table then bring back no data or make pivot table blank. I have included the code below.

[Code] ........

View 1 Replies View Related

Runtime Error 1004: Too Many Different Cell Formats

Feb 28, 2009

I've just recently ran into this Error (Runtime error 1004) "Too many different formats." I'll admit i have a ton of cell formats within my single worksheet.
perhaps most are for artistic purposes.

Still, my worksheet as it is causes this error, when running my scripts. When I wipe out all my format color, boarders, etc. my scripts run fine. Is there a maximum amount of cell formats that can be used? I'm using Excel 2003

Is there a way to circumvent this?. Not sure if using Excel 2007 is an option:
I've tried opening my project in 2007, but found that all my buttons were missing, plus it seemed to run considerably slower.

View 2 Replies View Related

Countifs For Date - Runtime Error 1004

Sep 23, 2011

Here is the data I am working with. On another sheet, I want to count the number of entries on Total requests sheet if Column M - Closed < Column Q - End Date. There is other criteria and I have this, which doesn't work: Run-time error '1004' Application defined or object defined error. I think it is due to the part where I'm working with the dates...

======================
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('Total Requests'!R2C3:R" & tot_req_row & "C3,RC1,'Total Requests'!
R2C10:R" & tot_req_row & "C10,""5-Very Low"",'Total Requests'!R2C13',""

View 2 Replies View Related

Syntax Range - Getting Runtime Error 1004

Feb 15, 2013

I know you can use the syntax range(cells(x,y),cells(z,w)) but for some reason unknown to be the following isn't working:

Set CurrSuppRange = TT.Range(Cells(1, CurrSuppCol), Cells(LastRow, CurrSuppCol))
where
CurrSuppCol = cboxCurrSupp.ListIndex
OurSuppCol = cboxOurSupp.ListIndex
LastRow = Workbooks(lbWkBkName.Caption).Worksheets(cboxWorksheet.Value).Cells(Rows.Count, "A").End(xlUp).Row
(each return integer values)
Set TT = Workbooks(lbWkBkName.Caption).Worksheets(cboxWorksheet.Value)

(tested and works fine)

View 2 Replies View Related

Runtime Error 1004 - Cells Not Locking?

Jul 23, 2013

I am trying to protect cells & password protect my workbook on closing. Here is my code. Not only is the cell locking code not working, but it is also preventing the code from re-hiding the columns ("P:P,R:R,W:W,U:U,AA:AA"). Here is my code; I've tested it on a blank sheet & it was working:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Application.EnableEvents = False

[Code].....

View 2 Replies View Related

Runtime Error '1004' Section Is Too Large

Jan 19, 2007

I am copying a large section of formulas. It has to paste one row of formulas into an area from A2:AI46754.

I get the above error in VBA.

View 9 Replies View Related

Runtime Error 1004 :: Active Cell Contains A Value?

Mar 27, 2009

I want the code to determine if the cell before and after the active cell contains a value and if both these conditions are true to delete the active row.

I keep getting a runtime error 1004 when I try this...

View 9 Replies View Related

Runtime Error 1004 :: Cut Invoices Out Of Several Workbooks

Jul 20, 2009

I'm currently trying to make a script that will cut invoices out of several finance workbooks, I have ommitted the main part of the processing as it happens later and does not have any bearing on this problem.

When it reaches the line I have marked, it breaks with the error "Runtime error '1004': Select method of worksheet class failed". I'm fairly sure the problem lies with the Workbook but at the time of break WbZ is set to a valid workbook (FinanceSheet_SP_Thomas.xls in this case) and the workbook contains a sheet called "Invoice Sheet".

Sub Extract() ...

View 9 Replies View Related

Copying Data Gives A '1004' Runtime Error

Nov 16, 2009

worked at one point but now gives a '1004' runtime error. What I am trying to accomplish is have the user display a line of data on a userform, modify it, then write it back to another sheet in the workbook.

My ...

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved