date:20110217

Re: $$Excel-Macros$$ columns into rows, with a twist

2011-02-17 Thread ashish koul

send a mail on excel-macros@googlegroups.com and attach the workbook

On Fri, Feb 18, 2011 at 5:43 AM, RFisher  wrote:

> Hi Ashish,
>  Sorry I'm really new to google groups. How do I "send" the sample
> workbook? Can I post it here somewhere?
> Thanks and sorry to ask so many questions.
> Ryan
>
> On Feb 16, 8:19 pm, ashish koul  wrote:
> > can you please send  the sample workbook
> >
> >
> >
> >
> >
> > On Thu, Feb 17, 2011 at 3:03 AM, RFisher  wrote:
> > > Hi everyone,
> > >  It's great to find a list with such a helpful group. I have a
> > > question. I received some recent weather data (precipitation in mm)
> > > for weather stations in Canada; however, the file isn't quite
> > > formatted for how I need it in order to analyse it. The excel file is
> > > formatting like the following (Station ID is the identification number
> > > for the station, year is the year the measurements were made, month is
> > > the month (1-12), decimals is a constant column, and the Day1-Day31
> > > are maximum precipitation on each day of the month (see following data
> > > outline):
> > > StationID YearMonth Decimals Day1   Day 2   Day3
> > > Day4Day 31
> > > 23056  2003  13 0
> > > 12.2 13   7   10
> > > 23056  2003  23 1
> > > 5   12 0.6  20
> >
> > > There are unfortunately several thousand rows and I need the table to
> > > look like the following:
> > > StationID Year   Month  Decimals DayPrecip
> > > 23056  20031 3   10
> > > 23056  20031 3   2   12.2
> > > 23056  20031 3   313
> > > 23056  20031 3   4 7
> > > ...
> > > 23056  2003 13   31   10
> > > 23056  2003 231 1
> > > etc
> >
> > > Note that there are several different stations, over 7 years and for
> > > all months (January to December).
> >
> > > Thanks for any help. I hope that's enough information that you might
> > > be able to provide a way to this automatically with a macro or some
> > > other excel functions.
> > > Thanks!
> > > Ryan
> >
> > > --
> >
> > >
> ------
> > > 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 athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://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
> >
> > --
> > *Regards*
> > * *
> > *Ashish Koul*
> > *akoul*.*blogspot*.com 
> > *akoul*.wordpress.com 
> > My Linkedin Profile 
> >
> > P Before printing, think about the environment.- Hide quoted text -
> >
> > - Show quoted text -
>
> --
>
> --
> 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
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

-- 
--
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 face

Re: $$Excel-Macros$$ columns into rows, with a twist

2011-02-17 Thread RFisher

Hi Ashish,
 Sorry I'm really new to google groups. How do I "send" the sample
workbook? Can I post it here somewhere?
Thanks and sorry to ask so many questions.
Ryan

On Feb 16, 8:19 pm, ashish koul  wrote:
> can you please send  the sample workbook
>
>
>
>
>
> On Thu, Feb 17, 2011 at 3:03 AM, RFisher  wrote:
> > Hi everyone,
> >  It's great to find a list with such a helpful group. I have a
> > question. I received some recent weather data (precipitation in mm)
> > for weather stations in Canada; however, the file isn't quite
> > formatted for how I need it in order to analyse it. The excel file is
> > formatting like the following (Station ID is the identification number
> > for the station, year is the year the measurements were made, month is
> > the month (1-12), decimals is a constant column, and the Day1-Day31
> > are maximum precipitation on each day of the month (see following data
> > outline):
> > StationID     Year    Month     Decimals     Day1   Day 2   Day3
> > Day4Day 31
> > 23056          2003      1                3             0
> > 12.2     13       7           10
> > 23056          2003      2                3             1
> > 5       12     0.6          20
>
> > There are unfortunately several thousand rows and I need the table to
> > look like the following:
> > StationID     Year   Month      Decimals     Day    Precip
> > 23056          2003    1                 3               1        0
> > 23056          2003    1                 3               2       12.2
> > 23056          2003    1                 3               3        13
> > 23056          2003    1                 3               4         7
> > ...
> > 23056          2003     1                3               31       10
> > 23056          2003     2                3                1         1
> > etc
>
> > Note that there are several different stations, over 7 years and for
> > all months (January to December).
>
> > Thanks for any help. I hope that's enough information that you might
> > be able to provide a way to this automatically with a macro or some
> > other excel functions.
> > Thanks!
> > Ryan
>
> > --
>
> > ------
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com 
> *akoul*.wordpress.com 
> My Linkedin Profile 
>
> P Before printing, think about the environment.- Hide quoted text -
>
> - Show quoted text -

-- 
--
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 Pull out Ref type Value base on process DOJ & Process Name

2011-02-17 Thread maulik desai

Thanks Ashish Sir,

For such nice solution & time

Sorry for late reply

On Tue, Feb 8, 2011 at 8:23 PM, ashish koul  wrote:

> try this
>
>
> On Mon, Feb 7, 2011 at 9:10 PM, maulik desai wrote:
>
>> Hello sir
>>
>> please provide me the correction in your macro actully i just want to
>> chang the column no as per my database in macro,i have tried but not
>> get the proper out that is why i want u to just change the column no
>> based on attached file in my prev mail in macro and send it back that
>> it
>>
>> thanks
>>
>>
>>
>>
>> On 2/5/11, maulik desai  wrote:
>> > hello Sir,
>> >
>> > Sorry for delay replay please find the dummy my database sheet with the
>> > same
>> > column no which is apper in database
>> >
>> > i want the same macro or formula as a output for attached file.
>> >
>> > Once again Congrates Ashish sir.
>> >
>> > On Thu, Feb 3, 2011 at 2:16 PM, ashish koul 
>> wrote:
>> >
>> >>
>> >> yo have sent me the same file
>> >>
>> >> sent me this one
>> >>
>> >> header - column no.
>> >> Name - 4
>> >> process doj -63
>> >> process - 61
>> >> ref type -87
>> >> process doj 1 -92
>> >> process1 -90
>> >> ref type 1-116
>> >> process doj 2-123
>> >> process 2 - 121
>> >> ref type 2 -147
>> >> process doj 3-154
>> >> process 3- 152
>> >> ref type 3-178
>> >> final status-236
>> >>
>> >>
>> >>
>> >>   On Wed, Feb 2, 2011 at 9:09 PM, maulik desai
>> >> wrote:
>> >>
>> >>> Hello sir please find the file as requested
>> >>>
>> >>> -- Forwarded message --
>> >>> From: ashish koul 
>> >>> Date: Wed, 2 Feb 2011 12:35:27 +0530
>> >>> Subject: Re: $$Excel-Macros$$ How to Pull out Ref type Value base on
>> >>> process DOJ & Process Name
>> >>> To: excel-macros@googlegroups.com
>> >>>
>> >>> see if it helps
>> >>>
>> >>> Sub add_data()
>> >>> Application.ScreenUpdating = False
>> >>> Application.DisplayAlerts = False
>> >>> Dim i, j, k, s As Long
>> >>> s = 2
>> >>> Sheets(2).Range("a2:d" & Sheets(2).Range("a1").End(xlDown).Row).Clear
>> >>> For i = 2 To Sheets(1).Range("A2").End(xlDown).Row
>> >>> For j = 2 To Sheets(1).Range("A1").End(xlToRight).Column
>> >>> If UCase(Application.WorksheetFunction.Trim(Left(Sheets(1).Cells(1,
>> >>> j).Value, 11))) = UCase("Process DOJ") Then
>> >>>
>> >>> Sheets(2).Cells(s, 1).Value = Sheets(1).Cells(i, 1).Value
>> >>> Sheets(2).Cells(s, 2).Value = Sheets(1).Cells(i, j).Value
>> >>> Sheets(2).Cells(s, 3).Value = Sheets(1).Cells(i, j + 1).Value
>> >>> Sheets(2).Cells(s, 4).Value = Sheets(1).Cells(i, j + 2).Value
>> >>>
>> >>> If UCase(Application.WorksheetFunction.Trim(Sheets(1).Cells(1, j +
>> >>> 3).Value)) = UCase("Final Status") Then
>> >>> Sheets(2).Cells(s, 4).Value = Sheets(1).Cells(i, j + 3).Value
>> >>> End If
>> >>>
>> >>> s = s + 1
>> >>> End If
>> >>> Next j
>> >>> s = s + 1
>> >>> Next i
>> >>>  Application.ScreenUpdating = True
>> >>> Application.DisplayAlerts = True
>> >>> End Sub
>> >>>
>> >>>
>> >>>
>> >>> On Tue, Feb 1, 2011 at 10:20 AM, maulik desai > >>> >wrote:
>> >>>
>> >>> >
>> >>> > Hi Guru,
>> >>> >
>> >>> > Kindly Provide the solution for attached file
>> >>> >
>> >>> > My problem is:
>> >>> >
>> >>> > I am having 2 workbook , in 1st Excel Workbook (Database) I m having
>> >>> data
>> >>> > with multipul Process name ,doj & their respective ref type
>> >>> > what I want that in 2nd excel sheet (working File) I am having diff
>> >>> format
>> >>> > (mention above) ,with individual Entries as per process name &
>> process
>> >>> DOJ
>> >>> > Kindly proive me the solution that how can I get the Ref type as per
>> >>> > process doj & Process name
>> >>> > Kindly note that some time the process name & process doj is same
>> but
>> >>> their
>> >>> > ref type is diff so in that case I want the last or latest entry's
>> ref
>> >>> type
>> >>> > as out put as mention in above case (mention in yellow)
>> >>> >
>> >>> > --
>> >>> > Thanks & Regards,
>> >>> > Maulik Desai
>> >>> > 9967363926
>> >>> >
>> >>> > --
>> >>> >
>> >>> >
>> >>>
>> --
>> >>> > 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
>> >>> >
>> >>>
>> >>>
>> >>>
>> >>> --
>> >>> *Regards*
>> >>> * *
>> >>> *Ashish Koul*
>> >>> *akoul*.*blogspot*.com 
>> >>> *akoul*.wordpress.com 
>> >>> My Linkedin Profile <
>> ht

Re: $$Excel-Macros$$ Diff excel Sheet based on Name & test

2011-02-17 Thread maulik desai

Thanks mahesh,

there is some problem with the code

when i click on Create workbook button it give me  error in code

ActiveWorkbook.SaveAs (sPath & sFileName)

also border part also not execute

request u to kindly check the same

And my earlier mail have mentioned that each process has is own tests so i
just want that after extracting data to diff sheets i just want the test
columns according to that process only

In this exp
Emp name - Test columns wanted in diff sheets are
A- Test1 & 2
V-Test 3,4,5
C-Test 1&3
D-Test1 & 2
E-Test1, 2,3

In short i dont want test scores with NA just want pass columns

Hope u understand.

On Wed, Feb 16, 2011 at 12:58 PM, Mahesh parab  wrote:

> Hi Maulik
>
> find attach, prepared as per my understanding
> 1. it will create separate worksheet for each value in column J (emp name &
> date)
> 2. it will crate separate workbook for each value in column J, create a
> folder name Output in thisworkbooks path (where u save the file)
> for each new workbook it will border the used cells
>
>
>
> On Wed, Feb 16, 2011 at 2:14 AM, maulik desai wrote:
>
>> Hi Gurus,
>>
>> I Just want to create Diff excel sheet based onn Emp name & sheet name
>> should be "emp name & date".there are diff test are available for each
>> process I also want to update that process test scores in respective sheet
>> (kindly see the sheets for more info),i am having very large excel database
>> & every week process names are changed so request you to kindly proive the
>> solution which creates the automatically sheet name & filter the test
>> columns & also i want to create that sheet as a individual workbook & need
>> to saved automatically on desktop with same sheet name & date.
>>
>> --
>> Thanks & Regards,
>> Maulik Desai
>> 9967363926
>>
>> --
>>
>> --
>> 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
>



-- 
Thanks & Regards,
Maulik Desai
9967363926

-- 
--
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$$ Import .csv & match records

2011-02-17 Thread Dave Bonallack

I think someone is using my name in vain - unless there are two Dave's in the 
world - and I don't know how that could be...
Dave.

Date: Thu, 17 Feb 2011 09:15:24 -0800
From: schreiner_p...@att.net
Subject: Re: $$Excel-Macros$$ Import .csv & match records
To: excel-macros@googlegroups.com

Piece of cake...
I can probably write it in 20 minutes.
=
Option Explicit
Dim Col_Array_Code1, Col_Array_Code2, Col_Array_YR, Col_Array_MM
Dim Col_Array_CoName, Col_Array_Dept, Col_Array_Qty, Col_Array_Amt
Dim Code1, Code2, YR, MM
Dim CoName, Dept, Qty, Amt
Dim fso

Public Const ForReading = 1, ForWriting = 2, ForAppending = 3
Sub ReadData()
Dim CSVFile, R, f, str, StrArray
CSVFile = "P:\DAM\WI_JAN_2011.csv"
Set_Defaults
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(CSVFile, ForReading)
Do While Not f.atendofstream
RecCnt = RecCnt + 1
If (RecCnt Mod 100 = 0) Then Application.StatusBar = "Searching 
Commercial Archives for: " & MatNo & " : " & RecCnt
str = f.readline
StrArray = Split(str, ",")

Code1 = Trim(StrArray(Col_Array_Code1))
Code2 = Trim(StrArray(Col_Array_Code2))
YR = Trim(StrArray(Col_Array_YR))
MM = Trim(StrArray(Col_Array_MM))
CoName = Trim(StrArray(Col_Array_CoName))
Dept = Trim(StrArray(Col_Array_Dept))
Qty = Trim(StrArray(Col_Array_Qty))
Amt = Trim(StrArray(Col_Array_Amt))

Loop
End Sub
Sub Set_Defaults()
Col_Array_Code1 = 0
Col_Array_Code2 = 1
Col_Array_YR = 2
Col_Array_MM = 3
Col_Array_CoName = 4
Col_Array_Dept = 5
Col_Array_Qty = 6
Col_Array_Amt = 7

End Sub
===

However, something you said makes me think it might not be as easy to PLACE the 
data.

In the sheet for each month,
you have 200 "accounts"
and the combination of Code1 & Code2 defines a "record".

You want to read the .csv file and copy the records to the appropriate 
"accounts"...

That implies that the sheets in the monthly_totals workbook 
have a specific "layout" that you want to maintain.

I need to know what that layout is so that I can "find" the appropriate 
"account".

I think I'm going to need a copy of the Monthly_totals.xls workbook.
and, it would help if I had one or more of the .csv files.
Otherwise, I have to spend more time making up fake data than actually writing 
code!

Paul

From: Dave 
To: MS EXCEL AND VBA MACROS 
Sent: Thu, February 17, 2011 9:32:15 AM
Subject: $$Excel-Macros$$ Import .csv & match records

I  am on Excel 2003,. I have a spreadsheet with 12 monthly tabs one
for
every month of the year January, February … December.. This file is
called montly_totals.xls and is located in P:\DAM\WI_FTP.  There are
a
total of 200 accounts set up in each of these monthly tabs. Each of
these accounts starts with a code in A1 & B1 . These are 4 character
codes like below:  They are sorted by Code 1.
Code 1 + Code 2 uniquely identify a record.

Code1  Code2

0845MTRA
1016
1017LEGL
16051605
1605OSLA
0001
0002

Every month I download a comma delimited file named as WI_JAN_2011,
WI_FEB_2011 and so on till WI_DEC_2011.  This file is located in P:
\DAM
\WI_FTP This file may contain 10 to 150 accounts with updated
information for that specific month.  They also have the same codes
as
above. The format of the comma delimited file is :

Code1,Code2,YR,MM,CoName,Dept_name,Quantity,$amount

0010,,11,01,Iron works,Grills  ,
00130,003448635,

(they are all on one line)

Currently every month I have to open each monthly tab  and manually
copy and paste the correct Information from the  comma delimited file
into the exact columns in my montly_totals.xls spreadsheet. The
columns I copy are : YR,MM,CoName,Dept_name,Quantity,$amount

I want a macro that would read this comma delimited file and insert
these  new monthly  updated values to  the  correct account numbers.
I was thinking that it could key on Code1 & Code2  fields. Once there
is a match between the codes i.e Code1+Code 2 in the montly_totals
spreadsheet and the comma delimited file than the macro could insert
the complete record (YR,MM,CoName,Dept_name,Quantity,$amount ) for
that match in the columns  C, D,E,F,G, & going dow the rows.

If there is no match than a new record should be inserted maintaing
the sort order.

Thanks in advance,
Dave

-- 
--
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.quick

Re: $$Excel-Macros$$ New Web Query get data from existing open IE Browser instead of opening new browser window

2011-02-17 Thread ashish koul

 write
on error resume next
 on the line above in the code which is giving errors see if that helps



On Thu, Feb 17, 2011 at 8:17 PM, RCGUA  wrote:

> With Microsoft Excel 2003, using the "Web Query" feature, I record a
> macro, put the URL of the website into the New Web Query and that
> opens a new instance of Internet Explorer.  Using the Web Query, I go
> to a specific table within the tables on the web page and select the
> table that I want to get the data from and the Web Query copies the
> data and pastes it into Excel.  The website that I am getting the data
> from requires a login and password.  I wrote some VBA code to send the
> username and password and that works at first, however, if I use this
> web query several times in one day and after using the Web Query
> several times, I am logged into that website many times and I begin
> getting error messages and the Web Query does not grab any data.
>
> Is it possible for the Web Query to to switch to the existing, already
> opened and logged into IE and grab that data and paste it into Excel,
> instead of opening a new browser window every time the query is run?
>
> Or, if that is not possible, is it possible, to run the macro that
> uses the login and password, but then, after copying the data, have
> the query automatically logout and close the new instance of IE?
>
> --
>
> --
> 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
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

-- 
--
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$$ Import .csv & match records

2011-02-17 Thread Paul Schreiner

Piece of cake...
I can probably write it in 20 minutes.
=
Option Explicit
Dim Col_Array_Code1, Col_Array_Code2, Col_Array_YR, Col_Array_MM
Dim Col_Array_CoName, Col_Array_Dept, Col_Array_Qty, Col_Array_Amt
Dim Code1, Code2, YR, MM
Dim CoName, Dept, Qty, Amt
Dim fso
    
Public Const ForReading = 1, ForWriting = 2, ForAppending = 3
Sub ReadData()
    Dim CSVFile, R, f, str, StrArray
    CSVFile = "P:\DAM\WI_JAN_2011.csv"
    Set_Defaults
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.OpenTextFile(CSVFile, ForReading)
    Do While Not f.atendofstream
    RecCnt = RecCnt + 1
    If (RecCnt Mod 100 = 0) Then Application.StatusBar = "Searching 
Commercial Archives for: " & MatNo & " : " & RecCnt
    str = f.readline
    StrArray = Split(str, ",")
    
    Code1 = Trim(StrArray(Col_Array_Code1))
    Code2 = Trim(StrArray(Col_Array_Code2))
    YR = Trim(StrArray(Col_Array_YR))
    MM = Trim(StrArray(Col_Array_MM))
    CoName = Trim(StrArray(Col_Array_CoName))
    Dept = Trim(StrArray(Col_Array_Dept))
    Qty = Trim(StrArray(Col_Array_Qty))
    Amt = Trim(StrArray(Col_Array_Amt))
    
    Loop
End Sub
Sub Set_Defaults()
    Col_Array_Code1 = 0
    Col_Array_Code2 = 1
    Col_Array_YR = 2
    Col_Array_MM = 3
    Col_Array_CoName = 4
    Col_Array_Dept = 5
    Col_Array_Qty = 6
    Col_Array_Amt = 7
    
End Sub
===


However, something you said makes me think it might not be as easy to PLACE the 
data.

In the sheet for each month,
you have 200 "accounts"
and the combination of Code1 & Code2 defines a "record".

You want to read the .csv file and copy the records to the appropriate 
"accounts"...

That implies that the sheets in the monthly_totals workbook 
have a specific "layout" that you want to maintain.

I need to know what that layout is so that I can "find" the appropriate 
"account".

I think I'm going to need a copy of the Monthly_totals.xls workbook.
and, it would help if I had one or more of the .csv files.
Otherwise, I have to spend more time making up fake data than actually writing 
code!
 
 
Paul
 





From: Dave 
To: MS EXCEL AND VBA MACROS 
Sent: Thu, February 17, 2011 9:32:15 AM
Subject: $$Excel-Macros$$ Import .csv & match records

I  am on Excel 2003,. I have a spreadsheet with 12 monthly tabs one
for
every month of the year January, February … December.. This file is
called montly_totals.xls and is located in P:\DAM\WI_FTP.  There are
a
total of 200 accounts set up in each of these monthly tabs. Each of
these accounts starts with a code in A1 & B1 . These are 4 character
codes like below:  They are sorted by Code 1.
Code 1 + Code 2 uniquely identify a record.

Code1  Code2


0845    MTRA
1016    
1017    LEGL
1605    1605
1605    OSLA
    0001
    0002


Every month I download a comma delimited file named as WI_JAN_2011,
WI_FEB_2011 and so on till WI_DEC_2011.  This file is located in P:
\DAM
\WI_FTP This file may contain 10 to 150 accounts with updated
information for that specific month.  They also have the same codes
as
above. The format of the comma delimited file is :


Code1,Code2,YR,MM,CoName,Dept_name,Quantity,$amount

0010,,11,01,Iron works    ,Grills                      ,
00130,003448635,

(they are all on one line)

Currently every month I have to open each monthly tab  and manually
copy and paste the correct Information from the  comma delimited file
into the exact columns in my montly_totals.xls spreadsheet. The
columns I copy are : YR,MM,CoName,Dept_name,Quantity,$amount


I want a macro that would read this comma delimited file and insert
these  new monthly  updated values to  the  correct account numbers.
I was thinking that it could key on Code1 & Code2  fields. Once there
is a match between the codes i.e Code1+Code 2 in the montly_totals
spreadsheet and the comma delimited file than the macro could insert
the complete record (YR,MM,CoName,Dept_name,Quantity,$amount ) for
that match in the columns  C, D,E,F,G, & going dow the rows.

If there is no match than a new record should be inserted maintaing
the sort order.

Thanks in advance,
Dave



-- 
--

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/discus

Re: $$Excel-Macros$$ Nos. of Sundays between two days

2011-02-17 Thread Rajasekhar Praharaju

thanks alot its awesome

On Wed, Feb 16, 2011 at 8:48 PM, ashish koul  wrote:

> j1 start date
> j2 end date
>
> monday =I
> F(TEXT(J1,"")<>"Monday",NETWORKDAYS(J1,J2)/5,INT(NETWORKDAYS(J1,J2)/5)+1)
>
> saturday =  INT(((J2-J1)-NETWORKDAYS(J1,J2))/2)
>
>   On Wed, Feb 16, 2011 at 7:10 PM, Rajasekhar Praharaju <
> rajasekhar.prahar...@gmail.com> wrote:
>
>> hi ashish,
>>
>> Can you please help to find out how many mondays and saturdays are
>> present for the same above query.
>> 01/16/2011 to 02/16/20111
>>
>>
>> Thanks,
>> Raj
>>
>>
>>
>> On Tue, Feb 15, 2011 at 12:46 PM, N Pradhan 
>> wrote:
>>
>>>  Thank you very much for the prompt reply Sir.
>>>
>>>
>>>
>>> - Original Message -
>>>
>>> *From:* ashish koul 
>>> *To:* excel-macros@googlegroups.com
>>> *Sent:* Monday, February 14, 2011 8:17 PM
>>> *Subject:* Re: $$Excel-Macros$$ Nos. of Sundays between two days
>>>
>>> start date in a1
>>>
>>> and end date in a2
>>>
>>> no of sundays  =INT(((A2-A1)-NETWORKDAYS(A1,A2))/2)+1
>>>
>>> On Mon, Feb 14, 2011 at 6:04 PM, N Pradhan 
>>> wrote:
>>>
  Dear Sir,

 Formula required for nos. of sundays between two days.
 e.g. if an employee has joined on 18/7/2010, how many sundays between
 18/7/2010 and 31/01/2011.

 Thanks & Regards.

 --

 --
 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

>>>
>>>
>>>
>>> --
>>> *Regards*
>>> **
>>> *Ashish Koul*
>>> *akoul*.*blogspot*.com 
>>> *akoul*.wordpress.com 
>>> My Linkedin Profile 
>>>
>>>
>>> P Before printing, think about the environment.
>>>
>>>
>>> --
>>>
>>> --
>>> 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
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com 
> *akoul*.wordpress.com 
> My Linkedin Profile 
>
>
> P Before printing, think about the environment.
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.

$$Excel-Macros$$ New Web Query get data from existing open IE Browser instead of opening new browser window

2011-02-17 Thread RCGUA

With Microsoft Excel 2003, using the "Web Query" feature, I record a
macro, put the URL of the website into the New Web Query and that
opens a new instance of Internet Explorer.  Using the Web Query, I go
to a specific table within the tables on the web page and select the
table that I want to get the data from and the Web Query copies the
data and pastes it into Excel.  The website that I am getting the data
from requires a login and password.  I wrote some VBA code to send the
username and password and that works at first, however, if I use this
web query several times in one day and after using the Web Query
several times, I am logged into that website many times and I begin
getting error messages and the Web Query does not grab any data.

Is it possible for the Web Query to to switch to the existing, already
opened and logged into IE and grab that data and paste it into Excel,
instead of opening a new browser window every time the query is run?

Or, if that is not possible, is it possible, to run the macro that
uses the login and password, but then, after copying the data, have
the query automatically logout and close the new instance of IE?

-- 
--
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$$ Import .csv & match records

2011-02-17 Thread Dave

I  am on Excel 2003,. I have a spreadsheet with 12 monthly tabs one
for
every month of the year January, February … December.. This file is
called montly_totals.xls and is located in P:\DAM\WI_FTP.  There are
a
total of 200 accounts set up in each of these monthly tabs. Each of
these accounts starts with a code in A1 & B1 . These are 4 character
codes like below:  They are sorted by Code 1.
Code 1 + Code 2 uniquely identify a record.

Code1   Code2


0845MTRA
1016
1017LEGL
16051605
1605OSLA
0001
0002


Every month I download a comma delimited file named as WI_JAN_2011,
WI_FEB_2011 and so on till WI_DEC_2011.  This file is located in P:
\DAM
\WI_FTP This file may contain 10 to 150 accounts with updated
information for that specific month.  They also have the same codes
as
above. The format of the comma delimited file is :


Code1,Code2,YR,MM,CoName,Dept_name,Quantity,$amount

0010,,11,01,Iron works ,Grills   ,
00130,003448635,

(they are all on one line)

Currently every month I have to open each monthly tab  and manually
copy and paste the correct Information from the  comma delimited file
into the exact columns in my montly_totals.xls spreadsheet. The
columns I copy are : YR,MM,CoName,Dept_name,Quantity,$amount


I want a macro that would read this comma delimited file and insert
these  new monthly  updated values to  the  correct account numbers.
I was thinking that it could key on Code1 & Code2  fields. Once there
is a match between the codes i.e Code1+Code 2 in the montly_totals
spreadsheet and the comma delimited file than the macro could insert
the complete record (YR,MM,CoName,Dept_name,Quantity,$amount ) for
that match in the columns  C, D,E,F,G, & going dow the rows.

If there is no match than a new record should be inserted maintaing
the sort order.

Thanks in advance,
Dave



-- 
--
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$$ Partial Matching HELP PLEASE! SOS! SOS!!

2011-02-17 Thread Umed Singh

On 2/16/11, Aindril De  wrote:
> Hi Mike,
> Please use:
> =VLOOKUP(A2,Sheet1!B:B,1,0) instead.
>
> Regards,
> Andy
>
> On Wed, Feb 16, 2011 at 2:21 AM, MikeMikeMike
> wrote:
>
>> I am struggling with this and wondering if you could help..
>>
>> Worksheet 1 Column A & B
>> Keyword Category
>> telecom Telecom
>> tele com#N/A
>>
>> Worksheet 2 Column A & B
>> termcategory
>> telecom Telecom
>> telcom  Telecom
>> tele comTelecom
>>
>> What I want to do is pull the category name from worksheet 2 on to
>> worksheet 1.
>>
>> =VLOOKUP("*"&A2&"*",Sheet1!B:B,1,0)
>>
>> Is not working...any ideas?
>>
>> --
>>
>> --
>> 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
>


-- 
 Umed Singh

-- 
--
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$$ Refresh cell formula using VBA

2011-02-17 Thread Dave Bonallack


Hi,
Sorry - didn't see your attachment.
Link your button to the following macro, which can just live in a module:
Sub refreshRange()
Range("B40:B54").Calculate
End Sub

Alternatively, you could do away with the button and have the macro fire 
whenever you change B39. This macro would live in the VBA sheet window:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$39" Then
For Each c In Range("B40:B54")
If c.HasFormula = True Then c.Calculate
Next c
End If
End Sub

Regards - Dave

 
> Date: Thu, 17 Feb 2011 12:52:37 +0700
> From: squall.l...@gmail.com
> To: excel-macros@googlegroups.com
> Subject: $$Excel-Macros$$ Refresh cell formula using VBA
> 
> Hi experts,
> 
> Please help to solve my problem regarding refreshing cell containing 
> formula/function using button (vba).
> My apologize if someone has already post the same problem before, but 
> believe me it's my best attempt to search it beforehand to avoid double 
> post ;)
> 
> 
> What I need is a method or code in VBA to seemlessly "F2" the cell and 
> hitting "Enter Button" to refresh my cell.
> Please find the attached example of my request.
> 
> Thanks in advance,
> 
> 
> ~Aduh~
> 
> -- 
> --
> 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$$ Refresh cell formula using VBA

2011-02-17 Thread Dave Bonallack


Hi Aduh,
I assume that you have the Calculation set to Manual.
One way would be to use a selection change event that detects if the selected 
cell has a formula in it, then calculates just that cell.
The following code goes into the VBA sheet window:
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 And Target.HasFormula = True Then Target.Calculate
End Sub
 
Clicking in any cell will updaye it if it has a formula.

Let me know if I have understood you correctly.
Regards - Dave.

 
> Date: Thu, 17 Feb 2011 12:52:37 +0700
> From: squall.l...@gmail.com
> To: excel-macros@googlegroups.com
> Subject: $$Excel-Macros$$ Refresh cell formula using VBA
> 
> Hi experts,
> 
> Please help to solve my problem regarding refreshing cell containing 
> formula/function using button (vba).
> My apologize if someone has already post the same problem before, but 
> believe me it's my best attempt to search it beforehand to avoid double 
> post ;)
> 
> 
> What I need is a method or code in VBA to seemlessly "F2" the cell and 
> hitting "Enter Button" to refresh my cell.
> Please find the attached example of my request.
> 
> Thanks in advance,
> 
> 
> ~Aduh~
> 
> -- 
> --
> 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$$ columns into rows, with a twist

Re: $$Excel-Macros$$ columns into rows, with a twist

Re: $$Excel-Macros$$ How to Pull out Ref type Value base on process DOJ & Process Name

Re: $$Excel-Macros$$ Diff excel Sheet based on Name & test

RE: $$Excel-Macros$$ Import .csv & match records

Re: $$Excel-Macros$$ New Web Query get data from existing open IE Browser instead of opening new browser window

Re: $$Excel-Macros$$ Import .csv & match records

Re: $$Excel-Macros$$ Nos. of Sundays between two days

$$Excel-Macros$$ New Web Query get data from existing open IE Browser instead of opening new browser window

$$Excel-Macros$$ Import .csv & match records

Re: $$Excel-Macros$$ Partial Matching HELP PLEASE! SOS! SOS!!

RE: $$Excel-Macros$$ Refresh cell formula using VBA

RE: $$Excel-Macros$$ Refresh cell formula using VBA

13 matches

Site Navigation

Mail list logo

Footer information