date:20111003

Re: $$Excel-Macros$$ PPT

2011-10-03 Thread Chandra Shekar

Hello,

Any help on below request pls.

Thanks,

Chandra Shekar B

On Mon, Oct 3, 2011 at 11:31 AM, Chandra Shekar <
chandrashekarb@gmail.com> wrote:

> Hello Swapnil,
>
> Thanks for the code, but I am not getting desired output bcoz its copying
> once one slide into the new presentation.
>
> Please find attached file for the exact output.
>
> Regards,
>
> Chandra Shekar B
>
>   On Mon, Oct 3, 2011 at 9:21 AM, Swapnil Palande <
> palande.swapni...@gmail.com> wrote:
>
>> Hi,
>>
>> Use following code
>>
>> Sub createPPT()
>> Dim slide As slide
>> Dim shp As Shape
>> Dim mypresentation As Presentation
>> Dim filepath As String
>>
>> filepath = ActivePresentation.Path
>>
>> For Each slide In ActivePresentation.Slides
>> For Each shp In slide.Shapes
>> If shp.TextFrame.TextRange.Text = "XYZ 1" Then
>> Set mypresentation =
>> Application.Presentations.Add(msoTrue)
>> slide.Copy
>> mypresentation.Slides.Paste
>> mypresentation.SaveAs filepath & "\xyz1.pptx"
>> mypresentation.Close
>> ElseIf shp.TextFrame.TextRange.Text = "XYZ 2" Then
>> Set mypresentation =
>> Application.Presentations.Add(msoTrue)
>> slide.Copy
>> mypresentation.Slides.Paste
>> mypresentation.SaveAs filepath & "\xyz2.pptx"
>> mypresentation.Close
>> ElseIf shp.TextFrame.TextRange.Text = "XYZ 3" Then
>> Set mypresentation =
>> Application.Presentations.Add(msoTrue)
>> slide.Copy
>> mypresentation.Slides.Paste
>> mypresentation.SaveAs filepath & "\xyz3.pptx"
>> mypresentation.Close
>> End If
>> Next shp
>> Next
>>
>> Set mypresentation = Nothing
>> End Sub
>> Pls find attached ppt.
>>
>> Regards,
>>
>> Swapnil.
>>
>>   On Mon, Oct 3, 2011 at 11:59 AM, Chandra Shekar <
>> chandrashekarb@gmail.com> wrote:
>>
>>> Hello,
>>>
>>> Any help on this. Thanks in advance.
>>>
>>> Regards,
>>>
>>> Chandra Shekar B
>>>
>>> On Fri, Sep 30, 2011 at 8:47 AM, Chandra Shekar <
>>> chandrashekarb@gmail.com> wrote:
>>>
 Hello,

 I am looking for a macro which can copy slides based on TITLE and create
 a new presentation. Could u please help me out in the attached file.

 In this case I need create 4 PPTs i.e. for
 1) Title A one PPT with same file name as Title,
 2) Title B one PPT with same file name as Title,
 3) Title C one PPT with same file name as Title,
 4) Title D one PPt with same file name as Title.


 Thanks,

 Chandra Shekar B

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 <><><><><><><><><><><><><><><><><><><><><><>
 Like our page on facebook , Just follow below link
 http://www.facebook.com/discussexcel

>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>

-- 
-

Re: $$Excel-Macros$$ Update sheets on Enter

2011-10-03 Thread Mr excel

huhh..My COmmunication is sucking day by day...sorry guillet.i could not
communicate to you properlyHope this time i could make it clear

>From the Worksheet *MAKESHEETS example  *given by you.You are
adding/creating sheets based on the REP cell...Here the macro starts only
when it is run manually.
Instead,i  want the same type of solution.I.e. adding/creating sheets when a
new row with new data is entered into the main sheet.

To elaborate more in detail,when a new row is entered after the 44th  row in
row 45.the macro needs to be run automatically adding new worksheet based on
the column REP.

If all the sheets are added to the workbook previously the REP in the new
row should be added to the existing worksheet which was added before the
present row.The new row should be added to the worksheet underneath the
previous row.

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ vba for loops -beginner

2011-10-03 Thread Mr excel

Thanks.very useful for beginners like me

On Mon, Jul 25, 2011 at 11:35 PM, XLS S  wrote:

> please find the attachment for basic loop concept
>
> On Mon, Jul 25, 2011 at 5:50 PM, vijayajith VA wrote:
>
>> Hi rajan
>>
>> Can you explain
>> below code
>>  st = st & vbTab & Arr(Rw, Cl)
>> Next
>> st = st & vbCrLf
>> Next
>>
>> St means ?
>>
>>
>>
>>
>> On Sun, Jul 24, 2011 at 1:08 PM, Rajan_Verma wrote:
>>
>>> See if it Helps
>>>
>>> Option Base 1
>>> Sub StoreInArrya()
>>>Dim Arr() As Variant
>>>Dim rng As Range
>>>Dim Rw As Integer
>>>Dim Cl As Integer
>>>
>>>Set rng = Range("A1:C5")
>>>ReDim Arr(rng.Cells.Rows.Count, rng.Cells.Columns.Count)
>>>
>>>For Rw = 1 To rng.Rows.Count
>>>For Cl = 1 To rng.Columns.Count
>>>Arr(Rw, Cl) = rng.Cells(Rw, Cl).Value
>>>Next
>>>Next
>>>
>>>   For Rw = 1 To rng.Rows.Count
>>>For Cl = 1 To rng.Columns.Count
>>>st = st & vbTab & Arr(Rw, Cl)
>>>Next
>>>st = st & vbCrLf
>>>Next
>>>   MsgBox st
>>> End Sub
>>>
>>> -Original Message-
>>> From: excel-macros@googlegroups.com [mailto:
>>> excel-macros@googlegroups.com]
>>> On Behalf Of netuser501
>>> Sent: Sunday, July 24, 2011 6:42 AM
>>> To: MS EXCEL AND VBA MACROS
>>> Subject: $$Excel-Macros$$ vba for loops -beginner
>>>
>>> Hi
>>>
>>> The question worksheets is filled with data on 5 rows and 3 columns.
>>> I'd like to have this data stored in an Array. For testing purpose, I
>>> want to test if the loop is "reading" the data with an integer "tmp"
>>> that should return the number of cells filled in with data.
>>>
>>>  Set wkb = ThisWorkbook
>>>  Set wks = wkb.Worksheets("questions")
>>>
>>>
>>>  For i = 0 To wks.Cells(i + 1, 1 = ""
>>>For j = 0 To wks.Cells(i + 1, j + 1) = ""
>>>  tmp = tmp + 1
>>>Next j
>>>  Next i
>>>
>>> Why aren't Loop j and i looping?
>>>
>>>
>>>
>>> Another question :
>>>
>>> Is this possible to redim an array in a 2d for loop :
>>>
>>> dim SomeArray() as variant
>>> redim SomeArray(0,0) 'A
>>>
>>> for i = 0 to MAXL
>>> for j = 0 to MAXC
>>> SomeArray(i,j) = cells(i+1,j+1)
>>> redim preserve SomeArray(i,j+1)
>>> next j
>>> redim preserve SomeArray(i+1,j) 'j has still MAXC as value
>>> next i
>>>
>>> I understand it's not making sense since MAXL and MAXC could be
>>> declared in A.
>>>
>>> Back to the previous question it would make sense to redim a 2d array
>>> in a for loop until a cell is empty in the column and a cell is empty
>>> in the lines.
>>>
>>> Thanks for your precious help,
>>>
>>> --
>>>
>>> 
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>  --
>>
>> --
>>
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>  --
>
> ---

$$Excel-Macros$$ Need Help

2011-10-03 Thread syed aliya raza hashim

Can excel file such as reports be access from web or can be acccess as web
pages suppose we have 10 sheets but from some url we want to see the reports

-- 
syed aliya

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ How To Add A Year Field to a PivotTable Field List

2011-10-03 Thread NOORAIN ANSARI

Dear John,

You can also try...
Pivot Table Tools->Options->Group Selection...

-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 
On Tue, Oct 4, 2011 at 1:32 AM, John A. Smith wrote:

> Attached is a spreadsheet from this forum.  I would like to know how the
> "Year" field was added to a pivot table with only a date and amount column
> in the Data Source (See Attached File).
>
> This would be very helpful in a lot of applications I run into.
>
> Thank you for your ongoing assistance and teachings.
>
> John
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

RE: $$Excel-Macros$$ Help, Alert MsgBox Pair duplicate entry in row

2011-10-03 Thread Asa Rossoff

I would just use a data validation rule.

1. Select the range that needs to not allow duplicate entry.
2. Data/Data Tools/Data Validation on the ribbon.
3. For Validation Criteria:
ALLOW:   Custom
FORMULA: =COUNTIF(A$2:A$1000,$A2)=1
   (In the formula, A$2:A$1000 should be a range with the same rows you
highlighted for validation, but just the first column, and $A2 should be the
top left cell of the range).
IGNORE BLANK: whatever you want
4. Optionally change settings in the Error Alert tab.  Note depending on the
alert type, Excel will either prevent duplicate entry, warn users with the
default to cancel their entry, or only warn users.

Excel Help mentions this method at 
http://office.microsoft.com/client/helppreview14.aspx?AssetId=HP010342173&NS
=EXCEL#BM4_8

Scroll up to the top of this page (the given link is bookmarked to near the
end) and read some of the other infromation for limitations to this data
validation method.

In my example I placed the $ signs in the range/cell references such that
the same formula will work/automatically change for each cell in even a
range including multiple columns and validate for no duplicates in each
column seperately.

Asa


From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Sam Mathai Chacko
Sent: Monday, October 03, 2011 12:16 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Help, Alert MsgBox Pair duplicate entry in row

Chil,

Paste this code in the sheet code module of the respective sheet. Now once
any entry is made in any row of B & C, it will check and display a message
if any duplicate is found.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lngCount As Long
    Dim lngLoop As Long
    Dim strMsg As String
    lngCount =
Evaluate("=SUMPRODUCT((Hoja1!$B$2:$B$57&Hoja1!$C$2:$C$57<>)*(Hoja1!$B$2:
$B$57=Hoja1!B" & Target.Cells(1).Row & ")*(Hoja1!$C$2:$C$57=Hoja1!C" &
Target.Cells(1).Row & "))")
    If lngCount > 1 Then
    strMsg = "Pair duplicated " & lngCount & " times in " &
Application.Rept(vbNewLine, 2)
    For lngCount = lngCount To 1 Step -1
    strMsg = strMsg & "Row " &
Evaluate("=SUMPRODUCT(LARGE((ROW(Hoja1!$B$2:$B$57))*(Hoja1!$B$2:$B$57&Hoja1!
$C$2:$C$57<>)*(Hoja1!$B$2:$B$57=Hoja1!B" & Target.Cells(1).Row &
")*(Hoja1!$C$2:$C$57=Hoja1!C" & Target.Cells(1).Row & ")," & lngCount &
"))") & vbNewLine
    Next lngCount
    MsgBox strMsg, vbOKOnly + vbInformation, "Duplicates"
    End If
    
End Sub

Regards,

Sam
On Mon, Oct 3, 2011 at 10:15 PM, ChilExcel  wrote:
hi 
 
i Need VB lines codes for 
Alert MsgBox Pair duplicate entry in row
 
Column D function account and duplicates alerts (NO Problem!)
Attach File , thank you all 

-- 
Visita ; http://sites.google.com/site/chilexcel/Home
Visita ; http://www.youtube.com/user/timextag41

-- 

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com
 
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel



-- 
Sam Mathai Chacko
-- 

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com
 
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Thanks everyone.

2011-10-03 Thread santhosh qatester

Congrats Ayush :)

Regards,
Santhosh



On Tue, Oct 4, 2011 at 12:14 AM, Ayush Jain  wrote:

> Dear members,
>
> I am proud to share that I have been re-awarded the Micrososft MVP award in
> 2011.  My deep thanks to each group member for all your support and
> contribution to this group.
> I keep saying that this forum is a great community and emerging as one of
> the largest forum in the world, because of the 'helping hand' we all give
> one another.
>
> Thanks again & keep posting !
>
> Regards,
> Ayush Jain
> Group Manager
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Help, Alert MsgBox Pair duplicate entry in row

2011-10-03 Thread ChilExcel

Excellent, exactly what I needed  thank you very much Sam !!!

2011/10/3 Sam Mathai Chacko 

> Chil,
>
> Paste this code in the sheet code module of the respective sheet. Now once
> any entry is made in any row of B & C, it will check and display a message
> if any duplicate is found.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim lngCount As Long
> Dim lngLoop As Long
> Dim strMsg As String
> lngCount =
> Evaluate("=SUMPRODUCT((Hoja1!$B$2:$B$57&Hoja1!$C$2:$C$57<>)*(Hoja1!$B$2:$B$57=Hoja1!B"
> & Target.Cells(1).Row & ")*(Hoja1!$C$2:$C$57=Hoja1!C" & Target.Cells(1).Row
> & "))")
> If lngCount > 1 Then
> strMsg = "Pair duplicated " & lngCount & " times in " &
> Application.Rept(vbNewLine, 2)
> For lngCount = lngCount To 1 Step -1
> strMsg = strMsg & "Row " &
> Evaluate("=SUMPRODUCT(LARGE((ROW(Hoja1!$B$2:$B$57))*(Hoja1!$B$2:$B$57&Hoja1!$C$2:$C$57<>)*(Hoja1!$B$2:$B$57=Hoja1!B"
> & Target.Cells(1).Row & ")*(Hoja1!$C$2:$C$57=Hoja1!C" & Target.Cells(1).Row
> & ")," & lngCount & "))") & vbNewLine
> Next lngCount
> MsgBox strMsg, vbOKOnly + vbInformation, "Duplicates"
> End If
>
> End Sub
>
> Regards,
>
> Sam
>
>
> On Mon, Oct 3, 2011 at 10:15 PM, ChilExcel  wrote:
>
>> hi
>>
>> i Need VB lines codes for
>> Alert MsgBox Pair duplicate entry in row
>>
>> Column D function account and duplicates alerts (NO Problem!)
>>
>> Attach File , thank you all
>>
>> --
>> Visita ; http://sites.google.com/site/chilexcel/Home
>> Visita ; http://www.youtube.com/user/timextag41
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Sam Mathai Chacko
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
Visita ; http://sites.google.com/site/chilexcel/Home
Visita ; http://www.youtube.com/user/timextag41

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ How To Add A Year Field to a PivotTable Field List

2011-10-03 Thread John A. Smith

Chethan,

Thank you for your quick and enlightening response.  This opens up new
avenues for me.  Thank you very much!!!

John




On Mon, Oct 3, 2011 at 4:26 PM, B.N.Chethan kumar  wrote:

> Hi,
>
> you have option to group the file...Bring the data in excel pivit..and
> right clickselect group...and select group by year..
>
> you can also group my month & Quarter too..
>
>
> Regards
> Chethan Kumar BN
>
> On Tue, Oct 4, 2011 at 1:32 AM, John A. Smith wrote:
>
>> Attached is a spreadsheet from this forum.  I would like to know how the
>> "Year" field was added to a pivot table with only a date and amount column
>> in the Data Source (See Attached File).
>>
>> This would be very helpful in a lot of applications I run into.
>>
>> Thank you for your ongoing assistance and teachings.
>>
>> John
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> B.N Chetan kumar
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ How To Add A Year Field to a PivotTable Field List

2011-10-03 Thread B.N.Chethan kumar

Hi,

you have option to group the file...Bring the data in excel pivit..and right
clickselect group...and select group by year..

you can also group my month & Quarter too..


Regards
Chethan Kumar BN

On Tue, Oct 4, 2011 at 1:32 AM, John A. Smith wrote:

> Attached is a spreadsheet from this forum.  I would like to know how the
> "Year" field was added to a pivot table with only a date and amount column
> in the Data Source (See Attached File).
>
> This would be very helpful in a lot of applications I run into.
>
> Thank you for your ongoing assistance and teachings.
>
> John
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
B.N Chetan kumar

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Working out of Statistics on Exam Results URGENT solution needed!

2011-10-03 Thread shaneallen

Hi
Thanx for the very practical suggestion but unfortunately the sample book is 
over 10MB big and I tried to post it earlier this morning but unfortunately 
my post did not appear I think due to the size of this file. I can however 
see if I can post it as a text file from the Excel sheet.
shaneallen

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


grade_9_stats.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

2011-10-03 Thread Nitin Hemmady

*** SOLVED **

The best way to generate a connection string is to use the macro recorder
and go through the steps manually.

1. FIRST: Set up a Data Source Name (DSN)
2. Then use the macro recorder in Excel, and go to "Data"->"Existing
Connections" and choose the DSN.
3. Then choose which cell to begin the output
4. Now stop the macro recorder, and look at the VBA output. Edit the VB as
necessary. I wanted to run a specific query between 4 tables, so I created
an array:

mysql01 = "select (four table query)"

ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=(dbname);", Destination:=Range("$A$1")).QueryTable
.CommandText = Array(mysql01)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"\\path.odc"
.Refresh BackgroundQuery:=False
End With

On Mon, Oct 3, 2011 at 1:41 PM, Nitin Hemmady wrote:

> Yes maybe the macro recorder is a good idea, thanks!
>
>
> On Mon, Oct 3, 2011 at 1:33 PM, Sam Mathai Chacko wrote:
>
>> I connect to Access database as well as SQL server databases using the
>> same method suggested by Mechanic in the SQLJuicer code. Have you tried
>> modifying your query with a more conventional VBA connection code snippet?
>> If that didn't work, did you try recording a macro while you perform the
>> query manually?
>>
>> Regards,
>>
>> Sam
>>
>>
>> On Tue, Oct 4, 2011 at 12:00 AM, Nitin Hemmady wrote:
>>
>>> Hi Yes I saw this, but I still get a "General ODBC" error.  thank you
>>>
>>>
>>> On Mon, Oct 3, 2011 at 1:23 PM, Sam Mathai Chacko wrote:
>>>
 Working with the inbuilt query feature in Excel can get tricky.

 Try finding the exact syntax for a MySQL database from *
 http://connectionstrings.com/*

 And on how to run a query from within Excel, try

 *
 http://www.excelfox.com/forum/showthread.php?66-Execute-SQL-From-Excel-(VBA)
 *

 Regards,

 Sam Mathai Chacko (GL)


 On Mon, Oct 3, 2011 at 10:48 PM, Nitin  wrote:

> Hi All,
>
> I want to be able to run queries within Excel like this:
>
>
> --
>
>  mysql01 = "select * from some_table"
>
>   ActiveWorkbook.Worksheets.Add
>With ActiveSheet.QueryTables.Add(Connection:= _
>"ODBC;DRIVER={MySQL ODBC 3.51
> Driver};SERVER=111.111.11.11;PORT=3306;DATABASE=mydb_name;
> USER=my_user_name;PASSWORD=mypassword;OPTION=3; " _
>, Destination:=Range("A1"))
>.CommandText = Array(mysql01)
>
>
>.Name = "Query from MyDB"
>.FieldNames = True
>.RowNumbers = False
>.FillAdjacentFormulas = False
>.PreserveFormatting = True
>.RefreshOnFileOpen = False
>.BackgroundQuery = True
>.RefreshStyle = xlInsertDeleteCells
>.SavePassword = True
>.SaveData = True
>.AdjustColumnWidth = True
>.RefreshPeriod = 0
>.PreserveColumnInfo = True
>.Refresh BackgroundQuery:=False
>End With
>
> --
>
> When I try to run my VBA module, I get a General ODBC error, which
> does not help at all.
> Can anyone tell me what I am doing wrong here?
>
> Also, I realize there are probably better methods for connecting to
> excel, but for now, if anyone can answer this, that would be
> fantastic! Thanks!
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @
> http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



 --
 Sam Mathai Chacko

  --

 --
 Some important links for excel users:
 1.

Re: $$Excel-Macros$$ Update sheets on Enter

2011-10-03 Thread dguillett1

OK. I now see what you wanted which means a slight modification to my macro. 
However, I also posted a STRONG suggestion to just use filters.

From: Mr excel 
Sent: Monday, October 03, 2011 4:19 AM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Update sheets on Enter

Hi group,  

   I had been struggling with a solution for a problem from many days..
Recently i had seen a macro in this group which is very much nearer to my 
problem.The sample data is taken from one of the previous examples given by 
guillet.

Going by the same sample data :
the main sheet contains the following columns with data underneath it.
  Date Region Rep Item Units Cost Total 

I want sheets to be added & updated based on each Region and with the date in 
the chronological order.
If a sheet exists already with the REGION NAME there is no need to add the 
sheet with the same name instead new data should be added from the main sheet 
to the respective sheets (REGION wise)

I had attached the worksheet for reference.Kindly help me

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Update sheets on Enter

2011-10-03 Thread dguillett1

I don’t understand what you need There is a master sheet and no 
sample sheet for your desires. My macro and the other do essentially the same 
thing.

From: Mr excel 
Sent: Monday, October 03, 2011 10:54 AM
To: excel-macros@googlegroups.com 
Subject: Re: $$Excel-Macros$$ Update sheets on Enter

Sorry, guillet.the solution u had provided is not what i m looking for
I need a vba macro as in the example sheet attached here.its my fault that i 
had not given the full details...Very sorry for that...

i need the solution very much similiar to the attached makelist example 
sheet.In that example you had created sheets from the REP cell.

I need to update all the sheets when new data is entered in the main sheet..

Thanks in advance.

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Help, Alert MsgBox Pair duplicate entry in row

2011-10-03 Thread Sam Mathai Chacko

Chil,

Paste this code in the sheet code module of the respective sheet. Now once
any entry is made in any row of B & C, it will check and display a message
if any duplicate is found.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngCount As Long
Dim lngLoop As Long
Dim strMsg As String
lngCount =
Evaluate("=SUMPRODUCT((Hoja1!$B$2:$B$57&Hoja1!$C$2:$C$57<>)*(Hoja1!$B$2:$B$57=Hoja1!B"
& Target.Cells(1).Row & ")*(Hoja1!$C$2:$C$57=Hoja1!C" & Target.Cells(1).Row
& "))")
If lngCount > 1 Then
strMsg = "Pair duplicated " & lngCount & " times in " &
Application.Rept(vbNewLine, 2)
For lngCount = lngCount To 1 Step -1
strMsg = strMsg & "Row " &
Evaluate("=SUMPRODUCT(LARGE((ROW(Hoja1!$B$2:$B$57))*(Hoja1!$B$2:$B$57&Hoja1!$C$2:$C$57<>)*(Hoja1!$B$2:$B$57=Hoja1!B"
& Target.Cells(1).Row & ")*(Hoja1!$C$2:$C$57=Hoja1!C" & Target.Cells(1).Row
& ")," & lngCount & "))") & vbNewLine
Next lngCount
MsgBox strMsg, vbOKOnly + vbInformation, "Duplicates"
End If

End Sub

Regards,

Sam

On Mon, Oct 3, 2011 at 10:15 PM, ChilExcel  wrote:

> hi
>
> i Need VB lines codes for
> Alert MsgBox Pair duplicate entry in row
>
> Column D function account and duplicates alerts (NO Problem!)
>
> Attach File , thank you all
>
> --
> Visita ; http://sites.google.com/site/chilexcel/Home
> Visita ; http://www.youtube.com/user/timextag41
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
Sam Mathai Chacko

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Thanks everyone.

2011-10-03 Thread Ayush Jain

Dear members,

I am proud to share that I have been re-awarded the Micrososft MVP award in
2011.  My deep thanks to each group member for all your support and
contribution to this group.
I keep saying that this forum is a great community and emerging as one of
the largest forum in the world, because of the 'helping hand' we all give
one another.

Thanks again & keep posting !

Regards,
Ayush Jain
Group Manager

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

2011-10-03 Thread Nitin Hemmady

Yes maybe the macro recorder is a good idea, thanks!

On Mon, Oct 3, 2011 at 1:33 PM, Sam Mathai Chacko wrote:

> I connect to Access database as well as SQL server databases using the same
> method suggested by Mechanic in the SQLJuicer code. Have you tried modifying
> your query with a more conventional VBA connection code snippet? If that
> didn't work, did you try recording a macro while you perform the query
> manually?
>
> Regards,
>
> Sam
>
>
> On Tue, Oct 4, 2011 at 12:00 AM, Nitin Hemmady wrote:
>
>> Hi Yes I saw this, but I still get a "General ODBC" error.  thank you
>>
>>
>> On Mon, Oct 3, 2011 at 1:23 PM, Sam Mathai Chacko wrote:
>>
>>> Working with the inbuilt query feature in Excel can get tricky.
>>>
>>> Try finding the exact syntax for a MySQL database from *
>>> http://connectionstrings.com/*
>>>
>>> And on how to run a query from within Excel, try
>>>
>>> *
>>> http://www.excelfox.com/forum/showthread.php?66-Execute-SQL-From-Excel-(VBA)
>>> *
>>>
>>> Regards,
>>>
>>> Sam Mathai Chacko (GL)
>>>
>>>
>>> On Mon, Oct 3, 2011 at 10:48 PM, Nitin  wrote:
>>>
 Hi All,

 I want to be able to run queries within Excel like this:


 --

  mysql01 = "select * from some_table"

   ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={MySQL ODBC 3.51
 Driver};SERVER=111.111.11.11;PORT=3306;DATABASE=mydb_name;
 USER=my_user_name;PASSWORD=mypassword;OPTION=3; " _
, Destination:=Range("A1"))
.CommandText = Array(mysql01)


.Name = "Query from MyDB"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

 --

 When I try to run my VBA module, I get a General ODBC error, which
 does not help at all.
 Can anyone tell me what I am doing wrong here?

 Also, I realize there are probably better methods for connecting to
 excel, but for now, if anyone can answer this, that would be
 fantastic! Thanks!

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 <><><><><><><><><><><><><><><><><><><><><><>
 Like our page on facebook , Just follow below link
 http://www.facebook.com/discussexcel

>>>
>>>
>>>
>>> --
>>> Sam Mathai Chacko
>>>
>>>  --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>  --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Sam Mathai Chacko
>
> --
>
> -

Re: $$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

2011-10-03 Thread Sam Mathai Chacko

I connect to Access database as well as SQL server databases using the same
method suggested by Mechanic in the SQLJuicer code. Have you tried modifying
your query with a more conventional VBA connection code snippet? If that
didn't work, did you try recording a macro while you perform the query
manually?

Regards,

Sam

On Tue, Oct 4, 2011 at 12:00 AM, Nitin Hemmady wrote:

> Hi Yes I saw this, but I still get a "General ODBC" error.  thank you
>
>
> On Mon, Oct 3, 2011 at 1:23 PM, Sam Mathai Chacko wrote:
>
>> Working with the inbuilt query feature in Excel can get tricky.
>>
>> Try finding the exact syntax for a MySQL database from *
>> http://connectionstrings.com/*
>>
>> And on how to run a query from within Excel, try
>>
>> *
>> http://www.excelfox.com/forum/showthread.php?66-Execute-SQL-From-Excel-(VBA)
>> *
>>
>> Regards,
>>
>> Sam Mathai Chacko (GL)
>>
>>
>> On Mon, Oct 3, 2011 at 10:48 PM, Nitin  wrote:
>>
>>> Hi All,
>>>
>>> I want to be able to run queries within Excel like this:
>>>
>>>
>>> --
>>>
>>>  mysql01 = "select * from some_table"
>>>
>>>   ActiveWorkbook.Worksheets.Add
>>>With ActiveSheet.QueryTables.Add(Connection:= _
>>>"ODBC;DRIVER={MySQL ODBC 3.51
>>> Driver};SERVER=111.111.11.11;PORT=3306;DATABASE=mydb_name;
>>> USER=my_user_name;PASSWORD=mypassword;OPTION=3; " _
>>>, Destination:=Range("A1"))
>>>.CommandText = Array(mysql01)
>>>
>>>
>>>.Name = "Query from MyDB"
>>>.FieldNames = True
>>>.RowNumbers = False
>>>.FillAdjacentFormulas = False
>>>.PreserveFormatting = True
>>>.RefreshOnFileOpen = False
>>>.BackgroundQuery = True
>>>.RefreshStyle = xlInsertDeleteCells
>>>.SavePassword = True
>>>.SaveData = True
>>>.AdjustColumnWidth = True
>>>.RefreshPeriod = 0
>>>.PreserveColumnInfo = True
>>>.Refresh BackgroundQuery:=False
>>>End With
>>>
>>> --
>>>
>>> When I try to run my VBA module, I get a General ODBC error, which
>>> does not help at all.
>>> Can anyone tell me what I am doing wrong here?
>>>
>>> Also, I realize there are probably better methods for connecting to
>>> excel, but for now, if anyone can answer this, that would be
>>> fantastic! Thanks!
>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
>>
>> --
>> Sam Mathai Chacko
>>
>>  --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
Sam Mathai Chacko

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blo

Re: $$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

2011-10-03 Thread Nitin Hemmady

Hi Yes I saw this, but I still get a "General ODBC" error.  thank you

On Mon, Oct 3, 2011 at 1:23 PM, Sam Mathai Chacko wrote:

> Working with the inbuilt query feature in Excel can get tricky.
>
> Try finding the exact syntax for a MySQL database from *
> http://connectionstrings.com/*
>
> And on how to run a query from within Excel, try
>
> *
> http://www.excelfox.com/forum/showthread.php?66-Execute-SQL-From-Excel-(VBA)
> *
>
> Regards,
>
> Sam Mathai Chacko (GL)
>
>
> On Mon, Oct 3, 2011 at 10:48 PM, Nitin  wrote:
>
>> Hi All,
>>
>> I want to be able to run queries within Excel like this:
>>
>>
>> --
>>
>>  mysql01 = "select * from some_table"
>>
>>   ActiveWorkbook.Worksheets.Add
>>With ActiveSheet.QueryTables.Add(Connection:= _
>>"ODBC;DRIVER={MySQL ODBC 3.51
>> Driver};SERVER=111.111.11.11;PORT=3306;DATABASE=mydb_name;
>> USER=my_user_name;PASSWORD=mypassword;OPTION=3; " _
>>, Destination:=Range("A1"))
>>.CommandText = Array(mysql01)
>>
>>
>>.Name = "Query from MyDB"
>>.FieldNames = True
>>.RowNumbers = False
>>.FillAdjacentFormulas = False
>>.PreserveFormatting = True
>>.RefreshOnFileOpen = False
>>.BackgroundQuery = True
>>.RefreshStyle = xlInsertDeleteCells
>>.SavePassword = True
>>.SaveData = True
>>.AdjustColumnWidth = True
>>.RefreshPeriod = 0
>>.PreserveColumnInfo = True
>>.Refresh BackgroundQuery:=False
>>End With
>>
>> --
>>
>> When I try to run my VBA module, I get a General ODBC error, which
>> does not help at all.
>> Can anyone tell me what I am doing wrong here?
>>
>> Also, I realize there are probably better methods for connecting to
>> excel, but for now, if anyone can answer this, that would be
>> fantastic! Thanks!
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Sam Mathai Chacko
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Working out of Statistics on Exam Results URGENT solution needed!

2011-10-03 Thread Sam Mathai Chacko

Sometimes, a sample workbook speaks louder than words. So would really help
in boosting your chance to get a solution is to have the expected output
also posted as a sample. That would give the volunteers here a perspective
of what the objective is.

On Mon, Oct 3, 2011 at 11:40 PM, shaneallen  wrote:

> Hi
>
> I posted earlier today but my post does not appear at all,maybe the Excel
> sheet I sent(to show what I want) was over 10MB big as well as the sheet i
> actually want a solution for is about 4MB .
> What I want is to have a macro which can give me the total number of codes
> for each subject/learning area.If it can be done per class fine, but if I
> can paste all worksheets of all classes for grade 8 next to each other and
> the macro can work it out for ALL classes at once it would be great.
> *URGENT solution needed!*
> shaneallen
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
Sam Mathai Chacko

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

2011-10-03 Thread Sam Mathai Chacko

Working with the inbuilt query feature in Excel can get tricky.

Try finding the exact syntax for a MySQL database from *
http://connectionstrings.com/*

And on how to run a query from within Excel, try

*
http://www.excelfox.com/forum/showthread.php?66-Execute-SQL-From-Excel-(VBA)
*

Regards,

Sam Mathai Chacko (GL)

On Mon, Oct 3, 2011 at 10:48 PM, Nitin  wrote:

> Hi All,
>
> I want to be able to run queries within Excel like this:
>
>
> --
>
>  mysql01 = "select * from some_table"
>
>   ActiveWorkbook.Worksheets.Add
>With ActiveSheet.QueryTables.Add(Connection:= _
>"ODBC;DRIVER={MySQL ODBC 3.51
> Driver};SERVER=111.111.11.11;PORT=3306;DATABASE=mydb_name;
> USER=my_user_name;PASSWORD=mypassword;OPTION=3; " _
>, Destination:=Range("A1"))
>.CommandText = Array(mysql01)
>
>
>.Name = "Query from MyDB"
>.FieldNames = True
>.RowNumbers = False
>.FillAdjacentFormulas = False
>.PreserveFormatting = True
>.RefreshOnFileOpen = False
>.BackgroundQuery = True
>.RefreshStyle = xlInsertDeleteCells
>.SavePassword = True
>.SaveData = True
>.AdjustColumnWidth = True
>.RefreshPeriod = 0
>.PreserveColumnInfo = True
>.Refresh BackgroundQuery:=False
>End With
>
> --
>
> When I try to run my VBA module, I get a General ODBC error, which
> does not help at all.
> Can anyone tell me what I am doing wrong here?
>
> Also, I realize there are probably better methods for connecting to
> excel, but for now, if anyone can answer this, that would be
> fantastic! Thanks!
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
Sam Mathai Chacko

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

2011-10-03 Thread Nitin

Hi All,

I want to be able to run queries within Excel like this:

--

  mysql01 = "select * from some_table"

   ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={MySQL ODBC 3.51
Driver};SERVER=111.111.11.11;PORT=3306;DATABASE=mydb_name;
USER=my_user_name;PASSWORD=mypassword;OPTION=3; " _
, Destination:=Range("A1"))
.CommandText = Array(mysql01)


.Name = "Query from MyDB"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
--

When I try to run my VBA module, I get a General ODBC error, which
does not help at all.
Can anyone tell me what I am doing wrong here?

Also, I realize there are probably better methods for connecting to
excel, but for now, if anyone can answer this, that would be
fantastic! Thanks!

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Update sheets on Enter

2011-10-03 Thread dguillett1

I suggest, instead that you use ONLY one sheet and filter as desired. See 
attached and filter by region and then by date. to clear filters use 
data>filter>show all

From: Mr excel 
Sent: Monday, October 03, 2011 4:19 AM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Update sheets on Enter

Hi group,  

   I had been struggling with a solution for a problem from many days..
Recently i had seen a macro in this group which is very much nearer to my 
problem.The sample data is taken from one of the previous examples given by 
guillet.

Going by the same sample data :
the main sheet contains the following columns with data underneath it.
  Date Region Rep Item Units Cost Total 

I want sheets to be added & updated based on each Region and with the date in 
the chronological order.
If a sheet exists already with the REGION NAME there is no need to add the 
sheet with the same name instead new data should be added from the main sheet 
to the respective sheets (REGION wise)

I had attached the worksheet for reference.Kindly help me

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

UPDATE SHEETS_SAS.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Re: SEARCHING DATA WITHIN SHEET WHICH IS SELECTED IN DROP DOWN LIST

2011-10-03 Thread dguillett1

Suggest you try this instead as the formula for currsheet. It will work for 
all. Notice there is NO sheet name in the formula, just the !
=OFFSET(!$A$1,1,0,COUNT(!$A:$A),5)

From: Haseeb Avarakkan 
Sent: Sunday, October 02, 2011 6:03 PM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Re: SEARCHING DATA WITHIN SHEET WHICH IS SELECTED IN 
DROP DOWN LIST

Hi Sourabh,

See the attached. Used a dynamic named range. Always use same format for the 
sheet name. As you used mmmyy format (First 3 letter in the month & 2 digits in 
year). Do not use a space in sheet names.

HTH
Haseeb
-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com
 
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ regarding date format in excel vba

2011-10-03 Thread §»VIPER«§

hi sanjib

i would suggest the below

1. Right Click on Cell and select Format Cells option
2. Custom category in Number tab
3. and type [$-409]dd/mmm/;@  in Type text box




On Mon, Oct 3, 2011 at 1:05 PM, Sanjib Chatterjee <
chatterjee.kolk...@gmail.com> wrote:

> But It is not working sir,
>
> I want to put the value as "25/09/2011" and like to display "25/sep/2011"
>
> regards,
>
> sanjib
>
>
> On Mon, Oct 3, 2011 at 12:58 PM, Swapnil Palande <
> palande.swapni...@gmail.com> wrote:
>
>> Hi Sanjib,
>>
>> The code I had given is vba code it will not change your computer date
>> format.
>>
>> If you want to change date format for particular cell without using code
>> then you can do that using following method
>>
>> 1. Right Click on Cell and select Format Cells option
>> 2. Custom category in Number tab
>> 3. and type "dd/mm/" in Type text box
>>
>> Regards,
>>
>> Swapnil
>>
>> On Mon, Oct 3, 2011 at 12:28 PM, Sanjib Chatterjee <
>> chatterjee.kolk...@gmail.com> wrote:
>>
>>> thank you for your quick responce Swapnil Palande .  But i want to say
>>> is there any option in excel to put the date value like "dd/mm/",without
>>> changing my computer date format.
>>>
>>> Thanking you
>>>
>>> Sanjib
>>>
>>>
>>>
>>> On Mon, Oct 3, 2011 at 11:43 AM, Swapnil Palande <
>>> palande.swapni...@gmail.com> wrote:
>>>
 Hi,

 Use format function.

 ex. MsgBox Format(CDate("03/10/2011"), "dd-mmm-")
 Regards,

 Swapnil.
   On Mon, Oct 3, 2011 at 11:33 AM, Sanjib Chatterjee <
 chatterjee.kolk...@gmail.com> wrote:

> Dear Friends,
>
> would u please help me about the below mentioned problem in VBA EXCEL
>
> I like to put the date value in excel as "dd/mm/".  But like to get
> the output
> as "dd-mmm-"
>
> please help and oblige
>
> Thank you in advance
>
> Ragards,
>
> Sanjib
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @
> http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 <><><><><><><><><><><><><><><><><><><><><><>
 Like our page on facebook , Just follow below link
 http://www.facebook.com/discussexcel

>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>  --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>  --
>
> --
> Some important links f

$$Excel-Macros$$ Update sheets on Enter

2011-10-03 Thread Mr excel

Hi group,

   I had been struggling with a solution for a problem from many
days..
Recently i had seen a macro in this group which is very much nearer to my
problem.The sample data is taken from one of the previous examples given by
guillet.

Going by the same sample data :
the main sheet contains the following columns with data underneath it.
*Date* *Region* *Rep* *Item* *Units* *Cost* *Total* *
*
I want sheets to be added & updated based on each Region and with the date
in the chronological order.
If a sheet exists already with the REGION NAME there is no need to add the
sheet with the same name instead new data should be added from the main
sheet to the respective sheets (REGION wise)

I had attached the worksheet for reference.Kindly help me

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


UPDATE SHEETS.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$

2011-10-03 Thread NOORAIN ANSARI

Dear Azeem,

Please provide query

-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 
On Mon, Oct 3, 2011 at 2:47 PM, Azeem Quraishi (HAB5) wrote:

>  ** **
>
> ** **
>
> * *
>
> 
>
> ** **
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Urgent Help!

2011-10-03 Thread NOORAIN ANSARI

Dear Rashid,

Please try through below link, I hope it will be help to u..

http://www.wikihow.com/Recover-a-Corrupt-Excel-File

-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 
On Mon, Oct 3, 2011 at 10:40 AM, rashid memon wrote:

> Hi,
>
> Can any one help me. My Excel Files are *corupted *Please tell me what i
> can do for *Recover Excel files.*
> *
> *
> *
> *
> Rashid Memon.
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

RE: $$Excel-Macros$$ Splitting of files

2011-10-03 Thread Chidurala, Shrinivas

Thanks Mahesh,

But it is capturing only div sheet, i want to capture all 3 sheets.


Regards,
Shrinivas





From: Mahesh parab [mailto:mahes...@gmail.com]
Sent: Saturday, October 01, 2011 10:38 PM
To: excel-macros@googlegroups.com; Chidurala, Shrinivas [ICG-GTS]
Subject: Re: $$Excel-Macros$$ Splitting of files

Hi Shrinivas

Try :

Sub Mtest()
Dim Rng As Range
Dim ws As Worksheet
Dim shname As String
Dim i As Integer
Dim shn As Long
Dim mx As Variant
Dim x As Integer
Dim LR As Long
Dim sPath As String, sFileName As String
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
Set Rng = Sheets("Div").Range("A6:A" & Cells(Rows.Count, 1).End(xlUp).Row)
Set ws2 = Worksheets.Add(After:=Sheets(Sheets.Count))
With ws2
Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("A1"), unique:=True
.Name = "Temp"
End With
Sheets("Temp").Columns("A").SpecialCells(xlCellTypeBlanks).Delete 
shift:=xlShiftUp
LR = Sheets("Temp").Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LR
Cname = Sheets("Temp").Cells(i, 1)
Set ws2 = Workbooks.Add
mx = Array("Div", "bon", "right")
shn = 1 - LBound(mx)
For x = LBound(mx) To UBound(mx)
Sheets(x + shn).Name = mx(x)
Next x
m = ws2.Name
ThisWorkbook.Activate

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Temp" Then
ws.UsedRange.AutoFilter Field:=1, Criteria1:=Cname
ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy
shname = ws.Name
Application.Goto _
Workbooks(m).Sheets(shname).Cells(1, 1)
ActiveSheet.Paste
ThisWorkbook.Activate
End If
Next ws
ws2.Activate
'Save the new workbook
sPath = ThisWorkbook.Path & "\"
'sPath = "C:\Users\MAHESH\Downloads\Delete\"
sFileName = Cname & ".xls"
Application.DisplayAlerts = False
ws2.SaveAs (sPath & sFileName)
ws2.Close True
ThisWorkbook.Activate
Next i
End Sub

HTH
Mahesh

On Sat, Oct 1, 2011 at 4:34 PM, Chidurala, Shrinivas 
mailto:shrinivas.chidur...@citi.com>> wrote:
Dear All,

I have dump file of 3 sheets which contain the data of some clients in each 
sheet, I want to split the files into client wise and also note the client name 
is all sheet is in column A.

Please advise me to create macro. Find attached sample of dump file and client 
file for your reference.

Regards,
Shrinivas

--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to 
excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ regarding date format in excel vba

2011-10-03 Thread Sanjib Chatterjee

But It is not working sir,

I want to put the value as "25/09/2011" and like to display "25/sep/2011"

regards,

sanjib

On Mon, Oct 3, 2011 at 12:58 PM, Swapnil Palande <
palande.swapni...@gmail.com> wrote:

> Hi Sanjib,
>
> The code I had given is vba code it will not change your computer date
> format.
>
> If you want to change date format for particular cell without using code
> then you can do that using following method
>
> 1. Right Click on Cell and select Format Cells option
> 2. Custom category in Number tab
> 3. and type "dd/mm/" in Type text box
>
> Regards,
>
> Swapnil
>
> On Mon, Oct 3, 2011 at 12:28 PM, Sanjib Chatterjee <
> chatterjee.kolk...@gmail.com> wrote:
>
>> thank you for your quick responce Swapnil Palande .  But i want to say is
>> there any option in excel to put the date value like "dd/mm/",without
>> changing my computer date format.
>>
>> Thanking you
>>
>> Sanjib
>>
>>
>>
>> On Mon, Oct 3, 2011 at 11:43 AM, Swapnil Palande <
>> palande.swapni...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> Use format function.
>>>
>>> ex. MsgBox Format(CDate("03/10/2011"), "dd-mmm-")
>>> Regards,
>>>
>>> Swapnil.
>>>   On Mon, Oct 3, 2011 at 11:33 AM, Sanjib Chatterjee <
>>> chatterjee.kolk...@gmail.com> wrote:
>>>
 Dear Friends,

 would u please help me about the below mentioned problem in VBA EXCEL

 I like to put the date value in excel as "dd/mm/".  But like to get
 the output
 as "dd-mmm-"

 please help and oblige

 Thank you in advance

 Ragards,

 Sanjib

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 <><><><><><><><><><><><><><><><><><><><><><>
 Like our page on facebook , Just follow below link
 http://www.facebook.com/discussexcel

>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><

Re: $$Excel-Macros$$ regarding date format in excel vba

2011-10-03 Thread Swapnil Palande

Hi Sanjib,

The code I had given is vba code it will not change your computer date
format.

If you want to change date format for particular cell without using code
then you can do that using following method

1. Right Click on Cell and select Format Cells option
2. Custom category in Number tab
3. and type "dd/mm/" in Type text box

Regards,

Swapnil

On Mon, Oct 3, 2011 at 12:28 PM, Sanjib Chatterjee <
chatterjee.kolk...@gmail.com> wrote:

> thank you for your quick responce Swapnil Palande .  But i want to say is
> there any option in excel to put the date value like "dd/mm/",without
> changing my computer date format.
>
> Thanking you
>
> Sanjib
>
>
>
> On Mon, Oct 3, 2011 at 11:43 AM, Swapnil Palande <
> palande.swapni...@gmail.com> wrote:
>
>> Hi,
>>
>> Use format function.
>>
>> ex. MsgBox Format(CDate("03/10/2011"), "dd-mmm-")
>> Regards,
>>
>> Swapnil.
>>   On Mon, Oct 3, 2011 at 11:33 AM, Sanjib Chatterjee <
>> chatterjee.kolk...@gmail.com> wrote:
>>
>>> Dear Friends,
>>>
>>> would u please help me about the below mentioned problem in VBA EXCEL
>>>
>>> I like to put the date value in excel as "dd/mm/".  But like to get
>>> the output
>>> as "dd-mmm-"
>>>
>>> please help and oblige
>>>
>>> Thank you in advance
>>>
>>> Ragards,
>>>
>>> Sanjib
>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ PPT

Re: $$Excel-Macros$$ Update sheets on Enter

Re: $$Excel-Macros$$ vba for loops -beginner

$$Excel-Macros$$ Need Help

Re: $$Excel-Macros$$ How To Add A Year Field to a PivotTable Field List

RE: $$Excel-Macros$$ Help, Alert MsgBox Pair duplicate entry in row

Re: $$Excel-Macros$$ Thanks everyone.

Re: $$Excel-Macros$$ Help, Alert MsgBox Pair duplicate entry in row

Re: $$Excel-Macros$$ How To Add A Year Field to a PivotTable Field List

Re: $$Excel-Macros$$ How To Add A Year Field to a PivotTable Field List

Re: $$Excel-Macros$$ Working out of Statistics on Exam Results URGENT solution needed!

Re: $$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

Re: $$Excel-Macros$$ Update sheets on Enter

Re: $$Excel-Macros$$ Update sheets on Enter

Re: $$Excel-Macros$$ Help, Alert MsgBox Pair duplicate entry in row

$$Excel-Macros$$ Thanks everyone.

Re: $$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

Re: $$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

Re: $$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

Re: $$Excel-Macros$$ Working out of Statistics on Exam Results URGENT solution needed!

Re: $$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

$$Excel-Macros$$ Connection String for connecting a MYSQL DB to Excel 2007

Re: $$Excel-Macros$$ Update sheets on Enter

Re: $$Excel-Macros$$ Re: SEARCHING DATA WITHIN SHEET WHICH IS SELECTED IN DROP DOWN LIST

Re: $$Excel-Macros$$ regarding date format in excel vba

$$Excel-Macros$$ Update sheets on Enter

Re: $$Excel-Macros$$

Re: $$Excel-Macros$$ Urgent Help!

RE: $$Excel-Macros$$ Splitting of files

Re: $$Excel-Macros$$ regarding date format in excel vba

Re: $$Excel-Macros$$ regarding date format in excel vba

31 matches

Site Navigation

Mail list logo

Footer information