I am trying to build a list to put into Microsoft access
Cell A1 has "
Cell B1 has 8W###
Cell C1 has " Or
I want a formula that makes D1 "8W### Or ". edit: I got it =(A1) & (B1) & (C1)
I kept trying to use "" and it would work.
I have a spreadsheet and an Access database that contain the same items and prices. The database and spreadsheet are on a server shared drive. I would like the spreadsheet to automatically update its list of items and prices from the Access database whenever the spreadsheet is opened. Right now, we update the item and price list on both the spreadsheet and the database but I want to just update the database and have the spreadsheet pull the updates from the database.
View 2 Replies View RelatedUsing a pivot table, I'm linking to an external data source in ACCESS. I use the pivot table wizard, select the database (spend2008.mdb), and then ONE table that is not in a relationship/linked etc, its just a plain table, and then I get the error
"The Microsoft Office ACCESS database engine cannot find the input table or query "mdb.summary". Make sure it exists and that its name is spelled correctly".
This is new spreadsheet, so new join to the data. I'm selecting the table from the Query Wizard, so not possibility of a spelling mistake. I've been all through the database to make sure I haven't set a link or relationship. There are a total of 4 tables in in the DB that are related, but I've deleted the relationships
Got something I need to do that I have never done.
I have a column on a worksheet1:
NBC//1
ABC//1
ABC//2
ABC//3
ABC//4
CBS//1
CBS//2
I have a column on another worksheet2:
NBC
ABC
CBS
In the column next to the above on worksheet2, i want to pull the numbers in for that (the numbers are contained in ONE column):
NBC | 1
ABC | 1 2 3 4
CBS | 1 2
Im not sure how to go about this?
In the example I attached, on sheet 2 column B, that is where I want the numbers to build to.
I have a workbook with two spreadsheets, one blank and one with a Header row and a list of employee names and random dates that will continue to be added to at the bottom per a copy and paste.
What I need is to have a form dropdown on the blank sheet (per a cell reference) that shows employee names and dates that has that name next to it in the data sheet.
The employee names are in three columns Last, First, and Middle and the dates are in a separate column (A).
I guess I need a VLOOKUP that can take the range and pull into the dropdown the employee name and every date associated with that name.
This below code i find out from this form Skip the row find next
but i need it i have column fom c to M
if the cell k valur or j value = 0 or empty skip the enitre row go next row ifthe kvalue <> 0 or <> empty
do something
the below code where do i moify .
Set c = .Find(MyInput, LookIn:=xlValues, SearchOrder:=xlRows)
If Not c Is Nothing Then
Count = Count + 1: ff = c.Address
k = c.Row
Redim Preserve wsName(1 To Count): wsName(Count) = ws.Name
Redim Preserve rng(1 To Count): rng(Count) = c.Address(0, 0)
Do
I was trying to use this guide to add a date picker in:
[URL]
and noticed that the date picker does not appear in my active x controls list.
I am definitely on Excel 2010 so it should be there right?
i have been looking around here and it seems like my problem is similar to many's regarding the vlookup function. to me, what i'm trying to do sounds simple enough, but it can't get it to work. i have a table that has three columns, Item Number, Item Description, and Amount.
Each item has it's own number, a corresponding item description, and ammount (obviously), but there are some repeats. What I want to do it input the Item Description, and have one field automatically pull up the Item number, and the next field, pull up the price. I am using a drop-down list for the Item Descriptions utilizing data validation. here is formula that i have been trying to use that is not working: =VLOOKUP(B2,Sheet1!$A$2:$C$18,1,FALSE). i have attached a trial worksheet that i am using to work things out on.
I have a database that has outgrown excel (over 77,000 rows now) so i now have it in Access. I want to replicate the following situation I used to have using lookups in excel with a query to the Access database: Paste a list of identifiers into an excel sheet. Values corresponding to the list are returned from the database in adjoining columns. The list can be from 10 to 300 cells long an returns data in 14 columns. I have had a go with the Import external data wizard which generates a query but have not been able to work out how to base the query on a list of cells in excel and have the query return values for each of the identifiers in the list.
View 2 Replies View RelatedI am familiar how to perform the task of bringing in an access table into excel, then using a data validation filter to control the data set in Excel 2003.
How to replicate this procedure in Excel 2007?
I am Generating Excel file with Macro using my asp.net (c#) application.
I am able to generate Excel file in development environment, but in Production it gives following error:
"Programmatic access to Visual Basic Project is not trusted Line: Microsoft Office Excel"
I did googling a bit and found that I have to open Excel file physically make few security related changes in macro as below.
1. Open the Office application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
2. On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access.
3. Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.
Can i do above changes at runtime (using some code)?
I have an Excel file ( named "Classes.xsl"), that has a worksheet (named "RawData") that is layed out something like:
Name Dept Class
John 0547 Class 1
Jane 0368 Class 1
Jim 0368 Class 2
Sue 1235 Class 2
I am trying to get an Access-Like report without utilizing Access. Is there a way to generate a report similar to below using Excel as the data source (could be mail merge, perhaps a macro with a printout) I am not sure which way to try and was hoping someone could point me in the right direction...and possibly provide an example.
Class EnrollmentClass 1John 0547
Jane 0368Class 2Jim 0368
Sue 1235
I have a brand Master in below format in sheet 1
brand codebrand Description10a11b12c13d14e
and Product master in sheet 2
product codeProduct Description000roof paint001floor paint002exterior paint003marine paint004wood finishes
I need in sheet 3 for each brand
Brcode + Product CodeBrand Description and Product description10000a roof paint10001a floor paint10002a exterior paint10003a marine paint10004a wood finishes
How can I control non-Microsoft applications using VBA? For example, how can I open a pdf file, print it and then close Acrobat Reader?
View 5 Replies View RelatedDoes anyone know how to convert a Microsoft Word .RTF file into a Microsoft Excel file? I have tried everything I know and I have no success other than getting everything crammed into column A in excel.
View 9 Replies View Relatedhow to build a formula that would allow my remaining UOM and it's associated cells to move up to the days remaining UOM once my time goes below, say 31 days.
View 3 Replies View RelatedI need to create a calculator that tells me how long a invoice will take to be paid using my current processes at work, working days only.
I need to imput the date the invoice is received and then for the rest to be worked out automaticly
I need it to do the follwoing ....
I have been trying to create a macro that would create a schedule for me based on date and a product type. In columns K-N I have an IF statement that tells when a shipment would need to arrive in a row that corresponds with a date (column A is 1/1/2015 - 12/31/2015). On the next page I would like to generate a schedule that lists the dates that all of the products are needed in chronological order. I've tried to use custom functions like finding the nth_occurence but it just gets way too messy with so many shipments.
View 6 Replies View RelatedThe code im stuck on is as below:
I am getting "runtime error 438. Object doesn't support this property or method".
[Code] ..........
So I have a formatted empty template listed as BUILD, It has two main parts, a left half that serves as a key and a right part that I want to populate with data.
So the first part of the code copies the key/margin then as I commented I want to start building sets for.
The second part of the code I'm trying to run a loop through user entered data stored in listbox2 grabbing each entry and pasting it in the first row of each set.
Is it possible to build a simple input dialog box with only code?
I'd really like to avoid having a userform permanently in the project.
Ideally it would either display and be dismissed with code only, or be constructed as a real userform, displayed and dismissed, then deleted from the project afterwards.
I am aware of excel's built-in input dialog box, but I'd really like to get away from it's awkward keyboard functionality if it's somehow possible to do. Although that would be ideal if there were some way to make the textbox's behavior to the Home, End, Tab and arrow keys more like that of a normal textbox, but I am unaware of any such options.
I am building a table for a group of vlookups, and I have a bunch of variables that need to be defined. I need to identify each and every possible combination of about seven different terms.
E.g. this simple table:
A B C
1 red1 org1 yel1
2 red2 org2 yel2
leads to this list:
red1org1yel1
red2org2yel2
red1org2yel1
red2org1yel1
red1org2yel2
...
etc.
This model is almost exactly what I am starting with, except there will be six or seven columns, depending. Also, no two variables within a given column will mix with one another, e.g. no "red1red2yel1" etc.
Rather than do this by hand, I thought there must be a programmatic solution (or even a little formula).
I need to build up one dynamic report.
Here is the task:
1) Everyday workers fill up with records in each sheet (record count can be different, because there is a "List" table)
2) Each day is in one separate sheet (for example: 1st June is in sheet with name 1, 2nd June is in sheet with name 2 .)
3) ALL sheets are named 1, 2, 3 ... 31
4) And in the sheet with name "ALL records" must be generated all records from sheet 1,2, ... 31
Here is my Example
I'm trying to build a rota for work, with the aim to gradually make it more and more complex.
So:
First step - I have made a basic skeleton, Names going down, Monday with beginning and finish, Tuesday with beg and fin etc to Friday going across. With hours worked during the day being calculated, minus 30 mins for lunch.
=(24*SUM((D5-C5))-0.5)
Second step - Now, for instance if you begin or finish work between the hours of 11:00 and 14:00 I need to make it so that you don't loose that half an hour.
I'm trying to insert a Microsoft Rich Text Box control (RICHTX32.OCX) on an Excel user form. It does it once, but on the second time Excel uninstalls it and posts a "The subject is not trusted for the specified action." My security level is low and I have checked the Trust all installed add-ins and templates.
View 2 Replies View RelatedI am currently working on a project, and basically I want to convert Microsoft word data into excel. Is there a way excel can read specific fields of data from word?
For example, search for a field called a particular name and a corresponding value amongst several data held within Microsoft word pages?
I have a query which is giving me some trouble when returning the data from ms query. The query was written in mysql query browser and returns 2 columns of data - a date and a number. MS Query correctly processes the query but when the data is returned to excel only the second column is returned. Here is the query:
SELECT
(SELECT
max(l.the_date)
FROM
nc_view_date_functions AS l
WHERE
l.week_of_year = d.week_of_year
AND l.yyyy = d.yyyy
) AS week_end_date,
count(r.consent_id) AS weekly_count
FROM
rg_resource_consents AS r
INNER JOIN nc_view_date_functions AS d
ON r.application_date = d.the_date
GROUP BY
d.yyyy, d.week_of_year;
I used the Get External Data function within Excel and the query returned the fields in alphabetical order. I went back into the query and rearranged the fields into a more logical order, but when refreshed the query continues to return the fields to Excel in the original alphabetical order.
Am I missing something, or do you only get one shot at the order of the fields being returned?
I would like to have set of data as Parameter if possible.
Example: In Microsoft Queries I have two data sets. In first one shows let's say Item Number, and second one I should have table with components of all Item numbers listed in first table.
So first table I set one Parameter (?) and i get like 8 results. I would like that second table takes those 8 results (sometimes more or less then 8) as parameter and shows data (components) for them.
If I set sign '?' I can only select one cell as parameter, is there any way I can select multiple cells as parameter and use it like SQL function IN ?
Code:
SELECT 1.ITMNR, 1.COMNR, 1.QTYPR
FROM 1.1.1 1
WHERE 1.ITMNR IN ?
And second table should look for results in first table under 1.COMNR and select it as 2.ITMNR
Code:
SELECT 2.ITMNR, 2.COMNR, 2.QTYPR
FROM 1.1.2 2
WHERE 2.ITMNR IN ?
My code here...
Sub ClearSheet()
Worksheets("Summary").Activate
If Range("CustInfo") = False Then
Range("ICompany, IPhone, IFax, IContact, ICell, IEmail, IAddress, IPOBox, ICity, IState, IZip").Select
Selection.ClearContents
Else
Range("IJobDescription").Select
Selection.ClearContents
End If
For I = 1 To 5
Range("Qty" & I).Select
Selection.ClearContents
Next
End Sub
keeps giving me an error 400. I have reinstalled xl just to see if it was related toan error I recieved earlier relating to VBE6.dll. This still works in another workbook but it doesn't work in this one. All I did was change the Range names and cleaned it up a bit.
I have a worksheet that pulls data from an ODBC datasource (import, External Data). The datasource is a SQL server 2005 database.
the query's sql is complex enough that it says it cannot be represented graphicly.
part of the WHERE clause fro my query specifies a date range for one of the date fields.
I am wondering if from within the SQL query in Microsoft Query I can reference the value of a cell. that way i coudl have the user enter his date range values in two specific cells.