I'I need to randomly input values into row cells and when press enter key, it calculates the total value and show in another sheet. Example, in sheet1, I input random values in A1,C1,D1,Z1 and when I press enter key, it calculates the total value and appear in the first row cell, A1, of sheet2. All by VBA coding.
View RepliesI have table1:
Name, Date, Points
Peter, 1.8.2005, 100
________2.8.2005, 200
Paul, 1.8.2005, 100
________4.8.2005, 300
etc. and I need to fill the blanks with Peter, Peter... , Paul, Paul, ... etc. so, that I can later take it as a group ID and work with the data.
Preferably with SELECT query, but insert or update is also possible.
I am using Access 2010 . I need to format cells inside the Excel spreadsheet
Here is my code:
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim intColumn As Long
Set ApXL = CreateObject("Excel.Application")
[Code] .....
The code "With xlWSh.Selection" returns a run-time error 438 - Object does not support this property or method
When coding, when entering a "bang" ("."), Access normally returns the next piece of code. The code above does not! How to I correct this?
I do have a reference to Microsoft Excel 14.0 Object library....
I have had to copy a column of dates to a new column to extract the month and be able to make subtotals based on each month.Now I am trying to tidy up the column by removing all the cells containing the month and leave behind the subtotals.
I have got a little way there using:
lastrow2 = Range("A1").End(xlDown).Row
With Range("A1:A" & lastrow2)
Set RngFnd = .Find(Total).
Set rngDelete = .ColumnDifferences(Comparison:=RngFnd)
rngDelete.ClearContents
But it only clears the cells down to the first subtotal so how can I get this carry on?
Alternatively I could use code to find the subtotal, and then cut and paste it into the next column over if that would be easier?
I have a few problems and I want them to be able to be done from switchboard:
1. Is there a way for me to export a particular report (after selecting it) to a closed excel template, that is formatted? It would open the excel template (that has a logo and column headings), export data to below the column headings, then save the file with a unique name?
2. Also, a way to import data from an excel file, after allowing the user to select file? Only data below the column headings mentioned above. Same data will be appended to existing table.
I have spent the day using Access 2010 and attempting to move information from a parameterized query into specific cells in an excel template. It runs smoothly until I attempt to reference the query at which point I run into th error "Too few parameters. Expected 1." Currently my reference code looks like this:
Dim T As Recordset
Set T = CurrentDb.OpenRecordset("SELECT [8D Data].ID, [8D Data].[Customer Closed], [8D Data].[Days Open], " & _
"[8D Data].[Open Date] , [8D Data].[QN #], [8D Data].[Last Report Date], " & _
"Leaders.[Leader Name] , Leaders.[Leader Title], Leaders.[Leader Phone #], " & _
"Leaders.[Leader Email], [8D Data].[Part Description], [8D Data].[Customer P/N], " & _
"[8D Data].Customer , [8D Data].[Vehicle Year], [8D Data].[Problem Description]" & _
"FROM [8D Data] INNER JOIN Leaders ON ([8D Data].Lead = Leaders.ID)" & _
"WHERE ((([8D Data].ID)=[Enter QCR #]));")
I am trying to write a VBA Module that will look in a directory for Excel files with a specific file prefix, and then import specified cells into a temp table. Once it processes the first file, it copies the file to another folder, and then kills the file. However, when I run my current code, it goes through the loop the first time, but fails on the second attempt, because it is still looking for the first file it came across even though it has been moved. The code is pasted below:
Sub Count()
Dim xlwrksht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim nIndex As Integer
Dim strMvPath As String
Dim mvPath As String
Dim strFile As String
[Code] ....
I receive a bunch of excel files with items to be added to a database. some of the items are to be added as new, some to be deleted and others to be modified.
I identify the action (add/delete/modify) by looking at a column in the excel file e.g. column A.
At this point I am fine with the add/delete because I can filter the data or bring all the files to a temporary table in an Access database via vba script and then running queries to do the rest.
My issue is that the spreadsheets have a lot of columns and for the modifications what they do is use the same file for instance, make changes on the cells (any cell) that need modification and then highlight, bold or underline only the items that need modification and ignore the rest of the file (a tedious process since I have to then open each file and manually modify the database).
I am writing the following code that will first of all display column headers dynamically using "Headers" field data from Access table and then find out the sum(volume) using column header and first column values. The following code works fine to display headers dynamically in Excelsheet from Access table but doesn't display sum(volume) in all the corresponding cells. As I can't attach the Access table so I have stored data from Access table to sheet named "Access Data" as attached. The sheet2 named "Report" should populate total volume .
Code:
Public Function Inputdata()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim i As Integer
[code]...
I have the code below which takes information from a form on access and sends it over to the correct place on an excel spreadsheet template. This works fine but I then need it to save and send on outlook.
The issue I am having is that the saved document is not attaching to the e-mail. The subject etc all work fine but the excel spreadsheet just doesn't attach. When I go into the folder I have specified for the document to be saved in it isn't there either. :0(
The code for the e-mail "callmail" function works perfectly for word documents but I don't know if it is different for an excel file.
Code:
Private Sub Command154_Click()
On Error Resume Next
Dim appExcel As Excel.Application
Dim wbook As Excel.Workbook
Dim wsheet As Excel.Worksheet
Set appExcel = New Excel.Application
[Code] .....
Hello,
I did a cursory search of the forum and didn't find anything (probably more my search than the content). I also think I know the answer to this, but I want to clear it up once and for all.
Is it true that I cannot insert an image into an Access Table in a way that it treats the image like data (e.g., it comes up on reports as entered).
If so, and I suspect it is so, what would be the best way to associate a row with a certain image in Access? Here's what I'm hoping to do: I have a list of projects and all of them have a status. Right now the "status" column includes the text "Green," "Yellow," or "Red." I would rather have this text display as green, yellow, or red color rather than text. Is this possible?
Thank you!! :D
Hello,
say for example I have a particular column in Access that I would like to force users to enter as percentage only, is there a way to format the cell as in Excel to create such effect? Thanks a lot!
Regards,
Anyi
In a query design view, I have two fields, LastName and FirstName. In the Field of a blank column I enter [LastName]&[FirstName]. In the query the last and first names are now connected , like SmithJohn. How do I put a space and or a comma to separate them?
View 1 Replies View RelatedOnce again, I'm working with a file that I got from somewhere else that isn't in *quite* the right format.
What I have is a table listing all of the titles and their holdings we have in various journal databases. The table looks something like this:
|____TITLE____|___COVERAGE______|____DATABASE___|
|_ABC Journal_|_1998 until 2000_|_EBSCOhost DB__|
|_ABC Journal_|_1953 until 1986_|_Proquest DB___|
|_ABC Journal_|_1980 until 2006_|_Free Journals_|
|_XYZ Journal_|_1899 until 1956_|_CINAHL DB_____|
|_XYZ Journal_|_2000 until 2006_|_EBSCOhost DB__|
|_XYZ Journal_|_1955 until 1999_|_Proquest DB___|
The titles, as you can see, are listed more than once, with one record for each holdings record from each database where that title is found. For my purposes, I need the holdings to show up all together. I can do this in a report, by sorting by the title, but I really need to have it all together in its own cell--one line per title. The above table should look more like this:
|____TITLE____|_______________________COVERAGE____ _________________|
|_ABC Journal_|_1998 until 2000_,_1953 until 1986_,_1980 until 2006_|
|_XYZ Journal_|_1899 until 1956_,_2000 until 2006_,_1955 until 1999_|
I don't really care what happens to the database field, but if it helps, the title and the database fields together would be a unique combination in the table.
Currently, I am working on an exported version of this table in excel, merging the cells 2 by 2 with a function command like this: =A2&" "&B2. Since there are 32366 records, I hope there is a quicker way. At this rate, I'm not sure if I'll be done for 100 years.
i'm creating a table and would like to have a list of items to chosefrom in a cell to keep from having to type an answer. e.g countries , or Prefix
thank you
I need to update column 'customer' in table 'db' with a name of a customer.
Currently this column is empty from data.
I do following , but it doesn't work.
UPDATE db SET Customer = 'Microsoft'
WHERE Customer='';
Where is the problem.
How do I paste to multiple cells in access 2007? Basically I want to copy one
cell in the dataview and then highlight maybe 10 cells and copy the info from the one cell into the highlighted 10 cells at one time. I had no problem doing this with 2003 but in 2007 this seems to be an issue. Any help is appreciated!
I want to import only the information from an excel sheet like the one below where the answers for Q1 and Q2 are No and Yes respectively.
http://www.dfwls1.com/hosting/albums/userpics/11225/excel.jpg
I'm using this code below that is working but importing all cells:
Private Sub Command31_Click()
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "CustomerRecords", "C:Documents and SettingsmstaffordDesktopCustomerRecords.xls", True, "C1:I20"
End Sub
What do I need to add?
Right, So i've got a table which has records for the Top 40 Music Charts.
I want to be able to find out the averages for each song taken from it's positions in the chart over the weeks:
http://www.playfm.orcon.net.nz/Chart4.GIF
So essentially I get something that tells me
[Lenny Kravits] [American Woman] [24.5]
[Silverchair] [Ana's Song (Open Fire)] [41.1]
and so on...
Any ideas?
Cheers guys,
Alex.
Hi,
I currently have a table of contacts. For the most part, each record has a 'company name' field, an 'address' field, etc. Unfortunately, because I imported this data from a text file (I ran a VBA code which extracted each record from the text file and put them into the table) and because not all the contacts have the same number of fields, the fields don't all line up.
How can I traverse through the entire table and shift the cells so that everything lines up properly (i.e. - all the addresses are under one column and all the fax numbers are under another)? If I have to write some sort of procedure to do this, what approach should I take?
Thanks!
Hello,
how do you connect fields from one table to another? what im trying to do is to connect two fields from product! product id and order details! product id. the one in the order details table should equal whatever i input in the product table.
I need to count the number of cells that match a certain number like >0 and <60. Thanks!,
Mike :confused:
hi all
i have created a database but didnt insert any default value at the begining and now i have blank cells in the table rather than £0.00
how would i go about querying the blank cells and then populating them with 0.00
thanks in advance
chris
This is a form used to enter in the hours worked for each employee for any particular job. What I'm trying to do, is show a total for all the hours worked for the job in a text box under the list. The cells that I want to total up are named "StartTime" and "EndTime," and they come from "tblRelEventEmployee." The form shown here is "frmEvent."
Each job can have anywhere from 1 - 30 employees, and I need to be able to basically have a calculation that does EndTime-StartTime for each employee, and then add up the total for each to give me a grand total of hours worked for the job.
I have a crosstab query which shows me something like:
Activity Quarter1 Quarter2 Total
Call 2 1 3
Visit 3 1 4
Home 4 2 6
and I want to add another row in the query itself or a new query to SUM the Visit and Home.. Like for example:
Visit+Home 7 3 10
Is it possible to do that?
Hi All,
Yes ok DDE may well be dead but it works for me so why change it? Basically I wondered what the commands where to format Excell cells. For example if I stamp a cell with todays date:
DDEPoke intChan1, "R3C2", Left(Now(), 10)
How would I say shade that cell black, change the lettering to white or underline or bold that cell?
Any help most appreciated.
Regards,
Dalien51