Excel 2007 :: Script Error In WEB QUERY
Nov 7, 2012
I am using Excel 2007. I am having problem with web query. I want to get data from Komatsu parts catalogue, komatsu parts book, komatsu parts search, komatsu parts numbers, komatsu parts database this website. In excel 2007-> get external data tab->from web-> and i typed the website(Komatsu parts catalogue, komatsu parts book, komatsu parts search, komatsu parts numbers, komatsu parts database) and click go then i receieved Script error
[An error has occured in the script on this page]
Line: 3
Char: 1373
Error: Expected identifier, string or number
Code: 0
[URL] .....
Do you want to continue running script on this page?
yes no]
like that i received an error
View 1 Replies
ADVERTISEMENT
Feb 16, 2012
I've inherited an SQL server database which imports data from a multitude of spreadsheets each night. Unfortunately the previous data is not deleted from the database or spreadsheets, so there are duplicated records throughout the database. The method they have been using to get data out is to "select distinct", which gets around the problem (not my preferred solution).
I now need to produce an invoice from the database, so I select distinct records for the previous month (based on date) from, say, the TRUCKING table into a TEMP table, so I can do my calculations from there.
But there are a couple of columns which prevent me from getting only unique records. They are the unique ID column and the TIMESTAMP column they have added.
If I try to select all the individual columns, except for the ID and TIMESTAMP, the string length goes over 256 chars and the query truncates.
So I tried "SELECT DISTINCT * FROM TRUCKING WHERE etc etc" but I get the ID and TIMESTAMP fields, which defeats the purpose, and I get duplicate records.
Is there a way to say, for instance, SELECT ALL EXCEPT the fields I don't want?
View 9 Replies
View Related
Apr 11, 2012
I have recently installed excel 2007 how do you edit and ODBC query from the spreadsheet. In 2003 you right clicked on a cell that opened a dropdown where you selected edit query.
View 1 Replies
View Related
Apr 22, 2014
I'm trying to query a web site and export the data to a single sheet in Excel. I've used the record macro function in Excel (2007) and come up with the following but I want to query over 20 pages from this site and wondered if there was any way in looping this macro to do the hard work for me;
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.futwiz.com/en/players", Destination:=Range("$A$1"))
.Name = "players_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
[Code] ........
On the second page the URL changes to;
URL;FIFA 14 Ultimate Team Players - FUTWIZ
and the .Name field changes to;
.Name = "players?page=1_1"
and on the 3rd page I get;
URL;FIFA 14 Ultimate Team Players - FUTWIZ
and..
.Name = "players?page=2"
View 3 Replies
View Related
Jan 6, 2014
I am wanting to create a validation rule so when two cells are added the together the answer must be less than or equal to 14:00 - otherwise a message box will appear
So for example if H7+I7 gives an answer of 13:00 in J7, that's OK, but if the answer is 15:00, the error box will appear. I know that data validation doesnt work on a cell that already has a formula so I'm hoping to use K7 for the Validation and then hide the column
View 4 Replies
View Related
Mar 25, 2013
Why this sql query doesn't return values? My excel version is 2007.
Code:
if object_id( 'TEMPDB..#TMPDOC') is not null
begin
drop table #TMPDOC
end
create table #TMPDOC (Code varchar(5) NOT NULL)
[Code] ...........
View 6 Replies
View Related
Oct 13, 2013
I'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.
I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.
View 2 Replies
View Related
Jan 19, 2014
I am trying to perform an SQL query on an Excel worksheet, as per one of the examples in Michael Alexander's book "Excel and Access Integration: With Microsoft Office 2007" and I am encountering an error message.
Could not find installable ISAM
To clear I am trying to pull data from one Excel workbook to another using SQL.
I am using Excel Excel 2010 64 bit.
This is the code I am running.
Code:
Sub GetData_From_Excel_Sheet()
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
[Code]....
I have some across a few times that relates specifically to Excel 2010 64 bit is that I need to install the following driver
Microsoft Access Database Engine 2010 Redistributable
However I already have this driver (I installed when I was trying to figure out how to write from Excel to Access).
Why am I receiving this message. It occurs when my code reaches this line:
Code:
MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly
View 9 Replies
View Related
Aug 13, 2014
How can we "read" information from the VBE Error Trapping options (Break on All Errors/ Break in Class Module/ Break in Unhandled Errors) programmatically?
Background: I am developping a spreadsheet that needs to be sent around to distant users. Some of them might not have the default "Break on Unhandled Errors" setting), which would just make my programme fail or risk to fail...
This issue has been covered in the thread below, but the proposed solution doesn't work on my side (always returning the same output!): [URL] .....
Hint: it has been suggested to look into the registry, which the linked post does.
Ideally, I would love to be able to change the setting programatically, but that seem near to impossible in excel 2007 VBA (unless calling an external programme that closes and restarts excel after having changed the option?...)
View 10 Replies
View Related
Jan 9, 2013
(Excel 2007), I have a template that I need duplicated for every reference. My Worksheet lists all the references and the macro use to duplicate a worksheet for every reference in the worksheet. The problem is when duplicating now, it duplicates the name of the template. For example, when the macro is ran sheet1= Template(1), sheet2= Template(2) ect.
Here's the code- I think it has something to do with the named ranges
Code:
Sub Macro1()
For i = 1 To Application.WorksheetFunction.CountA(Worksheets("Worksheet").Range("A:A"))
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Worksheets("Worksheet").Cells(i, 1).Text
Next i
End Sub
View 4 Replies
View Related
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
Aug 9, 2012
I am getting a "Run time error 5", "invalid procedure call or arguement" when I run a macro on a PC other tha the one on which it was created.
In this case both PC's are running on the version 2007 of Excel.
This is the highlighted statement when the macro fails
Code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"SOCX25!R1C1:R" & Lr & "C23", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="SOCX25!R7C25", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
View 4 Replies
View Related
Nov 3, 2012
Excel 2007
No empty rows
No empty cells
No calculations in cells
Field names look fine - no punctuation no merged cells
If I select one column - any single column I can generate a pivot table but not with multiple columns
View 4 Replies
View Related
Jul 18, 2013
We are using Windows7 with Excel 2007. We have old code from excel 2003 and earlier that was adding a msoControlPopup with other sub-controls using the code below:
Code:
Set HelpMenu = CommandBars(1).FindControl(Id:=30010)
If HelpMenu Is Nothing Then
' Add the menu to the end
[Code].....
I tried going to Excel Options>Customize>Reset to reset any customizations to the quick access toolbar as this was recommended by something else I found online. This did not fix the problem.
I can find the control in the Immediate window but it just isn't showing in the add-ins tab. if I can find and repair that XML file perhaps?
View 9 Replies
View Related
May 15, 2014
I get this error : Compile error : Sub or Function not defined. I found out that this might be caused by the solver not being referenced. But, this is fixed now but I still get the same error message.
I'm using Excel 2007 - Windows.
Here is the code :
Sub Macro1()
Dim i As Integer
Dim j As Integer
j = 4
For i = 1 To 4000
If ActiveSheet.Cell(i, 12).Value = 1 Then
ActiveSheet.Cell(j, 15).Value = ActiveSheet.Cell(i, 2)
[code].....
View 2 Replies
View Related
May 26, 2014
I having problem to determine the format value of dates that needs to be inserted in a Userform.
I have the following format in my userform for my Textbox:
[Code] ......
When I select the calendar in my userform that is set as follows:
[Code] .......
And select the first day of the month, it will always add the format as 05/01/2014 but if I select today’s date as example it will add it in the right format: 2014/05/26
I have included a sample to demonstrate the function of the calendar as I have declared it public as d in a module, therefore making the population of my userforms textbox easier to add information on dates.
Calendar.xlsm
If you add 2014/05/01 - 2014/05/26 and 2014/05/06 you will see where the system is having problem with the month and dates I guess the system confuses the two?
I need the format as follows: yyyy/mm/dd
View 2 Replies
View Related
May 7, 2012
I am trying to develop a code which extracts the data from text files inside a folder (Folder test in my desktop) into one sheet. The Macro is in the workbook “Text Extract” which is an excel 2007 file. The data of each text should be copied to Sheet1 of this workbook one below the other. For testing purpose I have kept only one text file in the folder and was trying to copy the data from the text data extracted sheet to Cell A1 of Sheet1 of workbook “Text Extract”. The code works fine till copying the data, but shows below error in the line “Selection.Paste”:
Run time error 438: Object doesn’t support this property or method.
Below is the code:
Sub LoopThroughFiles()
Dim strFile As String
Dim strPath As String
[Code]....
View 6 Replies
View Related
Mar 16, 2014
I've set up an email to be sent via a macro (excel 2007 and outlook) and I expanded the body of the message and it now comes up with an error "Compile error: invalid outside procedure"
It was working when I only had 4 lines and now it's not working.
strbody = "Hi," & vbNewLine &_
"abc." & vbNewLine &_
"def." & vbNewLine & vbNewLine & _
"ghi" & InputBox("Enter Response date (dd/mm/yyyy)") & vbNewLine & vbNewLine & _
"jlk." & vbNewLine & vbNewLine & _
"lmn" & vbNewLine & vbNewLine & _
"ABC" & vbNewLine & vbNewLine & _
"XYZ" & vbNewLine & _
[code]....
View 3 Replies
View Related
Nov 26, 2012
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
View 3 Replies
View Related
Jan 10, 2012
I am using Excel 2007 and the following code is generating an error 1004 (Method 'Range' of object '_Worksheet' failed) on the .Range select line. I am trying to select a bunch of noncontiguous ranges and then format them all at one time.
Code:
With Sheet1
.Range("B9:G9,B10:D11,E10:E11,F10:G11,A13:G13,A14:D20,E14:E20,F14:G20,A22:H22,A23:D24,E23:F24,
G23:H24,A26:H26,A27:D28,E27:F28,G27:H28,B30:G30,B31:C32,D31:E32,F31:G32,B34:G34,B35:B36,E35:E36,C35:D36,F35:
G36,B38,B39:C40,D39:D40,E39:F40,B42:G42,B43:D50,E43:E50,F43:G50,A52:G52,A53:C54,D53:D54,E53:G54,
G61:G62,H65:H66,A56:H56,A57:H60,A61:F62,A64:H64,A65:G66").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With
I've written longer lines of code, so I don't think it's a line length issue. Is there a limit to the number of ranges or cells that Excel can select at one time?
View 4 Replies
View Related
Aug 11, 2008
I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error:
Run-time error '429':
ActiveX component can't create object
and is stopping on the line
Set OutApp = GetObject(, "Outlook.Application")
FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error.
Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String
'Set OutApp = CreateObject("Outlook.Application")
Set OutApp = GetObject(, "Outlook.Application")
If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
View 9 Replies
View Related
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
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
Feb 2, 2010
I imported some data from an MDB file while working at home. created a macro to that effect. and it works fine at my home PC. but when i try t execute this macro on another PC or work place, then it gives error #13. i dont understand why. something is wrong with the code. i think it is the "WITH" code for query tables connection giving error ....
View 9 Replies
View Related
Feb 28, 2013
I Wrote a code which as intended to open each excel file in a folder and copy the data containing in it into a new sheet.
But While running the code the first excel file gets open, and an error message "Run Time Error 91-Object Variable Or With block Variable not set Error"
How to set the file which got opened from the folder to wbk variable.
Code:
Sub dataintoonesheet()
Dim i As Integer
Dim jk As Integer
Dim j As Integer
Dim rowstart As Integer
rowstart = 3
[Code] .......
View 9 Replies
View Related
Nov 11, 2009
I need to create a new query that pulls data from a 2007 Access database into Excel 2007. When I invoke the 'From Microsoft Query' option after 'Data/From Other Sources', and then select 'MS Access Database*', the only file type presented is '*.mdb' . However, I need to pull from an '*.accdb' file type. I have both Office 2003 and Office 2007 installed.
View 8 Replies
View Related
Dec 8, 2009
I have a freaking problem coming from a Web Query. Actually, I get an "Execution Error 1004" because of the following piece of
View 7 Replies
View Related
Jun 30, 2014
I was trying to use IF and AND conditions to calculate values for a Col. but when I attempt to sum.
It appears that the value is always "0". Below is my test data.
Col. H Col. J
Qty Result
Row 713 0 <-Excel Formula: =IF(AND(H7>20,H7<=50),"1","0")
Row 825 1 <-Excel Formula: =IF(AND(H8>20,H8<=50),"1","0")
Row 960 0 <-Excel Formula: =IF(AND(H9>20,H9<=50),"1","0")
Row 10Total 0 <-Excel Formula: =SUBTOTAL(9,I7:I9) / =SUM(I7:I9)
View 3 Replies
View Related
Mar 12, 2012
I have the below code that will do a data dump of an access table into excel based on an SQL query, is there a way to do a select statement that can match against the data in column C on the spreadsheet.
like:
"Select * From Bsmart where Serial_Number = ##[EXCEL COLUMN C1:Lastrow]##
Code:
Dim bkNew As Workbook
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String
Dim SQl1, SQL2, SQL3, SQL4, SQL5, varSQl As String
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
[Code] .....
View 1 Replies
View Related
Jun 6, 2012
I am using below code to delete the sheet from workbook.
For iTemp1 = Workbooks.Item(funEach.Name).Sheets.Count To 1 Step -1
Sheets(iTemp1).Select
If UCase(Sheets(iTemp1).Name) "TEMPLATE" And UCase(Sheets(iTemp1).Name) "WORKING" Then
Sheets(iTemp1).Delete
End If
Next
How can i avoid the below popup..
-Data may exist in the sheet(s) selected for delettion. To permanently delete the data press delete.
View 2 Replies
View Related
