Multiple tables output to multiple sheets in single excel workbook

Hi all,

Been a while since I've done any access dev, and I can't for the life of me 
remember how to do this. I have 3 tables I need to export to Excel, 
preferably in one workbook as separate sheets. Using OutputTo simply 
overwrite the file, and I seem to recall using transfertext to accomplish 
this, but it says the file is read-only after the first table is output. Do 
I need to create an export spec? If so, how do I go about this? Or is this 
simply not going to happen without using the Office library reference and 
all that jazz?

TIA,

SusanV


0
SusanV
5/7/2007 3:55:36 PM
access 16762 articles. 3 followers. Follow

6 Replies
1822 Views

Similar Articles

[PageSpeed] 13

Thanks Tom - I'll give it a go!


"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message 
news:3D250271-2C82-46ED-B16B-1356B733F785@microsoft.com...
> Hi Susan,
>
> Correct. Using the OutputTo method will replace an existing spreadsheet. 
> Try
> using the TransferSpreadsheet method instead (DoCmd.TransferSpreadsheet).
>
>
> Tom Wickerath
> Microsoft Access MVP
> https://mvp.support.microsoft.com/profile/Tom
> http://www.access.qbuilt.com/html/expert_contributors.html
> __________________________________________
>
> "SusanV" wrote:
>
>> Hi all,
>>
>> Been a while since I've done any access dev, and I can't for the life of 
>> me
>> remember how to do this. I have 3 tables I need to export to Excel,
>> preferably in one workbook as separate sheets. Using OutputTo simply
>> overwrite the file, and I seem to recall using transfertext to accomplish
>> this, but it says the file is read-only after the first table is output. 
>> Do
>> I need to create an export spec? If so, how do I go about this? Or is 
>> this
>> simply not going to happen without using the Office library reference and
>> all that jazz?
>>
>> TIA,
>>
>> SusanV 


0
SusanV
5/7/2007 4:12:43 PM
Works wonderfully - thank you - you saved me quite a bit of time and 
headache!

One more question if I may - is there any way to specify the name of the 
worksheet? I see it's taking the tablename automagically, but the table 
names are not exactly intuitive for the recipients of the output data...

If that can't be specified, what's the VBA code to rename a table? I'm 
thinking I can rename the table prior, export, then rename it back.

Thanks TONS,
Susan


"SusanV" <svanallen@nospam-mvps.org> wrote in message 
news:O7KEMKMkHHA.588@TK2MSFTNGP06.phx.gbl...
> Thanks Tom - I'll give it a go!
>
>
> "Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message 
> news:3D250271-2C82-46ED-B16B-1356B733F785@microsoft.com...
>> Hi Susan,
>>
>> Correct. Using the OutputTo method will replace an existing spreadsheet. 
>> Try
>> using the TransferSpreadsheet method instead (DoCmd.TransferSpreadsheet).
>>
>>
>> Tom Wickerath
>> Microsoft Access MVP
>> https://mvp.support.microsoft.com/profile/Tom
>> http://www.access.qbuilt.com/html/expert_contributors.html
>> __________________________________________
>>
>> "SusanV" wrote:
>>
>>> Hi all,
>>>
>>> Been a while since I've done any access dev, and I can't for the life of 
>>> me
>>> remember how to do this. I have 3 tables I need to export to Excel,
>>> preferably in one workbook as separate sheets. Using OutputTo simply
>>> overwrite the file, and I seem to recall using transfertext to 
>>> accomplish
>>> this, but it says the file is read-only after the first table is output. 
>>> Do
>>> I need to create an export spec? If so, how do I go about this? Or is 
>>> this
>>> simply not going to happen without using the Office library reference 
>>> and
>>> all that jazz?
>>>
>>> TIA,
>>>
>>> SusanV
>
> 


0
SusanV
5/7/2007 4:22:42 PM
Hi Susan,

You're welcome TONS!   <smile>

> I'm thinking I can rename the table prior, export, then rename it back.

It would be better to use VBA code to rename the Excel file, instead of 
messing around with trying to rename a table. However, even that should not 
be necessary. Here is an example that I use, to save the Excel file to the 
same folder that the .mdb file is located in:

Private Sub cmdExportToExcel_Click()
On Error GoTo ProcError

Dim strPath As String
strPath = CurrentProject.Path

  DoCmd.TransferSpreadsheet _
            TransferType:=acExport, TableName:="qryMovieSelections", _
            FileName:=strPath & "\MovieSelections.xls", HasFieldNames:=True
  
  MsgBox "The selected movies have been exported to the " _
          & "file MovieSelections.xls" & vbCrLf & "in the folder:" _
          & vbCrLf & strPath, vbInformation, "Export Complete..."

ExitProc:
  Exit Sub

ProcError:
  MsgBox "Error " & Err.Number & ": " & Err.Description, , _
         "Error in cmdExportToExcel_Click event procedure..."
  Resume ExitProc
End Sub


In this case, I am hard-coding the name of the Excel file as "Movie 
Selections.xls". However, you could modify this code by adding a SELECT Case 
.....END SELECT  construct to assign the correct name, based on the name of 
TableName parameter.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"SusanV" wrote:

> Works wonderfully - thank you - you saved me quite a bit of time and 
> headache!
> 
> One more question if I may - is there any way to specify the name of the 
> worksheet? I see it's taking the tablename automagically, but the table 
> names are not exactly intuitive for the recipients of the output data...
> 
> If that can't be specified, what's the VBA code to rename a table? I'm 
> thinking I can rename the table prior, export, then rename it back.
> 
> Thanks TONS,
> Susan
0
Utf
5/7/2007 4:49:01 PM
Hmmm, well, since I posted my last response I've been doing some longer-term 
thinking, and since the tables I'm exporting are temporary tables based on 
complex SQL queries of multiple joined tables, I'm gunking up the database 
with lots of whitespace after each create/delete of the temporary table - no 
data is modified, they are simple select ... into queries. So now I'm 
thinking my best bet is probably to create a new querydef (with a name the 
file recipients will be able to comprehend in the worksheet) and export 
that, then delete it,  rather than creating/deleting the temp table. I know 
I *could* simply save the queries, but I have users who are "fiddlers" and 
I've learned not to put anything in the frontend that they can mess with 
<grin>

Any reason you know of that this would be a bad idea?

SusanV

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message 
news:9E8AA693-8D37-4942-9605-8BF3020F0497@microsoft.com...
> Hi Susan,
>
> You're welcome TONS!   <smile>
>
>> I'm thinking I can rename the table prior, export, then rename it back.
>
> It would be better to use VBA code to rename the Excel file, instead of
> messing around with trying to rename a table. However, even that should 
> not
> be necessary. Here is an example that I use, to save the Excel file to the
> same folder that the .mdb file is located in:
>
> Private Sub cmdExportToExcel_Click()
> On Error GoTo ProcError
>
> Dim strPath As String
> strPath = CurrentProject.Path
>
>  DoCmd.TransferSpreadsheet _
>            TransferType:=acExport, TableName:="qryMovieSelections", _
>            FileName:=strPath & "\MovieSelections.xls", HasFieldNames:=True
>
>  MsgBox "The selected movies have been exported to the " _
>          & "file MovieSelections.xls" & vbCrLf & "in the folder:" _
>          & vbCrLf & strPath, vbInformation, "Export Complete..."
>
> ExitProc:
>  Exit Sub
>
> ProcError:
>  MsgBox "Error " & Err.Number & ": " & Err.Description, , _
>         "Error in cmdExportToExcel_Click event procedure..."
>  Resume ExitProc
> End Sub
>
>
> In this case, I am hard-coding the name of the Excel file as "Movie
> Selections.xls". However, you could modify this code by adding a SELECT 
> Case
> ....END SELECT  construct to assign the correct name, based on the name of
> TableName parameter.
>
>
> Tom Wickerath
> Microsoft Access MVP
> https://mvp.support.microsoft.com/profile/Tom
> http://www.access.qbuilt.com/html/expert_contributors.html
> __________________________________________
>
> "SusanV" wrote:
>
>> Works wonderfully - thank you - you saved me quite a bit of time and
>> headache!
>>
>> One more question if I may - is there any way to specify the name of the
>> worksheet? I see it's taking the tablename automagically, but the table
>> names are not exactly intuitive for the recipients of the output data...
>>
>> If that can't be specified, what's the VBA code to rename a table? I'm
>> thinking I can rename the table prior, export, then rename it back.
>>
>> Thanks TONS,
>> Susan 


0
SusanV
5/7/2007 4:59:56 PM
Are your fiddlers smart enough to re-enable the Shift key, if you disable it? 
If not, you can set the startup options (Tools > Startup...) by removing all 
check marks, including Display Database Window and Use Access Special Keys. 
Make sure you provide a switchboard form for navigating, and set it as the 
startup form. You can either do this using Tools > Startup, or via an 
Autoexec macro. I prefer using the Autoexec macro method (one of the only 
times that I will use a macro of any type). Then use code to disable the 
Shift key, and distribute in compiled .mde form only. Your users will not be 
able to change any forms, reports or modules in the compiled .mde form.

Note: A VBA break point will not work if you have Use Access Special Keys 
disabled.

> Any reason you know of that this would be a bad idea?

It could render a signed database as unsigned, in case you happen to use 
this "feature" to avoid having to deal with macro security each time you open 
the database.

Here's a couple of alternate ideas for you to consider:

1.) Export the query directly, without creating the temporary table first 
(if possible)
or
2.) Use a temporary linked database, as shown in this example which you can 
download from my web site:
     http://home.comcast.net/~tutorme2/samples/tmpwrkdb.zip

Note: For Access 2007, you will need to Trust the user's temporary folder

     C:\Documents and Settings\{NTUserID}\Local Settings\Temp

where {NTUserID} is the user's NTUserID used for logging in to their computer.

    Dealing with the Trust Center (Access 2007 only)
    http://www.access.qbuilt.com/html/trust_center.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"SusanV" wrote:

> Hmmm, well, since I posted my last response I've been doing some longer-term 
> thinking, and since the tables I'm exporting are temporary tables based on 
> complex SQL queries of multiple joined tables, I'm gunking up the database 
> with lots of whitespace after each create/delete of the temporary table - no 
> data is modified, they are simple select ... into queries. So now I'm 
> thinking my best bet is probably to create a new querydef (with a name the 
> file recipients will be able to comprehend in the worksheet) and export 
> that, then delete it,  rather than creating/deleting the temp table. I know 
> I *could* simply save the queries, but I have users who are "fiddlers" and 
> I've learned not to put anything in the frontend that they can mess with 
> <grin>
> 
> Any reason you know of that this would be a bad idea?
> 
> SusanV
0
Utf
5/7/2007 7:54:01 PM
The database is split, and on the front-end I have the startup options set 
to hide the database window and open a form that acts as a custom 
switchboard, if you will. Then I make and distribute an MDE, so they can't 
mess with anything (other than that they can make their own queries). I've 
found in the past that they will be too lazy to make a new query, and simply 
modify an existing one, then save the changes when they close it, breaking 
reports and functions which call those queries. <sigh> So now the reports 
are based on SQL statements, rather than stored queries, either hard-coded 
into properties or created on-the-fly via VBA.

Oh, and I stay away from macros - too many problems debugging, too many 
limitations <smile>

By the way, the DAO create querydef is working wonderfully, and is not 
bloating the database as I had expected.
Thanks for your input, it's much appreciated as always!

SusanV


"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message 
news:0425B86C-7703-417D-AA70-916B6841D34D@microsoft.com...
> Are your fiddlers smart enough to re-enable the Shift key, if you disable 
> it?
> If not, you can set the startup options (Tools > Startup...) by removing 
> all
> check marks, including Display Database Window and Use Access Special 
> Keys.
> Make sure you provide a switchboard form for navigating, and set it as the
> startup form. You can either do this using Tools > Startup, or via an
> Autoexec macro. I prefer using the Autoexec macro method (one of the only
> times that I will use a macro of any type). Then use code to disable the
> Shift key, and distribute in compiled .mde form only. Your users will not 
> be
> able to change any forms, reports or modules in the compiled .mde form.
>
> Note: A VBA break point will not work if you have Use Access Special Keys
> disabled.
>
>> Any reason you know of that this would be a bad idea?
>
> It could render a signed database as unsigned, in case you happen to use
> this "feature" to avoid having to deal with macro security each time you 
> open
> the database.
>
> Here's a couple of alternate ideas for you to consider:
>
> 1.) Export the query directly, without creating the temporary table first
> (if possible)
> or
> 2.) Use a temporary linked database, as shown in this example which you 
> can
> download from my web site:
>     http://home.comcast.net/~tutorme2/samples/tmpwrkdb.zip
>
> Note: For Access 2007, you will need to Trust the user's temporary folder
>
>     C:\Documents and Settings\{NTUserID}\Local Settings\Temp
>
> where {NTUserID} is the user's NTUserID used for logging in to their 
> computer.
>
>    Dealing with the Trust Center (Access 2007 only)
>    http://www.access.qbuilt.com/html/trust_center.html
>
>
> Tom Wickerath
> Microsoft Access MVP
> https://mvp.support.microsoft.com/profile/Tom
> http://www.access.qbuilt.com/html/expert_contributors.html
> __________________________________________
>
> "SusanV" wrote:
>
>> Hmmm, well, since I posted my last response I've been doing some 
>> longer-term
>> thinking, and since the tables I'm exporting are temporary tables based 
>> on
>> complex SQL queries of multiple joined tables, I'm gunking up the 
>> database
>> with lots of whitespace after each create/delete of the temporary table - 
>> no
>> data is modified, they are simple select ... into queries. So now I'm
>> thinking my best bet is probably to create a new querydef (with a name 
>> the
>> file recipients will be able to comprehend in the worksheet) and export
>> that, then delete it,  rather than creating/deleting the temp table. I 
>> know
>> I *could* simply save the queries, but I have users who are "fiddlers" 
>> and
>> I've learned not to put anything in the frontend that they can mess with
>> <grin>
>>
>> Any reason you know of that this would be a bad idea?
>>
>> SusanV 


0
SusanV
5/8/2007 11:26:32 AM
Reply:

Similar Artilces:

Removing multiple hyperlinks
I've got data copied into my spreadsheet that includes hyperlinks. Ther are thousands of these in a column. I can remove individually by right-clicking and selecting Hyperlink an Remove. I can then arrow down and do a control Y, cell by cell, What I want to do is highlight the column and be able tp remove th hyperlinks all at once. I've exhausted myself peering through knowlegebases and groups, bu cannot find any way to make this work. Thanks for any input you can provide -- Message posted from http://www.ExcelForum.com Hi one way: Use the following macro to remove hyperlinks...

Create a list with multiple criteria
I have a data base with 1,000's of addresses w/zipcodes (Sheet 1 columns A to E, E being zipcodes). After setting a base address I get all of the zipcodes within a given radius (in this case 70 zipcodes), listed in Sheet 2, Column A. Next, I use COUNTIF (in column B) to find how many addresses are in each zipcode, in this example there are 46 addresses within the 70 zipcodes. What I want to do is create a list in sheet three that will list each with the data from Sheet 1 columns A to E). Any help would be appreciated. Ronbo ...

Localized Header/Footer formatting codes in Excel 2000
Hello, I would like to set the Header/Footer parameters of an Excel worksheet from a VC++/MFC application using formatting codes, for example: PageSetup page = worksheet.GetPageSetup(); page.SetLeftHeader(_T("&D &T")); // print date and time to header The problem is that my Office 2000 is a localized version (Hungarian), and Excel doesn't seem to recognize the &T code, only the localized version, which is &I. This code works fine: page.SetLeftHeader(_T("&D &I")); I recorded a macro, and the generated VBA code also uses the non-localized vers...

Charting multiple multiples
Hopefully I can explain this in a way that will be understood. Data: Place A Place B Place C FWD Back FWD BACK FWD Back Org_1 1 2 3 2 1 3 Org_2 2 5 4 6 1 4 I would like to place the data so that FWD and Back data is stacked on top of each other in different colors, and placed in one row on a 3-D chart as Org-1 and a second row as Org_2. Any ideas? Thanks. If you arrange your data like this: Back FWD Org_1 Place A 2 1 Place B 2 3 Place C 3 1 Org_2 Place A 5 2 ...

Multiple Hyperlinks In Same Shape Doesn't Work
Can anyone tell me how I can get multiple hyperlinks in the same shape to work? I have no problem inserting the hyperlinks, but they all point to only one. I scoured these posts, and found this (http://groups.google.com/groups?q=hyperlink+group:microsoft.public.visio.*&hl=en&lr=&ie=UTF-8&oe=UTF-8&group=microsoft.public.visio.*&selm=3eea6467%241%40news.microsoft.com&rnum=2), which doesn't appear to work. Thanks in advance-- RAD Um, which version of Visio are you using? As far as I know, at least Visio 2002 and Visio 2003 support multiple hyperlinks. Can't...

a-z sort sheets
is it possible to sort worksheets a - z. i have a workbook with about 40 sheets in it and would like to sort them in a - z order. thx Dale have a look here for a way to do it. http://www.cpearson.com/excel/sortws.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Dale" <daleathome@blueyonder.co.uk> wrote in message news:K2qgf.54567$375.5653@fe3.news.blueyonder.co.uk... > is it possible to sort worksheets a...

PivotChart will not preserve series formatting for some charts when reopening workbook
I have a WB with 4 PivotCharts. When I close and reopen the WB, 2 of my PivotCharts have slightly skewed formatting. Can somone help determine why? Details: PTable 1 / PChart 1, PTable 2 / PChart 2 all based on static data in Sheet 1. PTable 3 / PChart 3, PTable 4 / PChart 4 all based on static data in Sheet 2. All PTables were created from a new PCache object. I'm using any single data cache for more than 1 PTable. When I reopen, PChart 1 and PChart 3 have the following problem: - Series order goes back to the default order. - Series colors remains in the old order. 1. I know that...

Multiple Profiles, multiple accounts, multiple machine configuring?
Is there a vb or related method of adding *both* profiles *and* email accounts to multiple users logins from the administrator account? I have a network which is a workgroup (NOT a domain), in which there are about 15 users that are replicated on each of 5 machines. I'd like to be able to add all 15 Mail Profiles, and all 15 email accounts to each of the 15 logins on these 5 machines--obviously with as little work as possible. The mail is all retrieved from the same Exchange Server, but these machines *cannot* be added to a domain (don't ask!). I'd hate to have to log into ...

Transferring Excel Spreadsheet into Access
I work in a Real Estate Brokerage Firm. We need to transfer our current excel based spreadsheet into Access. Our goal is to utilize Access for superior Database Management while maintaining Excel's calculator functions. Is that feasible and if so, how? Also can this be done in an HTML environment? Thanks. Nancy From my perspective, Excel provides strong "calculator" functions, as you describe it, while Access, as a relational database, offers strong database functionality. These represent two different tools. What about "Database Management" (whatever you mean by...

Link to CRM tables from external application
My client has a mission-critical application that was written in Microsoft Access sitting on a SQL Server database. It is basically a Quotation management system. The client wishes to install CRM and I'm investigating how to migrate the Customer data from their Quotation system to CRM. I'm pretty confident I can do this without too much trouble (having identified the relevant tables in CRM - AccountBase and ContactBase). However, I then want to link directly to the CRM tables from within the Access application. Normally I'd do this by setting up a DSN and using it to pull in l...

Excel Instances
My coworker is having a hard time getting more than one workbook to open in a single instance of Excel. It seems that no matter how we try to open the second workbook, a new instance of Excel is opened. "Ignore other applications" is not selected in Tools - Options. For kicks I tried selecting that as an option and it made no difference. Excel continued to open a new instance with every new file. Any suggestions? you COULD open workbook2 from File/Open, rather than double-clicking in windows explorer. "StephanieH" <StephanieH@discussions.microsoft.com> ...

Make Excel's "grab and drag" behave like cut and paste "values only" ?
Excel question. Is there an easy way to make Excel's "grab and drag" behave like cut and paste "values only" ? Scenario : One sheet has input data Another sheet has formula to display output data which uses the input data as its variables There is a close positional match of data on both sheets (e.g. data entered A1 on sheet 1 affects the results in A1 on sheet 2) What I would like to be able to use Excel's "grab and drag" (this is where you put the mouse cursor right on the edge of a highlighted block of data and drag it to another location...

distributing data into multiple cells
hii i have to prepare an exel sheet of some 2000 products..i have to copy d data from the net and then prepare the sheet,with 4 different columns for 4 diff types of entries..now when i copy data nad paste is as text.it all goes in a single cell in a row .how can i distribute the data l into multiple cells????????? -- jaspreet ------------------------------------------------------------------------ jaspreet's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23821 View this thread: http://www.excelforum.com/showthread.php?threadid=374772 can you show some samp...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Is there a way to sort multiple columns with a tab or something?
I have a 4 column spreadsheet. I want to be able to click the heading for each of the columns to hav them sort by that column if clicked. How do I do that -- Message posted from http://www.ExcelForum.com Hi why not used the soprt icon for this. Note: This could screw up your sorting if Excel does not recognize your database columns correctly -- Regards Frank Kabel Frankfurt, Germany > I have a 4 column spreadsheet. > > I want to be able to click the heading for each of the columns to have > them sort by that column if clicked. How do I do that? > > > --- > Mess...

Sending to multiple addresses
How do I send an email to multiple addresses having only the recipient see their own email address? Michelle wrote: > How do I send an email to multiple addresses having only > the recipient see their own email address? Put the addresses into the BCC: field. Note that many e-mail programs will decide such mails are spam though. >-----Original Message----- >How do I send an email to multiple addresses having only >the recipient see their own email address? >. Open your new email and go to tools, select recipient, when your address book opens select (highlight) the a...

Closing workbook open as CSV
I open a CSV file in Excel, Copy two columns of data, paste the data into another workbook and then I want to cloes the CSV file without getting a prompt about saving the data to the clipboard. I am using Application.ActiveWindow.Close (False) to close the file but I'm still getting the prompt to save the data. Is there another command I should use? Thanks, Sam First, I don't go through the Windows collection. I just close the workbook. Dim CSVWkbk as workbook 'other code here 'open the csv file Workbooks.Open Filename:="somepath\somename.csv&quo...

multiple selection
How can I select multiple records into a list box? I want that for append identical records (for select records) in another table. Many thanks Florin Use a subform instead of a list box. In a subform bound to a related table, you can append as many rows as applicable. It is possible (but not simple) to use an unbound multi-select list box that is populated in the Current event of the form, and executes Append, Delete, and Update query statements based on the ItemsSelected if they are changed. You will also have to find a way to respond to the user "undoing" changes. -- Al...

Exchange 2000 Distribution Lists
Hello We are running an Exchange 2000 environment in Exchange Native Mode and Windows Mixed Mode. We just converted from Exchange 5.5 Is there any way to configure multiple people to be owners or editors of the distribution lists? This was possible in 5.5, but we cannot find a means of accomplishing this in 2000 Any help would be appreciated Thank yo Jason "Jason P.S." <jason.pruden-shebaylo@investorsgroup.com> wrote: >We are running an Exchange 2000 environment in Exchange Native Mode and Windows Mixed Mode. We just converted from Exchange 5.5. > >Is there any w...

Remember selections in Table Names Lookup window (Dex Runtime)
In Microsoft Dynamics GP, the user can view details on database tables in the Table Descriptions window (Tools>>Resource Descriptions>>Tables). If the user clicks the elipsis (...) button to the right of the table name, a lookup window opens. The user needs to choose both the product, series, and view by option that he or she is working with, and the list of tables will display. At least 10 times a day I go into this window, and oftentimes I don't select the table I really want first. That means I need to do a second lookup, select the product, series, and view by aga...

enter time into file for a time sheet
how can I enter the time automatically into a cell. perhaps a macro. hilte the cell and hit your macro or enter? I want to round the time to the nearest 1/4 hour. I also want to be able to override the automatic function The quick way to enter current time is Ctrl plus the colon (date is Ctrl plus semi-colon) but this doesn't round to the nearest 15 minutes. I think there is an add-in that would do that as a worksheet function. Here is a subroutine that might do what you want: Sub RndToQtrHr() Dim currMin As Long Dim M As Long Dim plusHour As Boolean currMin = Minute(Time) plusHour = ...

Excel suddenly generating gobbledigook nonsense!
I was just working on some accounts when suddenly one of the Excel cells started filling up with gobbledigook! Kind of semi-random words that almost make meaningful sentences but do not. In English. I have never used or installed a random text generator. Could this be a virus/trojan, general cyber-bugger? I've searched and cannot find any other similar reports. Any ideas would be welcome. In the meantime I've booted into safe- mode and running Spybot S&D, just in case. BTW, I disconnected from the internet and it continued to ramble garbage into my spreadsheet. Any chance yo...

Need to have a dropdown list containing multiple rows
I am setting up an form and need to create a dropdown list that will contain 8 sets of data. Each set needs to contain 3 rows of data (name, street address & city/state/zip. The goal is to allow the user to click onto the cell, see the dropdown and select which address set to use. This would then place the data into three rows suitable for mailing. I have never attempted anything like this and am in need of assistance. Thank you. --- Message posted from http://www.ExcelForum.com/ Pat What you ask is easily done using Data Validation drop-down list and a few Vlookups but........Ex...

Combine multiple tables into one
I took over a database in which the last person created a new table for everyday worth of data (ie. Feb-21-08 is named 022108). There are over 100 tables in the single database with the same columns and table attributes, I would like to combine all of these tables into one where I have a Date_Key field in place of the table name. I figured out how to query the table names using the following: SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name; Now I j...

Multiple domains #6
Hi, Having done some research I have found that it is possible to send and receive email from multiple domains with Exchange 2003. I have a few questions that I would like cleared up if anyone can help. 1. Is it really as simple as configuring a recipient policy for the new domain and then changing the mail record for that domain to point to the proper IP address? 2. If I set up a new account in AD, and need this account to be associated with the second email domain, will the default SMTP email be for the first domain? If so, can I just edit this directly? 3. If I set the default SMTP add...