Run-Time Error '1004' Cannot Open PivotTable Source File 'Consolidated Report'
Oct 2, 2008
I have a macro that takes the date from different excel sheets, consolidates the data and renders the pivot table and chart accordingly. It was working fine when the date range defined for the pivot table was static. Now I have made it dynamic since the data range changes each month depending on the number of days it has got. When I run the macro, it runs succesfully, generates the report and save & close the report, but after that I am getting this error. Errorneous
View 2 Replies
ADVERTISEMENT
Nov 14, 2012
I am trying to adapt a macro to run on data that is consistently in the same format but the data does change, the macro needs to run over and over on new excel workbooks, dynamic range ? instead of a set source ?
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"weekly71012!R1C1:R9379C30", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
I am sure this is the problem as i initially created the macro using weekly71012 excel file however i just want it to run on every file i pull it off?
View 2 Replies
View Related
Sep 17, 2008
I am opening the file through the GetOpenFilename method. I am facing an error in of the cases... Like say or example i try and open a file with the XYZ.xls...which is already open. the system generates a mess saying
"reopening will cause any changes you made to be discarded. do you want to reopen XYZ.xls?"
if i click yes...it works fine by reopening the file but when i click to No...Runtime error 1004 comes:
"Method open of object workbooks failed "
and its giving an error here in the Workbooks.Open Filename:=sFilename ......
View 9 Replies
View Related
Dec 9, 2009
The script below scans through a specified folder on a network and scans every xls file inside that folder for a value. When it finds the spreadsheet which contains that value (a policy number) it outputs some of the data from the matching file and also provides a link to the sheet in question.
It worked perfectly until I saved, exited and re-opened the file and now I am getting a 1004 run-time error every time I try and execute the script.
I cant for the life of me work out a cause.
More baffling, the wording of the error says that the file 'R:DirCancellations.xls' could not be found.
At no point is a file called 'Cancellations.xls' specified. Why has the script decided to search for a file called 'Cancellations'?
Sub Search()
ActiveSheet.unprotect
ActiveWorkbook.unprotect
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
Dim DirLoc As String
Dim PolNum As String
ActiveSheet.Range("C13:G25").ClearContents................
View 9 Replies
View Related
May 21, 2007
I have embeded a PDF file in an Excel Worksheet. When I double-click to open the PDF file the I get an error message: "Cannot start the source application for this object." However, the pdf file opens anyway. how to prevent the error message from displaying (without stopping real error messages)? (...happens in Excel 2000 and Excel 2003) NB: I researched this site first - user "Ed" raised the same issue 27/1/03 - but it doesn't appear to have been resloved.
View 4 Replies
View Related
Sep 6, 2009
This is what i am doing, i m trying to open file one after other in a folder, and process each of them for some information in it and then closing it without saving it. and looping through next.
Code runs great the first loop record, when it tries to get into loop second time, it breaks with error 1004: run time.
Workbook.Open failed...... I have no clue, as files are there folder is there and code runs the first time...
View 9 Replies
View Related
Apr 9, 2014
I have a sheet that will force user to enable macro before revealing the sheets and enabling them to key in data. But because I need to protect the workbook from user deleting sheets and also having some locked cells. I got the run-time error 1004 unable to set visual property of worksheet class
View 1 Replies
View Related
May 5, 2014
the problem I'm having is that when trying to copy the value of cell A2 and paste it in cell A4 with (INSERT with the second button of the sheet). this give me the error RUN TIME ERROR '1004 '.
View 7 Replies
View Related
Jul 24, 2008
having a nightmare with a pivottable in VBA. I cannot give it a dynamic datasource. Surely i must be coding this wrong.
it breaks on setting the range for PTRange with the error
Run Time Error '1004'
application-defined or object-defined error
View 10 Replies
View Related
Dec 10, 2012
Im almost finished my first code where the basic idea was to make activecell.name=activecell.value in the first column for the first 3 sheets. After messing with it i figured i couldnt have spaces in the cell name...then i learned i couldnt have random '()-'/ either. So i have the code formatted to do that and then its supposed to call to rename everything as well. but after the 1 sheets column is renamed the 2nd sheet gets error 1004 when range("A3").select is hit. What gives?
This is what ill be using the code on
[URL]
Here is the code
Code:
Sub RemoveChars()
' Works through first 3 sheets '
Sheets(1).Select
[Code]....
View 2 Replies
View Related
Apr 15, 2009
I am receiving the error:
Run-time error 1004 Application-defined or object-defined error.
It is occuring on the line of
Selection.QueryTable.Refresh BackgroundQuery:=False.
I am using SQL '05, Excel '03, and VB 6.3. I am trying to run a SQL stored procedure into Excel. So far all of my VB code seems to be running fine.
View 9 Replies
View Related
Mar 23, 2007
I am using the below code to select some spreadsheets under a folder, open them, select the entire font, change it to Arial and close the file. I am passing the file names from Column A and calling the below procedure in a loop. Everything works fine except the below problem.
If the file name I passed doesn't exist or the file is not accessable, I am getting an error message "File cannot be accessed. The file may be read only......" with two options "Retry" and "Cancel". When I click Cancel , it takes me to my code and stops at the line marked in red with Run-Time error '1004', Method 'Open' of Object 'Workbooks' failed. When the above error occurs , I need to just pass the value "ERROR" to ErrMsg filed and proceed further with the next file name.
Sub ChangeFont(FileName As String)
Dim objXL, objWb, objR ' Excel object variables
Dim Title, Text, tmp, i, j, file, name
Dim strPathToSaveTo As String
Dim strFileName As String
Dim wkb As Workbook
Dim wkb2Print As Workbook
strFileName = FileName
Application. ScreenUpdating = False
Set wkb2Print = Workbooks.Open(strFileName)
Cells.Select
With Selection.Font
.name = "Arial" .............
View 7 Replies
View Related
Feb 1, 2010
I am working with a bunch of files to do a dashboard. I have the main file which shows end results and what my users will see "Dashboard". I have about 10-15 files which I export weekly from MS Access. and I have the "Dashboard Data" file. In this file I have a sheet which links all the information for the files exported from MS Access. In here I have named ranges and dynamic ranges plus additional columns created to look up information in other tables. I use this file as my source data for my Dashboard File so that I can reduce the size of the actually dashboard. Dashboard Data file is about 5.5 mb and my Dashboard is 300kb. I have 12 branches that need to view the data over a radius of a hundred miles so the smal file are better.
My problem: I can get the Dashboard Data file to update without opening all the 10-15 other files. I cant however make the Dashboard file update without opening the Dashboard Data file. I have it to auto update without prompting but its still tells me that it cannot update. I was wondering if there is some VBA code I could use that would work like this:
I open the Dashboard File, Dashboard Data file opens so that Dashboard file updates and then close Dashboard Data File so that the user never has to see the Dashboard Data file. I dont want them to see it or have access to it.
View 11 Replies
View Related
Jan 12, 2009
I have 4 command buttons (A-D) so that the users can update after inputting new or revised Data.
My problem is that if updated in order, button D Macro below stops at the first "Selection.AutoFilter..." as if it cannot turn on the Auto Filter.
It will run if I manually set the AutoFilter on the sheet.
I think, and as I am completely new to this please feel free to correct me, the reason that it stops is that on the Button B is a "AdvancedFilter" Macro.
View 9 Replies
View Related
Apr 5, 2007
I have a piece of VB code that works perfectly on my pc. The spreadsheet has been emailed to a partner (different company) and the code no longer works and they get the run-time error.
Private Sub cmdPrintandsave_Click()
Dim Company As String
Dim irow As Long
Dim MyDate
MyDate = Date
Dim wsD As Worksheet
Dim wsD1 As Worksheet
Set wsD = Worksheets("AppBDataSheet")
Set wsD1 = Worksheets("AppBPrintable")
Company = Application.OrganizationName
Application.ScreenUpdating = False................
View 9 Replies
View Related
Sep 24, 2007
I have inherited a spreadsheet and code that I don't understand much about. I have code
Range("B4").Select
ActiveSheet.PivotTables("Kh07Pivot").PivotFields("Specialty").Subtotals = Array(False, _
False, False, False, False, False, False, False, False, False, False, False, _
False, False, False, False, False, False, False, False, False, False, False, _
False, False, False, False, False, False, False)
which error (run time 1004) and says 'unable to set the SubTotals property of the PivotField class'. If I make the number of False's equal to 12, then it doesn't error. However I don't know whether it is having an adverse effect on the data?
View 9 Replies
View Related
Jul 15, 2008
Every time I run it I get a Run Time Error Message 1004 when trying to custome sort on D4.
Rng2.Select
With Selection
.Validation.Delete
.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlYes
.Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:="L,M,H,Contract", DataOption:=xlSortNormal
End With
View 9 Replies
View Related
Dec 26, 2008
Range(Cells(1, 1), Cells(257, 257)).Select
This gives the Run-time error '1004'
I searched these forums for any old posts to work around this but did not find any threads. If anybody knows a good thread about this, I would be greatful.
Basically I run some code to generate Startcolumn, StartRow, EndColumn, EndRow. If there is a limitation of 256, the code is basically useless. I don't know of a way to use the A1 range method whilst using the Range("A1:....")
View 9 Replies
View Related
Jun 30, 2009
I have been working on the following worksheet, which has A3-Z27 cells right now and will grow/shrink with time. The spreadsheet shown below is just a portion of the entire worksheet. My vbscript is below the worksheet. I have two worksheets PL Dbase and Waiting List. I am copying columns B thru G from Waiting List if Column J is = to "Yes". To the next available row in worksheet PL Dbase columns B thru G...These columns have the same headings on both worksheets. I am getting the following error, when I click the button I assigned the macro to: Run-Time Error 1004 Copy Method of Range Class Failed.
Waiting List .....
View 11 Replies
View Related
Jan 14, 2010
Sub paste()
' paste Macro
' Macro recorded 2/20/2009 by lawryad
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.paste
ActiveSheet.paste
Application.CutCopyMode = False
ActiveSheet.Range("p1").Copy
ActiveSheet.Range("a1").Select
End Sub
Now everything works good with this code unless there is nothing copied to paste. In that case it will go to a "run-time error'1004' " I would like instead for a msg box to appear saying "You must copy required data first"
View 9 Replies
View Related
May 20, 2006
I want to paste data from a table without the header row from one workbook (Test1) into another (Test2), from Test2. Test1 is closed before this operation starts. I'm using the following
Sub Macro2()
Dim myTable As Range
Set myTable = Range("A1").CurrentRegion
Workbooks.Open "C:ExcelTest1"
myTable.Offset(1).Resize(myTable.Rows.Count - 1).Select
Selection.Copy
Workbooks("Test2").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
End Sub
When I have both workbooks open, comment out the line that opens Test1 and step through the code, it works fine but when I close workbook Test1, and run the macro, it generates the following error message: Run-time error 1004 Application defined or object defined error. When I click 'Debug' it has highlighted the row that has the offset instruction so there is no trouble with opening the workbook Test1.
View 6 Replies
View Related
Jan 8, 2007
im having problems calling the hlookup function in vba. Here is the code im using. I get run time error '1004'. Ive tried both setting the range, and manually inputting it. The User Defined Function takes no inputs, but is designed to get the value based on the relative position of the cell in the spreadsheet.
Option Explicit
Option Base 1
Function lastrow()
Sheets("template").Select
Range("A65536").Select
Selection.End(xlUp).Select
lastrow = ActiveCell.Row
End Function
Function summary1()...............
View 6 Replies
View Related
Apr 24, 2014
I have a workbook entitled "Correlation Matrix". I download data from my data provider and then update it in excel each night. I have several worksheets in the workbook, one called "MCC". Today I made a copy of MCC, named MCC2, to experiment, then deleted it. Now, after I update my data in Excel it gives me the msg, "We can't open the source file '(Path)[Correlation Matrix.xlsx]MCC2'".
The data updates fine and all values are correct in the worksheets. I've tried closing and reopening the file, closing and reopening excel, but I'm still getting this msg when updating the data.
View 2 Replies
View Related
Oct 22, 2008
I included a couple screen prints showing that the form exists, and that it is not misspelled in the code. What should I look for?
View 4 Replies
View Related
Dec 8, 2008
I have a vlookup table with will define the Job description based on the job number & cost code. The function is working fine but the problem come when i try to convert it into macro.
View 10 Replies
View Related
Feb 9, 2009
I ran my macro on a spreadsheet and it was fine.
But when I ran it from another spreadsheet I received the below error.
i.e. I placed the macro in book1.xls & from there open book2.xls.
ERROR:
HTML Run-time error '1004':
Application-defined or object-defined error
The error pops up when the macro reach the line "Rows ("3:3").select".
View 5 Replies
View Related
May 6, 2009
Is there a setting within excel that could cause a user to get a run-time error 1004? I have a workbook with a macro that adds additional sheets to the workbook when a button is clicked. The thing works fine on my machine and 3 or 4 other machines that I have had guys test it out, but I have a user that it will not work for. He is at a location about 3 hours away so I cant see exactly what he is doing, but he says after he clicks the button he gets a run-time error 1004 that says "Unable to set the PrintQuality property of the Page Setup class". I'm thinking that it is a setting within excel?
View 5 Replies
View Related
May 22, 2009
I cant get this code to run more than one time and when it does run it doesnt reset the password protection back on and it fails to complete all instructions.
I get a Run time error '1004'.
Unable to get the CheckBoxes property of the worksheet class
View 2 Replies
View Related
Aug 26, 2009
I am getting a run time error 1004 when i am running this macro. I got this macro to my earlier thread. As i am a beginner in a macro, I am unable to identify the problem. I have attached the workbook for the same
View 5 Replies
View Related
Sep 21, 2009
I have a macro, operating in my excel document. It works and does it's function. The only problem is every time this macro is running I get the run time error 1004, Method 'SaveAs' of object '_Workbook' failed. After clicking End in the error window it opens up a newly created sheet that I need. I don't need to save the file in the desktop, I need it just to open up like it does right now.
View 5 Replies
View Related
