VBA / Query While Delete Sheet Using Excel

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


ADVERTISEMENT

Excel 2013 :: Delete Using Microsoft Query?

Apr 8, 2014

Using Excel 2013. Is it possible to delete some records from Sheet1 if the records exist on Sheet2?

I tried several SQL variations in MSQuery but all come back with error messages.

I tried:

[Code].....

View 3 Replies View Related

SQL Query That Will Populate Data From Access To Excel Sheet Using Conditions

Jul 1, 2014

I have an Access table which has following fields:

ScanDate Number DataType
Type Number
Type1 Number
Type2 Number
BatchNo Number
Cases Number
Pages Number

Now I have a useform in Excel so that when the user selects the Date from the combobox then it should check for total batch numbers (Count(BatchNo)), total cases (Sum(Cases)),Total Pages(Sum(Pages)) where ScanDate= Date from the combobox group by Queue Number. The Queue Number consist of Type+Type1+Type2.

See attached the Excelsheet where the data should be populated to. WBCount.xls‎

View 1 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

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

Excel 2007 :: Delete Sheet Greyed Out

Jan 13, 2009

I am using excel 2007 and when I right click on the worksheet tab, delete is greyed out. My workbook/sheet is not shared. I also had the problem owith row and columns. CommandBars("row").Reset sorted that out but I can't seem to sot out the tab issue.

View 6 Replies View Related

Trying To Delete A Query

Oct 7, 2009

What my excel sheet is doing is importing a bunch of data from a query. I have all my queries linked to buttons through macros. There are going to be 20-30 buttons that are linked to a query, and each query will produce a different amount of data(in this case, account numbers). When pressing a button linked to a query(i also have one 'Clear' button that just deletes the data, thats all), i use a macro to delete the previous query so it can import the new query into the same cell. The problem I am having is that when a button is pushed and there is no query, I am getting an error message. Here is my original code(macro), and the underlined code is where i am getting the error

View 3 Replies View Related

Excel 2007 :: Delete Sheet On A Shared Workbook

Oct 29, 2013

I have a file that has an original file format .xls

I saved as .xlsx (I am using Excel 2007) with a different name so I could make changes to the new file without changing the original.

That turn off compatibility mode.... but it is still "shared". While it is shared I cannot delete a sheet. In fact, I really don't even want it to be shared. If the file is open, and someone else opens it, I want it to open as "read only". I cannot uncheck the "Allow changes by more than one user at the same time. This also allows workbook merging." checkbox because it is greyed out.

View 3 Replies View Related

Excel Macro Delete Rows Where Date Criteria Is In Another Sheet

Feb 28, 2014

I am trying to adapt a macro which deletes all rows in Worksheet 1 where the date in column K differs from a date in Worksheet 2. The date in Worksheet 2 will change each day.

I think the macro below could be used, but don't understand how to replace the "Value = ":70:" with a reference to the master date in Worksheet 2.

Sub test() Dim lr As Long, i As Long lr = Range("A1").End(xlDown).Row
For i = lr To 1 Step -1 If Cells(lr, 1).Value = ":70:" Then
Cells(lr, 1).EntireRow.Delete End If lr = lr - 1 Next i End Sub

View 2 Replies View Related

Delete Query Tables Macro Code

Aug 14, 2008

I am currently working on a project that uses Excel to parse a .txt document. Its working quite well for me. It functions by having two worksheets. In the first worksheet I use the " import external data" menu to import my .txt file. In another worksheet I have set up fields that show only the important information from the .txt file and leave the junk behind.

I’m looking to improve the functionality of this by adding a button to automatically clear the data in the first worksheet so that new data can be added quickly.

I have searched the forum and found a couple of threads on "QueryTables". After reading up on those, I have made a simple button with the following code.

Sheets("Sheet1"). Cells.Clear
Sheets("Sheet1").QueryTables.Item(1).Delete

After I load a .txt file and parse it using the formulas set up, I copy my needed information and then press this button. The cells clear, and the QueryTables are "reset" (maybe not the right word). Now a different .txt file can be imported and the process starts again.

The problem I am having is that. If there is no "QueryTables.Item(1)" to delete, I get a run time error. (Run-time error '9': Subscript out of range).

Would anyone know how to make my button conditional to having a "QueryTable" active? I.E. If I press it when there is no data loaded it doesn't do anything or give me that error.

View 4 Replies View Related

Use MS Query From A Sheet In Same Workbook

Jan 18, 2014

I am trying to do a MS Query from another sheet in the same workbook

I created a new data source using Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)

Tried read only checked and unchecked

Workbook is a .xlxs but same error with .xlsm workbook
Workbook is on the desktop

I receive the below error when trying to edit a MS Query table by right clicking Table>Edit Query

ODBC Excel Driver Login Failed

The Microsoft Access database engine cannot open or write to the file "(unknown)". It is already opened exclusively by another user, or you need permission to view and write its data.

How to use MS Query in the same workbook?

View 5 Replies View Related

Balance Sheet Query

Feb 15, 2008

I am currently creating a new system or coursework. I decided to create a balance sheet using Excel. I was wondering if it is possible to have a cell whereby you can enter a number for example £10 and it will add to a Total balance cell. Once it is added the cell where £10 was entered will reset itself back to £0 but the total will remain £10 in the total balance cell. I think its called continuing total or something but ive searched for ages and cannot find any info. If done it will allow the user to enter any money coming into the business at any given period of time and will add to a total.

View 9 Replies View Related

Refresh MS Query That Runs On Another Sheet

Mar 11, 2013

Currently i have a button on sheet 1, that contains this VBA code

Code:
ActiveWorkbook.RefreshAll

Sheets 2 , 3 ,5 all have a MS Queries.

However I only need sheets 2 and 3 refreshed , and not 5.

At the moment it takes long to refresh all , and want to specify which sheets need to be refreshed, to make it faster for the user.

FYG, I cannot remove sheet 5, as it is required on adhoc basis.

View 2 Replies View Related

Refresh Web Query Without Selecting Sheet

Apr 27, 2014

I am trying to avoid selecting sheets in my current project. I have a querytable in A1. I can get it to work if i selects sheet and range.

Can it be done using with as below

Code:
With Worksheets("meeting to look at")
End With

View 1 Replies View Related

Web Query Fails When Sheet Locked

Jan 26, 2007

I have a web query that returns a value to the first column of the row.

I have unlocked the cell (Format -> Cells, Protection Tab, uncheck the Locked check box).

If I run my web query, it works fine. If I then lock the worksheet, the web query fails, even though the cell that I am returning the web query to is not locked.
Code below:

Sub create_pay_proposal(pUserId As Range, pRespID As Range, pWorksheet As Worksheet, pRange As Range, pParms As String)
Dim strURL As String

'On Error GoTo WebConnError 'Error handling if unable to connect to database/URL

strURL = <removed For security>

With pWorksheet.QueryTables.Add(Connection:= _
"URL;" & strURL, Destination:=pRange)
.Name = "fnd_web"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False

View 5 Replies View Related

Inserting Query Tables In Active Sheet

Feb 26, 2013

When using

VB :ActiveSheet.QueryTables.Add

How would you get the code to overwrite whatever is in the worksheet as opposed to adding new columns?

View 1 Replies View Related

Excel Summation Query From IF Conditions

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

Excel 2007 :: VBA SQL Query Length?

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

Excel Column In Access Query Via VBA

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

How Do I Run A Query In Access Via A Macro In Excel.......

Jan 29, 2008

I have an access database that has many queries and in order to speed it up I have a Macro in access that runs it in no time, but I need to be able to run the query in access via a trigger in Excel and have come up with the below, but it's not working.

View 9 Replies View Related

Filter Parameter In Excel For MS Query

Jul 25, 2008

I'm trying to create a report for a coworker to run. I want to filter the parameter in a cell (ssn), so that she can run the report. I have added a button for her to execute..once she puts in teh ssn. The spreadsheet is linked to MS Query in the background. How do I tell the Query to link to the cell in excel for filtering?

View 9 Replies View Related

Query SQL 2005 Views With Excel

May 20, 2009

I've posted this on Experts Exchange too, specifically trying to target the SQL Server experts. http://www.experts-exchange.com/Soft..._24423637.html


SQL Server isn't really my thing. I have been asked to quote on a job to build some models. One of the components is to pass parameters to sql server views and return the data into a table in Excel. I never done this.

1. Is it possible to pass parametrs to a view thru Excel?
2. Will the view execute based on the users permissions (different users using the Excel template have different access rights)?
3. Any other things to consider?

I don't need specific solution, only really some broad advice. I really only want to know how feasible this is...

View 9 Replies View Related

Excel 2003 :: Referencing Cell As (MS Query) Value?

Jan 22, 2013

We have a query, that looks at an external data source (db2 i believe). It will pull all data under a certain product set, and as an additional Criteria we currently have Serial Number.

Now, if the value entered is the serial number, lets say '1234567' for example, it will happily pull all instances of that into the table.

However, what I would like is a cell where I can enter the serial number, and the query will look at the Criteria, the Value will reference a cell (where we can type any serial number), and it will then populate it! I thought that would be fairly intuitive even for a complete novice like myself - now I am likely to have nightmares about Function Errors!

What I have tried:

*Setting the value to a cell ref... ie instead of '1234567' i have set the value to =$a$1 and in that cell i have a serial number.

*Tried to create a parameter. So value of [Serial Number] I then get a prompt saying "Enter parameter Value (serial number) - Regardless of if i type a cell reference (ie =$a$1, or i input the actual serial that worked before when it was '1234567' I still get a function error!

I keep trying different permutations of things, but I am ill prepared.

View 1 Replies View Related

Data Sorting Query - Get Excel To Calculate?

Feb 10, 2013

I am a teacher and have a recurring problem when trying to sort data in excel. The problem involves national curriculum sub levels. For those not familiar with these levels, there is a main level then 3 sub levels. The problem is that they are counter intuative to the way excel sorts data. The sub levels work like this:

4a - highest
4b
4c
3a
3b
3c
2a
2b
2c - lowest

There are lower and higher levels, but you get the idea. If I have a set of pupil data that I want to sort into decending order, I find it difficult in Excel. E.g.

Jacob2c
Mary3b
John2a
Sally2b
Sarah2b
Joe3a
Bill3a

If I manually sort the above data into descending order of national curriculum level it will look like this:

Joe3a
Bill3a
Mary3b
John2a
Sally2b
Sarah2b
Jacob2c

but if I try to get excel to do it, obviously excel just treat the levels as alpha numeric and sorts them like this:

Mary3b
Joe3a
Bill3a
Jacob2c
Sally2b
Sarah2b
John2a

I know there must be an easy way involving creating a custom list, but I don't know how to do it.

Problem 2

A related query involves finding a way to get excel to complete the data in the Sub levels progress required column below:

NameLevelTargetSub levels progress required
Mary3b4a
Joe3a4a
Bill3a4a
Jacob2c2a
Sally2b3c
Sarah2b3c
John2a3a

At the moment I manually complete this. E.g. for Mary the Sub levels progress required would be 4, as there are 4 sub levels between 3b and 4a. For Joe it would be 3 as there are 3 sub levels between 3a and 4a, etc. Is there a quick way to get excel to calculate this.

View 6 Replies View Related

Excel Web Query Is Returning Blank Data?

Jun 23, 2013

I am trying to get data in excel sheet using web query from the following web link:

But I the data returned is a 'hypen' (i.e. a dash symbol). I am getting the row/column headers but not the figures or the numbers under them.

View 2 Replies View Related

Returning Data From Microsoft Query To Excel?

Jun 23, 2013

I am trying to use queries I have been running in MS SQL Server Management Studio, to return data in Excel where it would display as pivot. Some queries I was able to use through Excel but few others are not returning anything.

I am doubting it has something to do with the query itself - but they are displaying results properly in MS Query, they are just not returning any data to Excel.

View 3 Replies View Related

Excel 2003 :: Web Query - No Data Has Been Found

Sep 20, 2013

I am trying to set up a spreadsheet in Excel 2003 that pulls data from a lot of different websites and formats it all for me. I have managed to do this with a few websites but there are two which I'm having problems with.

This is the table I am trying to get, but when I do a manual web query and select the table and click OK, it says that no data has been found.

[URL] ...

View 1 Replies View Related

Query Corporate Directory To Copy It Into Excel?

Nov 23, 2011

I am trying to query our corporate directory to copy it into excel. The problem is that the table i want to copy has 90 pages. I do not know VBA very well at all. Is there code that I could input that would copy the table on page 1, paste to excel, then move to page 2 and copy/paste to the next empty row?

View 1 Replies View Related

Excel 2007 :: Edit Query From Spreadsheet

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

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 View Related

Excel Query Using Transfer Data From System?

Aug 12, 2013

I am looking for information on how I can use the Transfer Data from System i Add-In for Excel to only get data that equals the data in one of my columns (both sets of data are strings). Unfortunately, I cannot just get the entire contents of the System i table since it contains more than the maximum allowed in Excel.

View 1 Replies View Related







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