Replies

Fantastic !! Thank you. I only need one clarification. When I send it to one recipient with one attachment it works perfectly. When I add the other three recipients and one more attachment it does not work. (Format used for multiple recipients is “Call SendMail (“recipient1;Recipient2;Recipient3;Recipient4”, “Subject”; “Message”; “Attachment1;Attachment2”). Any thoughts?

I started a new workbook and cut and pasted from the old to the new. The file size came out to be around 1 meg, which is still very high considering the file is virtually empty. I am still test some code, but the size is no longer growing.

it is 5.8 meg zipped. is that to large to attach?

Thank you for the reply. I have downloaded and ran the VBA Cleaner. The file is still 20.8 megs. It consists of only 2 sheets and there are less than 100 rows and 10 columns of data on either. This file when first started was about 15k and grew to about 45k before I started writting the code. As I was writting and testing the code it rapidly grew to 20.8 meg. I know there is not any other data on the files. In fact the last time I tested the code it grew from 11meg to 20.8 meg in just a few minutes of testing. The code merely takes information from one sheet and moves it to another based on sort criteria. I am totally confused !!!!! Should I at this point just create a new file and copy all of the code into it to start fresh? Is this supposed to happen when writing and testing code?

THank You for any help

thank you, you gentlemen/ladies do a great service here !!!!!!!!!!!!!!!!!!!!!!!!!

Sorry, reply should read… …Thank You for your suggestion. What is the best way to seperate the code? SHould I just put a file on the newtwork containing the code and relate the buttons to the code in that file?

Thank You for your suggestion. What is the best way to seperate the code? SHould I just the newtwork containing the code and relate the buttons to the code in that file?

legare,
Thank You for your help. The following is the code that I actually ended with through your help. Thanks again

Dim ws As Double
ws = Worksheets.Count
MsgBox “The total number of worsheets in this workbokk is ” & ws – 2
ws = ws – 2
‘Sub ARRAY_sheetnames()
Dim wksht As Worksheet
Dim I As Long
Dim wkshtnames() ‘This is an array definition
I = 0
For Each wksht In ActiveWorkbook.Worksheets
I = I + 1
ReDim Preserve wkshtnames(1 To I)
wkshtnames(I) = wksht.Name
Next wksht

For I = 1 To ws

‘MsgBox wkshtnames(i)
Sheets(“tblTarData”).Select
Range(“N1”).Offset(I, 0).Value = wkshtnames(I)
Next I
‘End Sub

Sheets(“TAR Summary”).Select
Range(“C27″).Select
MsgBox ” = ” & wkshtnames(I – 1) & ” and ” & wkshtnames(I – 2)

Dim strFormula As String
Dim Z As Long
Z = 1
strFormula = “='” & wkshtnames(I – 1) & “‘!RC”
For Z = 2 To ws ‘lNumWkSheets
strFormula = strFormula & “+'” & wkshtnames(I – Z) & “‘!RC”
Next Z
ActiveCell.FormulaR1C1 = strFormula

Steve, managed to figure out the syntax as follows:
ActiveCell.FormulaR1C1 = “='” & wkshtnames(i – 1) & “‘!RC+'” & wkshtnames(i – 2) & “‘!RC+'” _
& wkshtnames(i – 3) & “‘!RC+'” & wkshtnames(i – 4) & “‘!RC+'” _
& wkshtnames(i – 5) & “‘!RC+'” & wkshtnames(i – 6) & “‘!RC”

The problem as said in the previous… This assumes that there will always be 6 sheets to be added together. I need to make the number of joins a variable as well. Not sure how to do this.

Thank You

Steve, thanks for the reply, please explain the loop in more detail since I just realized that at any particular time I run this I don’t know how many spreadsheets there are going to be so I need to build the formula on the fly.
Is it even possible to build a formula on the fly with variables. (i.e. the number of sheets, the names of the sheets and the number of joins to add them all together)?

Thanks

Hans,
Thank you for you help. As it turns out, I could was not comfortable using the multi select list box and the degree of programming it required. I came up with a work around that uses a regular list box and once an item is selected it copies the data to a new table for export. The user can select multiple items by selecting each one and clickin an add button. After all are seleced I just transfer the spreadsheet to excel. Thanks again for your help

Hans,
All of this data is passed to me via a third party and resides in access tables. There is a move a foot here to standarize a report using this data but in excel format. I am investigating our ability to get the data from access to excel. I wanted to use the transfer spreedshett method. I wanted to use a query (Possibly a make table query) to extract the data save it in a table and pass it to excel. In my vision of how this was going to work I wanted to pass the query parameter to the text box. I had a button on the form that would launch the query using the parameter criteria in the text box. This then could pass to excel. Excel is important here because this data is going to be used by another program that can handle excel and not access. If there is no way to accomplish this by using a query, is there another way I can get a record set from selected items in a multi select list box and pass that record set to excel? I am already using automation in word in another database and think that once I can get the recordset I need should be able to use automation with excel also.

Thanks

Thanks for getting back to me so quickly. Here is the scenario for intended use. These are proposals for which I need to pull associated hours out of the database. I want the user to select the appropriate proposals from the list box. Then I want to run a query that takes all of the selected items from the text box and pull the associated hours from the database. Each proposal will have many records associated with it. Each record has a certain number of proposed hours. I want to display and report on all the reords associated with the selections in the list box. Does this help?

Hans,
thanks again for the help. I now have the correct (I Think) string being passed to the text box on the form. However, when I set the select query criteria to that text box it produces nothing. If i copy and paste the string directly from the text box into the query criteria and run the query it gives me the results I want. Can I not set the criteria for a select query to the string in the text box? The string that is produced and sent to the text box is the exact string that is built into the select query criteria when I build it manually. The query does not work from the text box but when input directly into the query it works. The code that I am using is below.

Dim frm As Form
Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String
Set frm = Forms!frmQuerySearchResults
Set ctl = frm!vQuery_Results

For Each varItem In Me.vQuery_Results.ItemsSelected
strWhere = strWhere & ” Or ” & Chr(34) & Me.vQuery_Results.ItemData(varItem) & Chr(34)

Next varItem

strWhere = Mid(strWhere, 5)

strSQL = strWhere

Me![Text8] = strSQL
MsgBox strSQL

Any thoughts?
Thanks

Hans,

Thank you for the tip. I have applied what I thought was the relevent part of the code to my situation. I sent the sql to a text box on the form and if gives me the following :

“select Pricing_ID from Pricing where Pricing_ID in (N0684.019.1N2100,N0684.019.2N2100,N0684.019N2100))”

How do I get rid of the “select Pricing_ID from Pricing where Pricing_ID in”

and just leave the query parameter that I need which would be

” N0684.019.1N2100,N0684.019.2N2100,N0684.019N2100 ” ?

Or in my attempt to fit this code to my situation leave something off that I should not have?

The “fit my situation code” looks something like this:

“Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String
Set frm = Forms!frmQuerySearchResults
Set ctl = frm!vQuery_Results

For Each varItem In Me.vQuery_Results.ItemsSelected
strWhere = strWhere & “,” & Me.vQuery_Results.ItemData(varItem)

Next varItem

strWhere = Mid(strWhere, 2)

strSQL = “select Pricing_ID from Pricing where Pricing_ID in (” & strWhere & “))”

Me![Text8] = strSQL”

Thanks for your help.