Posts by JF

Re: Save An Attachment To A Folder

[QUOTE=Mavyak]

Option Explicit
Private WithEvents olInboxItems As Items


Private Sub Application_Startup()
    Dim objNS As NameSpace
    Set objNS = Application.GetNamespace("MAPI")
    Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
    Set myFolder = myFolder.Parent.Folders("My Folders")
    Set myFolder = myFolder.Folders("Inbound Items")
    Set myFolder = myFolder.Folders("Reports")
    Set myFolder = myFolder.Folders("Daily Reports")
    Set objNS = Nothing
End Sub


End Sub

This code goes in the ThisOutlookSession module....QUOTE]

OK I put the code in the This OutlookSesson module. It initiall errored on

Dim objNS As NameSpace
    Set objNS = Application.GetNamespace("MAPI")

I changed to

Dim myNameSpace As Outlook.NameSpace
    Dim myFolder As Outlook.MAPIFolder

No loger get the error. However, nothing happens (Yes I did close and restart outlook)....

Re: Save An Attachment To A Folder

Quote from Mavyak

This works in Outlook 2000:

Code

Option Explicit
Private WithEvents olInboxItems As Items


Private Sub Application_Startup()
  Dim objNS As NameSpace
  Set objNS = Application.GetNamespace("MAPI")
  ' instantiate objects declared WithEvents
  Set olInboxItems = objNS.GetDefaultFolder(olFolderInbox).Items
  Set objNS = Nothing
End Sub


Private Sub olInboxItems_ItemAdd(ByVal Item As Object)
    If InStr(UCase(Item.ReceivedByName), "MAVYAK") > 0 Then
        'Code to Execute goes here
    End If
End Sub

Display More

That code goes in the ThisOutlookSession module. Whenever an email is received from Mavyak, the code specified will run.

Looks like some of the variables are the same as mine. Here is my code, how do I modify it to run continusly:

Sub SaveAttachments()
    Dim myOlapp As Outlook.Application
    Dim myNameSpace As Outlook.NameSpace
    Dim myFolder As Outlook.MAPIFolder
    Dim myItem As Outlook.MailItem
    Dim myAttachment As Outlook.Attachment
     
    Set myOlapp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlapp.GetNamespace("MAPI")
    Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
    Set myFolder = myFolder.Parent.Folders("My Folders")
    Set myFolder = myFolder.Folders("Inboun Items")
    Set myFolder = myFolder.Folders("Reports")
    Set myFolder = myFolder.Folders("Daily Reports")
    
    'create directory for files based on date
      fn = Format(Now(), "mmddyy") & "A"
      myPath = "H:\Kana_Reports\Daily\Daily_Received\" & fn & "\"
      On Error Resume Next
      MkDir myPath
      'myPath = "H:\Kana_Reports\Daily\Daily_Received\" & fn & "\"
      
    ' save attachments to folder
    For Each myItem In myFolder.Items
        If myItem.Attachments.Count <> 0 Then
            For Each myAttachment In myItem.Attachments
                'myAttachment.SaveAsFile "H:\Kana_Reports\Daily\Daily_Received\" & Format(Now(), "mm_dd_yyyy") & "_" & myAttachment.FileName
                myAttachment.SaveAsFile myPath & myAttachment.FileName
                
            Next
        End If
         
    Next
End Sub

Re: Save An Attachment To A Folder

OK, I found the code to rete the folder and have it working. Now I just need to know how to have my code automatically run all the time (be "listening" ) so that when the email is received and moved into the specified folder, the attachment will automatically get saved.

Re: Save An Attachment To A Folder

It may be easier to add the date up front.....

One other question: At present I have it saving the files in a predefinded folder. How would I have it crete a folder based on the date and then save the files to that folder? I.e if today is 10/5/2007 it creates the folder 100507 and then saves the files into that folder.

Re: Save An Attachment To A Folder

Thanks ANdy, that worked.

Now I have one other issue I need assistance with.

Ihave the following line:

myAttachment.SaveAsFile "H:\Kana_Reports\Daily\Daily_Received\" & myAttachment.FileName & _Format(Now(), "mm_dd_yyyy")

while this does save the attachment and does add the date to the end, it is adding it after the file type. i.e

file1.xls10_05_2007 or file2.csv10_05_2007

How do I make it add it prior to the file type (and add an "_" )? i.e

file1_10_05_2007.xls or file2_10_05_2007_.csv

One other question: At present I have it saving the files in a predefinded folder. How would I have it crete a folder based on the date and then save the files to that folder? I.e if today is 10/5/2007 it creates the folder 100507 and then saves the files into that folder.

I found the code to use for saving an attachment in an email to an external folder. (could not post to that threat - http://www.ozgrid.com/forum/showthread.php?t=59353 - so had to create a new one) However I am getting an error. The line of code is:

Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
    Set myFolder = myFolder.Folders("Daily Reports")

The second line is the one with the error. It is a runtime error indicating an object can not be found. The folder is actually a sub folder of a sub folder or a Folder called My Folders. The "My Folders" folder is not a sub folder of the main inbox but a sibling (for lack of better words)

I tried to change the first line to:

Set myFolder = myNameSpace.GetDefaultFolder(olFolderMyFolders)

but then received the error on that line. If it helps, here is the set up of my outlook if it helps:

Mailbox
-Inbox
-My Folders
-- Inbound Items
--- Reports
---- Daily Reports

The email with the attachments are in the "Daily Reports" sub folder

Any Help wouold be appreciated.

Re: Removing Unwanted Returns

Thanks. In this case the =clean(a1) would be fine.
But I did get it to work with the substitute() as follows:

=substitute(a1,char(13)&char(10),"",1)

Re: Removing Unwanted Returns

Thanks for the info. The code function indicated that the characters were infact a char(13) and a char(10) (one of each). so I am not sure why the substitue function did not work. However I did get this to work:

=LEFT(A3,FIND(CHAR(13),A3,1)-1)

I have a data file that is comma delimited (that I bring into excel). one section of the file contains several pices of information seperated by what appears to be a carriage return (shows up as a square box) I can use the find & repace to break it into seperate section. However I can not get rid of the carriage return.

Does anyone know how I can get rid of it?

I have tried the following formula without success:

=substitute(a1,CHAR(10),"|") 


=substitute(a1,CHAR(13),"|")

Re: Use Data From Form In Query

Quote from abundant_lyfe

Hi Norie.

I've been playing around with parameters as you suggested and have come up with this

Code

DoCmd.OpenForm "QA_Check_View_Report", acNormal, , Lab_ID = LabID

however, the form is blank because the data is actually on a subform, called CNMT_Temp_Query, so I'm not sure how to reference that.

I'm really in a twist aren't I!

Display More

did you try

DoCmd.OpenForm "QA_Check_View_Report", acNormal, , Lab_ID = [CNMT_Temp_Query]![LabID]

I am tring to create a table via an SQL command (vs the create table wizzard). Is there a way to create a numeric field with a limit of 5 digits in access? I know with ANSI SQL it would be number(5) but with access that did not work I tried the below but it errors on the "zip" line

create table employee
 (emp_id varchar(9) not null,
  last_Name varchar(20) not null,
  first_name varchar(20) not null,
  st_address varchar(30) not null,
  city varchar(20) not nnull,
  state char(2) not null,
  zip Integer(5) not null,
  date_hire date);

The code below works but does not limit the "zip" field to 5 digits

create table employee
 (emp_id varchar(9) not null,
  last_Name varchar(20) not null,
  first_name varchar(20) not null,
  st_address varchar(30) not null,
  city varchar(20) not nnull,
  state char(2) not null,
  zip Integer not null,
  date_hire date);

Can this be done or does the limiting have to be done via validation code in a module etc? I know that if I made it a character field I can limit it but then ahve to deal with the possibility of letters .

Re: Copy & Paste Data Based On Current Month

The code can be shortend as follows:

Sub Copy1() 
     '
     '
    Dim tmp As Integer 
     
    tmp = Month(Now()) 
    tmp2 = tmp + 5 
     'MsgBox (tmp)
     'MsgBox (tmp2)
     
     Range("D70:D72").Select 
    Selection.Copy 
    Cells(2, tmp2).Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 


End Sub

if you look at the code, the line

tmp=Month(now())

calculates the numeric value of the current month (for Jun it results in 6). To check that it is working correctly, unremark the line

MsgBox(tmp)

by removing the ' then when you run the macro, it will pop up a box showing the number. line

tmp2=tmp+5

added 5 to the tmp number becasue in the sample you posted, your graph data started in column F which is the 6th column. The first 5 columns were your static data. so we need to add 5 to the number of the month to get the correct column number to post the data into. It worked correctly on mine. It it was posting to the July column, then either you have one less static column or possibly the date on your computer is off. by unremarking the MsgBox lines, it will show where the issue may be.

Re: Use Macro To Copy And Paste Data Based On Current Month

Try the following (it is not the most elegant but does what I think you want)

Sub Copy1()
'
'
Dim tmp As Integer


tmp = Month(Now())
tmp2 = tmp + 5
'MsgBox (tmp)
'MsgBox (tmp2)


    Range("D70").Select
    Selection.Copy
    Cells(2, tmp2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("D71").Select
    Selection.Copy
    Cells(3, tmp2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("D72").Select
    Selection.Copy
    Cells(4, tmp2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


End Sub

I believe the code can be combined but can not remember how to get the pastespecial to work.

Range("D70").Copy Destination:=Cells(2, tmp2)

not sure how to get the pastespecial into the line above to paste just the value

Re: Future Interest

Try doing a search of the built in excel help for future value or financial functions. It will give a complete list of the different formulas/functions available with examples.

Re: Find Text String And Report Back Data In Unknown Number Of Rows

Quote from doo_leroux

REALLY? Nuts....I guess I do need a macro.

By "report back" I mean the following;

Report one

Name
Danielle
Joe
John
Mel
Cassie
(etc. 15,000 names or so but all are unique)

Report 2

Name Address Zip Phone #
Danielle
Danielle
Danielle
Danielle
Joe
Joe
John
John
John
John
Mel
Cassie
Cassie
(etc. 15,000 names or so but all are unique)

First I need to find each unique name (can't do it manually as there are too many) in report 2 from report 1. Then I need to take all data from all relevant rows (amt of data in rows differs everytime) from Report 2 and have it show up in Report 1 next to the name.

Get it? Oh this is never going to get done! Thanks for all your help guys! You are the best!

Display More

From your sample above, why not just do a sort on the names, this will group them together like you have them in the "report" two output you've shown.

How can I have excell display a specific picture based on the value of a cell.

For example, if A1 => 75 picture 1
if a1 <75 picture 2

Re: Random Number Generator Macro

Quote from daamyour

What can I do to eliminat the selection marque for each macro and prevent the sheet flip-flop?

Try putting:

Application.ScreenUpdating = False

at the top of your macro

and

Application.ScreenUpdating = True

at the end

Re: Delete Rows Not Meeting Criteria

Are the 5 names static or do they need to change?

If they are static, you could set up a named range and then use a formula to check the columns.

You could use a working column and a formula to indicate which rows to keep.

So if your names are in column A & B list your desired names in J1-J5

then use the following formula in Col C:

=IF(AND(ISERROR(MATCH(A1,$J$1:$J$5,0)),ISERROR(MATCH(B1,$J$1:$J$5,0))),"N","Y")

This could be copied down Either manually or via vba, then sort the list and remove any lines with a "N" in col C

You can do it via vba as follows:

Sub DeleteLines()
'
'
tmp = Range("a65536").End(xlUp).Row
Range("c1").Select




With Range("C1:C" & tmp)
    .FormulaR1C1 = _
        "=IF(AND(ISERROR(MATCH(R[]C[-2],R1C10:R5C10,0)),ISERROR(MATCH(R[]C[-1],R1C10:R5C10,0))),""N"",""Y"")"
        .Copy
        .PasteSpecial Paste:=xlPasteValues
End With


'Delete rows with "N"


 On Error Resume Next
    Columns("C:C").Select
  SR1 = Selection.Find(What:="N", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Row
        
 On Error GoTo 0
  If SR1 <> 0 Then Rows(SR1 & ":" & tmp).Delete Shift:=xlUp


0:
Application.ScreenUpdating = True
End Sub

Re: Open Files With Number Extensions

I am guessing that the file name is the date the file was created (or poertains to). If this is correct, is it always the previous day, two days prior etc. If so, you can create the file name based on the current date (minus 1, 2, etc) and use the text value of that. and append the .xls to the end of it.

Re: Manipulating Charts Without Activating

Try this:

Sub MyTest()
Dim mychart As ChartObject
Set mychart = Sheet1.ChartObjects("chart 1")
With mychart.Chart
    .ChartType = xlBarClustered
    .PlotBy = xlColumns
    
End With
End Sub