Worksheet Access Log

Aug 26, 2009

I want to create a log of everyone who opens a particular workbook. I'm using Excel 2003. I found this macro, and created a worksheet called "Log", but I've opened the workbook several times and nothing appears on the Log sheet.

ADVERTISEMENT

Access Another Workbooks Worksheet

Dec 28, 2007

In another thread I was able to access another workbook with a listbox.

I'm now trying to do the same thing with a command button. Here's what I have so far without success.

Private Sub CommandButton4_Click()
With Range("'Estimate_Database.xls'!ELECTRICAL")
Worksheets(.Parent.Name).Activate
.Select
End With

Electrical.Hide
DataSheets.Hide
End Sub

The file path being C/Documents/Estimate_Database

View 5 Replies View Related

Exporting Worksheet To Access Table

Oct 7, 2009

I have a script that exports an excel worksheet into an access db table and thsi is working fine. However i want to define a specific worksheet where the data is being exported from within the code but im having trouble doing, its using just a range at present.

View 2 Replies View Related

Macro To Access Worksheet Via Popup Box

Jan 19, 2010

I have a macro below that gives me a popup box that lets me type in the new sheet i want to access. etc(sheet1.xls) It give me 2 boxes one i must type the old sheet i currently access etc(sheet0.xls) and the next is the new sheet i will now be accesssing.

Is there a way to change this so i only get one box that will let me type in the new sheet i want to access?

View 11 Replies View Related

Best Way To Populate Worksheet From Access Database

Mar 8, 2008

I have an excel worksheet that I need to populate with a few thousand data points from MS Access. Currently I do this through vba code somewhat similar to this:

rst2.Open myQueryString, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
Do Until rst2.EOF
wsht1.Cells(1+counter, 1) = rst2!val
rst2.MoveNext
counter = counter +1
Loop
rst2.Close


Now this method works, but it is pretty slow to load. I remember reading as a general excel optimization technique you should avoid using long loops accessing cells on an individual basis.

Is there a better way for me to dump large clumps of Access data into Excel, instead of populating it cell by cell?

View 9 Replies View Related

Restrict User Access To Worksheet

Dec 6, 2007

I am creating a spreadsheet for work - I have a column that is all the formulas projected sales minus actual sales.

I need to lock this column so that no one BUT MYSELF can change the codes. In other words "idiot proof" the worksheet. there are only 18 sections that I want anyone else to be able to type in.

is there a way to do this without adding users? half the ppl have personal emails and half have company emails.

View 5 Replies View Related

MS Access Data Import Into An Excel Worksheet.

Oct 7, 2007

I currently have a userform, and on commandbutton_click, it performs
the following code that adds a column in a access table to a combobox(cbList)


Private Sub CmdName_Click()
Dim rstName As ADODB.Recordset
Dim strClientDatabase As String, strConnectionString As String
strClientDatabase = ActiveWorkbook.Path & "9001.mdb"......

After its listed on the combo box, upon commandbutton_click on another button, i wish to extract a particular record, based on the selection made on the combobox(cbList). This is where im having problems caused i have no idea how to do so. I just want it to extract the particular record row, based on cbList, and insert it into range A100:D100 in a particular worksheet. It is then updated and added to a listbox which ive already done the coding for, and with another button click it would add the details in the listbox into the appropriate location i wish to.. The only place im stuck is with extracting the data from access into a A100:D100 range in any worksheet. what ive come up with, but is incomplete is :


Private Sub CmdImport_Click()
Dim adoRS As ADODB.Recordset
Dim strSQL As String
Dim strClientDatabase As String, strConnectionString As String
strClientDatabase = ActiveWorkbook.Path & "9001mdb"
.........

View 9 Replies View Related

Access/read Data From A Hidden WorkSheet

May 19, 2006

I'm trying to access/read data from a hidden WorkSheet in Excel 2003 using:

Application.Worksheets("Hidden Sheet").Activate
With ActiveSheet
**** Data ****
End With

The accessed **** Data **** refers to another ('unhidden') WorkSheet. How can I read the data without making the WorkSheet visible to the User?

View 4 Replies View Related

Access Worksheet Controls Properties & Methods

Aug 12, 2006

I am trying to implement code that can handle a dynamic number of objects, specifically check boxes.

I know of eval( ) in perl that allows one to dynamically create command lines. For instance, the following code would change all of the Checkbox values to 1. The syntax may not be correct but the idea is solid.

Do While i < num_checkboxes
eval("Checkbox" .i. ".Value=1)
Loop

Does this funtion also exist in excel?

View 6 Replies View Related

Populate Values In Excel Worksheet From Access Table?

Mar 26, 2014

I want to write down the code that will populate values in "Sheet1" from the Access table. The column headers shows "Envelope types", "Envelope Size" fields from the Access table and each cell should store sum(volume) for each month in the table.

As I can't upload access table in the attachment so I have exported data into Workbook named "tblmain" as attached. But in actual tblmain is Access table. consider it an access table.

wrting code that will fetch data from access table and store in all the cells of the table in "Sheet1" of Elevate workbook.

View 7 Replies View Related

Entering Formula- Data Imported From Access And Saved As A Worksheet

Dec 7, 2007

I have a workbook with just one worksheet. It's just a list of data imported from Access and saved as a worksheet. When I try to enter a formula ie '=4+4' it goes in as text and will not calculate. I have tried various formatting to no avail. I added a second worksheet to see what happens and this works fine. (XP home, Excel 2003).

View 12 Replies View Related

Show Worksheets Tabs On Current Worksheet For Easy Access

Jan 12, 2009

You could use:

If ActiveWorkbook.Sheets.Count < 17 Then
Application.CommandBars("Workbook tabs").ShowPopup
Else
Application.CommandBars("Workbook tabs").Controls("more Sheets...").Execute
End If

The code doesn't do anything on my excel workbook which currently has like 20 workbooks, and it grows daily by like 4 or 5 worksheets.

View 9 Replies View Related

Excel 2003 :: Grant Access To Selective Columns On Shared Worksheet

Nov 15, 2012

I'd like to grant access to selective columns on a shared worksheet.

For example:
Columns A to D should be edited by only me
Columns E to G can be edited by anyone
Column H should only be edited by someone else

The worksheet has to be shared and has been shared by me. I know how to protect & hide locked cells etc but the problem I'm running into is this...

After locking & password protecting say columns A to D and then sharing the worksheet, I am unable to edit my own columns (columns A to D) without first entering the password.

However in order to enter the password I need to unprotect the sheet. And in order to unprotect the sheet, I need to unshare it!

Is there any way around this or perhaps a completely different way of approaching it?

View 3 Replies View Related

Programmatic Access To Trust Access To Visual Basic Editor

Jan 10, 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)?

View 4 Replies View Related

Access-Like Report Without Utilizing Access

Sep 12, 2006

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

View 4 Replies View Related

Copy Worksheet In Workbook With All Formulas On New Worksheet Referencing Previous Worksheet

Apr 21, 2012

I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.

How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?

View 1 Replies View Related

Excel Or Access?

May 14, 2007

I need a front worksheet with either buttons or tick boxes that will list different options for a machine

Once a tick or push button is activated a hidden block of text related to that specific tick box needs to be selected and placed onto a final print out sheet (allocation)

when futher boxes have been ticked I would like all the information blocks to build up on the final print out sheet.

View 10 Replies View Related

Access Query

Dec 8, 2008

I'm trying to build a query which matches two tables which say has a number like A#### , I want the query to give me the A number's which do not belong in the second table but which do in the first table. i believe i need an SQL query to do this?

View 2 Replies View Related

Sql Import To Access

Dec 20, 2008

The size of the table I'm importing will change, so i would like for the code to not matter on size. Also its, gonna be large too. The sheet will always be the same and the column headers will match for excel and access.

View 2 Replies View Related

Using VBA To Access Information In SQL

May 29, 2009

I need to compare data on a spreadsheet to data that resides in SQL database. I have never done this before so before I start I wanted to ask a general question, what is the cleanest, simplest, way to go about this. I read that ADO is a good way to access SQL. I understand that ADO is one of the main components of MS universal data access specifications so it sounds like the right approach (and that ADO is replacing DAO).

In terms of application, all I want to do for now is pull data from SQL and write it to a worksheet in the Excel workbook. Other macro's will then operate on this data. Later I will want to write data back to SQL but I want to focus on the "get" part first. Are the statements SELECT; INSERT; UPDATE; DELETE examples of ADO coding?

Conceptually, the SQL team provide nightly tables of data; ADO retrieves some of that data by selecting it; The selected data is written to a worksheet within the workbook. Is this a good approach?

View 3 Replies View Related

Formula Or Access

Jun 15, 2006

I have a very unuserfriendly report that looks like this

State - ype - County/City - Invoice # - Ref # - Amount
UT - Tax Billed - Salt Lake - 073714 - 238025 - $10.82
UT - Taxable Sales - Utah - 073714 - 238025 - $983.75
UT - Tax Billed - Slt Lk Cnty 073714 - 238025 - 4.92
UT- taxable Sales Slt Lk Cnty 073714 - 238025 - 983.75
UT - Tax billed - Utah - 073714 - 238025 - 46.73

For sales tax reporting we need to know the state, city, county district
amounts. This would be simple if we could just sort by county/city, however
we cannot because the state tax piece only only shows the word "UTAH" it does
not give a city or county name. So we must sort by invoice #. This would be
ok if we only had a few invoices in each city such as Salt Lake but we have
have 40 or 50 invoice numbers in salt lake.

I need to find a way to keep all of the tax pieces that belong together, and
subtotal by city/county. I thought if I had a formula that could change the
state name "UTAH" to reflect the city for that invoice. This is the case for
all states. Each state is 10,000 or more lines. I need to somehow grouping
the information I need. I used the pivot table approach. However, since I
must sort by invoice I still have 10,000 lines. I want to sort by invoice to
get all the pieces then sort by county city. Any ideas of how I can do this?
something in Access perhaps? a formual in excel perhaps?

View 9 Replies View Related

Getting All Data From Access

Nov 14, 2006

i have an access database and is using excel to get the required data from the access database. I am basing on department to filter out the required data into excel. Below is the VBA code i use

deptClause = Worksheets("Records").range("C1").Value
If Not deptClause = "All" Or deptClause = "" Then
requestClause = " WHERE Department='" & deptClause & "'"
End If

Queryline = "Select * from FailureQuery" & requestClause

the problem is i got one selection "All" in worksheet "Records" in cell "C1" and i cannot get this function to display all the data from the access. however when i choose a particular department it will show fine.

View 9 Replies View Related

Access Vs Excel As A DB

Dec 18, 2007

There are many examples and aspects to compare these 2 products but I just want to point one little difference which is quite crucial and interesting.

Generally if you use small amount of data - 1 Worksheet / 5000 rows / 20 columns you can use Excel without bothering about the execution time, queries and work fast and convenient with it.

The point on Excel is that in 1 Column/Row you can differently Format the data(cells). For example - format as Number or Hour the cells in Column B depending on the data in other columns. That saves you from making 2 Columns - one for Numbers and another one for Hours. This helps you to save 1 of the columns when the data structure in other column is the same.

In Access (and generally all SQL DBs) this is not possible.

View 9 Replies View Related

Converting From Access

Jun 2, 2008

I am trying to convert An Access macro/function programs to Excel and I am having trouble processing the following in Excel:

Set rsbuildinforce = CurrentDb.OpenRecordset(InfTable)

(InfTable) is defined as an Access linked table name. The values of columns in rsbuildinforce drive the logic of the macro/function

The object of the program is to read in data from excel, do some manipulations, reformatting, etc... and output .csv files.

View 9 Replies View Related

Formatting Using Access VBA

Sep 24, 2008

I have the following code set up and every works except for PaperSize and Margins. Is there a standard VBA code reference available somewhere that addresses Access to Excel operations? Thanks

With xlApp.ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = "$A:$C"
.LeftHeader = "XYZ Financial Wonks, Inc."
.CenterHeader = ""
.RightHeader = "COMPANY CONFIDENTIAL"
.LeftFooter = "&Z&F"
.CenterFooter = ""
.RightFooter = "&P of &N"
' .LeftMargin = = xlSheet.InchesToPoints(0.25)
' .RightMargin = xlSheet.InchesToPoints(0.25)
' .TopMargin = Application.InchesToPoints(0.25)
' .BottomMargin = Application.InchesToPoints(0.25)
' .HeaderMargin = Application.InchesToPoints(0.25)
' .FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False....................

View 9 Replies View Related

Using Access 2007

Jan 1, 2010

I have an Excel spreadsheet that I have developed into an over the top flight calculator/scheduling tool. I am using Excel because developing this prduct into a stand-alone program would be absolutely murder to get approved for use on my network.

Since Excel and Access has already been approved, I began coding this project there.

Now that my calculator functions are nearly complete, I am beginning to develop a database of airfields and other misc. flight data that I and others on my network can see and manipulate. To make my calculator funtion at 100% effectiveness, I will need to use VBA to open the Access DB and pull out specific data. I will also need it to open and display some Access pop-up forms that I have just developed. Does anyone know what code I would use for excel to display and retrieve Access' information?

View 9 Replies View Related

Getting A Value From A MS Access Database

Jun 9, 2003

I was wondering if there was any code that would 'lookup' a value in a MS Access database in the same way that a Vlookup formula looks up values in tables in Excel.

Even better, if there was a fuction already written to do this.

View 9 Replies View Related

Exporting To Access

Mar 6, 2006

to write a macro to export 3 columns of information from excel into a table in access.

View 8 Replies View Related

Disallow Access To Add-ins

Jun 7, 2006

I am trying help protect a project from all my colleagues that have a version of password breaker that is actually an add-in. I have already managed to disable or grey-out all of the other commandbar options that I don't want them to have access to during their use of the model but can't figure out how to disallow them from accessing the "Add-ins" option from the "Tools" menu. I can, of course, disable the entire tools menu but don't want to do that yet.

View 9 Replies View Related

Cannot Access UserForms In Add-in

Jun 29, 2006

I would really like to be able to have a function in a workbook that can run methods (for example Show or Hide) on a UserForm inside an add-in like this:

Public Sub Test_Addin()
frmTest.Show False
End Sub

where frmTest is a UserForm inside the addin. After the add-in has been added as a reference, I am able to access all of its functions/subs in this way. When I try to access a form inside the add-in from outside the add-in, I simply get an object not defined error. Looking more closely, if I type in "eRFTEAddIn." (which is the name of the add-in) and look at the possible options, the forms do not show up...although the modules and sheets do.

View 6 Replies View Related

Where To Access Ozgrid Add In

Apr 13, 2007

I have installed the free version of Ozgrid.xla, but do not see where to access it.

View 9 Replies View Related

Access From Different Logins

May 5, 2007

I have created few excel files from "X" login account and when i try to open it from the "Y" login account, it opens it in the read only mode. do you know how to access it from Y account in the edit mode or the write mode.

View 2 Replies View Related

Output Userform To Access?

Jul 21, 2010

I have a userform that employees use to enter production data every day. Originally I had this storing the data in excel, but now I would like it to put the data in tables in access.

Here is some code I am trying, but it's giving me an error: "Run-time Error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."

VB:
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cnt As New ADODB.Connection

[Code].....

View 3 Replies View Related

VBA - Transferring Data To Access

Aug 23, 2012

In VBA Excel transferring data from Excel to Access.

I want to know how to increment the [ID] for each value of the range. [ID] is the Primary Key.

I have tried "NULL".

I have tried adding "n=n+1" after the "for each" and "thisSQL = "INSERT INTO... VALUES (" & n & ",..."

VB:

For Each value In Range("D5:D" & Cells(Rows.Count, "D").End(xlUp).Row)
accDateSub = Cells(value.Row, 1)
accDescSub = Cells(value.Row, 2)
accSub = Cells(value.Row, 3)
thisSQL = "INSERT INTO [Table1] ([ID], [Date], [Desc], [Data]) VALUES ("[U]What goes here?[/U] ", #" & Format(CDate(accDate), "MM/DD/YY") & "#, '" & accDesc & "', " & accData & ");"
conn.Execute CommandText:=thisSQL
Next

View 1 Replies View Related

How To Define Dictionary To Access By Key

Jul 9, 2014

I have dictionary defined as series of keys and let's say two values:

name1,val1A,val1B
name2,val2A,val2B
...and so on

I would like to define named range from "name" column allowing user to select desired name from combo on another sheet. This is easy

But after that I would like to get val1 and val2 for selected name and show them with some calculation; For example to construct two columns like this: <nameX_selected_from_combo>, (<val1X>+<val2X>)/2

All the problem is how to select values from the same row as name selected in range.

View 2 Replies View Related

How To Access A Share Workbook

Jun 12, 2014

I need to know that how can we share excel workbook. Is it possible over LAN? Also how many users can access the shared workbook. If 50 users are accessing shared workbook and each one working on different workbook. Is this scenario feasible? Will there be any issue if 50 users working on 50 different worksheets of same workbook?

View 2 Replies View Related

Connect To Access Database

Nov 6, 2007

I was trying to move data from excel to access database in VBA. not sure if this has been done before.

What i have got at the moment is that there are some data in excel spreadsheet that i can dump into the table in the access database. My problem here is i need to be able to open the database first, set up connection, and then perform SQL insert query command.

So in the worksheet, i have a button with the following codes in it
so far i could just manage to open the database as follows

View 13 Replies View Related

Using VBA To Get Data From Access Into Userform

Feb 18, 2014

I'm currently working on something that requires me to use an access database with an excel userform.

I have a team of around 50 people who will be making outbound phone calls and the data for these calls is stored on an access db. The people calling don't have MS Access so I've created a userform for these guys in Excel, which they have on their computers, and I want them to be able to click the button "Get Next" on the form and up pops the next customers information to call.

I have a userform with some text boxes where I want the info to appear on click.

I have customers name, their mobile handset type which the ordered recently and their phone number.

So I've started off with the following which seems OK:

[Code] .......

What to use next.......DLookup didn't quite work......

I'm also wondering if I'd need something to send to access to basically say that customer 001 has been pulled through already and to move onto the next because there will be 50 people using this (max) at one time.

View 4 Replies View Related

Editing Cells From Access

Oct 3, 2008

I have some VBA code in an Access DB that opens an Excel spreadsheet at a specific workbook, and then performs some formatting on the sheet, before saving the changes and closing the workbook.

The first time i run the sub it works perfectly. If i then try and run it again i get a run-time error 91 message (Object variable or With Block variable not set). This happens every subsequent time i try and run the code until i manually reset.

The error message is always at the same point. Here's the
I've put ***ERROR MESSAGE HERE*** at the point in the code the error seems to be occuring

View 2 Replies View Related

Add Data To Access Database

Oct 20, 2008

Is there a way for an excel macro to open a access database & import a txt file straight to access (without importing to excel first) & then executing some other code e.g. copy query results(which i already have code for)

View 14 Replies View Related

Export Access, Or Reverse.

Jan 19, 2009

Excel has a DATA - IMPORT EXTERNAL DATA - NEW DATABASE QUERY Function built into the toolbar. I need to lookup a table in access **TableTest**. Find the Record with a **Door_Number** Equal to an input I change everytime. Then I need to lookup a table in access **TableTest2**.

Find a Record with a **Door_Number** Equal to an input I change everytime And then paste the records in a line going DOWNWARDS not Right to Left. To summarise. Hit button, Type Key1/2 input, find record(s) paste into excel. Im afraid providing a sample is gonna be a little hard on this one, my files are HUGE

View 4 Replies View Related

ADVERTISEMENT