>

User Login

TipidPC.com is the largest online IT Community in the Philippines. Have something to sell or share? Sign up for an account now. It's absolutely free!

Forum Topic

excel macro programming

  • test
  • Boss ram, nagmessage ako sayo kaso parang di ata pumasok, nagpasalamat lang ulit ako sa naitulong mo sa spreadsheet na binuild mo, mejo nakuha ko naman ang code meron lang akong katanungan, dito pa sa product tracking e walang limit kahit dumami ang row kasi diba continues ang update ko dito.

    Final Tracking.
    http://www.mediafire.com/view/407ng2m58y1anpc/TV_Tracking_-_28_APRIL_15.xlsm

    saka pwede ko bang palitan yung customer ID ng room no. na lang bale don papasok lahat nung room na naissuehan ko ng tv at pati serial no e lalabas don na nasa ganitong room tong serial na na inissue ko. parang sa tingin ko mahirap pera lam ko walang impossible sayo. saka dito sa tracking na to inalis ko na lng yung column para sa size kasi di nagaappear sa report yung size kaya bale ginawa ko na lang e sinama sya sa brand pra automatic na gagawa ng bagong column sa report kada pasok ko ng ibang brand na me size. pagme libreng oras ka na lang sana mahelp mo ko dto at kung san ko ipapasok yung code na bago para don sa pagchange ko ng room na sa isang column jan ng customer ID. ako nga pla si einjelhart dati di ko na kasi maaccess yung account ko dati kya gumawa ako bago. salamat ulit
  • patulong naman po.

    I want to group items by a specific date rate(eg 1-9,10-15, 16-23, 24-30/31 of the month). Data is being entered as 01-May(walang year). Di ko makuha kung pano gawin sa IF. TIA
  • Batusai Send Message View User Items on May 27, 2015 04:41 PM

    Gamit ka ng pivot table, then group mo yong date sa column by days with equal interval.
    1-4
    5-8
    9-12

    4-1=3
    8-5=3
    12-9=3

    Interval is equal.
  • Isang method is alamin mo kung ano yung value ng date sa excel, example ay 01/01/1900 is 1 at 05/30/2015 is 42154.

    Di ko alam bat if gusto mo pero pwede mo mano manuhin yan, sample ay =if(x=1,1,if(x=2,1,if(x=3,1...)
    Tas sort mo ng by 1 kasi pasok yung 1-9 sa criteria ng 1, tas sa 2 para sa 10-15 and so on, kung medyo magulo may mas madali pa.

    Pwede mo iconditional formatting tas sort mo by kulay naman, tas meron pang isang madali.

    Pwede mo ifilter tas paste filtered fields sa ibang sheet.

    O kaya yung sabe ni boss ram na pivot.
  • djhurt Send Message View User Items on May 02, 2015 11:43 AM

    saka pwede ko bang palitan yung customer ID ng room no. na lang bale don papasok lahat nung room na naissuehan ko ng tv at pati serial no e lalabas don na nasa ganitong room tong serial na na inissue ko.

    Ito ginawa ko, para hindi complikado ang revision, madali lang mag retrieve ng info.
    Doon sa product sheet, I added a column called "Room no." after the column "Brand". So dito papasok kung anong room no. ang product based from its latest status na nasa sheet din na ito.
    When you run the macro called "UpdateProductStatus()" ma update din ang "Room no." column.

    So here is the code to update the Room no. in product sheet. Just add this code near the end of the macro, like the following.

    Sub UpdateProductStatus()
    ...
    ...
    ...

    ' Update the info of what room is the product located:
    ' Scan column 1 of product sheet
    For i = 2 To 65000
    If Sheets("product").Cells(i, 1).Value = Empty Then
    Exit For
    End If

    psn = Sheets("product").Cells(i, 1).Value 'product s/n

    ' Scan the log sheet
    For j = 2 To 65000
    If Sheets("log").Cells(j, 2).Value = psn Then
    Sheets("product").Cells(i, 6).Value = Sheets("log").Cells(j, 8).Value
    Exit For
    End If
    Next j
    Next i


    End Sub


    Itong info sa Room no. ay kinuha ko from "Issued to" column in log sheet. So make sure na sensible ang entries mo doon sa "Issued to" columns. May nakita akong Issued status pero blank yong "Issued To" entry.

    -- edited by ram2010 on May 30 2015, 09:43 AM
  • Here is a simpler solution, just add 1 statement to existing code. This will report correctly the location of the item based from latest status.
    Add that bolded statement.

    Sub UpdateProductStatus()
    ...

    'scan Log sheet in reverse
    For j = 65000 To 2 Step -1
    If Sheets("Log").Cells(j, 2).Value = Value Then
    'update status, date and name in Product sheet
    Sheets("Product").Cells(i, 2).Value = Sheets("Log").Cells(j, 3).Value
    Sheets("Product").Cells(i, 3).Value = Sheets("Log").Cells(j, 4).Value
    Sheets("Product").Cells(i, 4).Value = Sheets("Log").Cells(j, 5).Value
    Sheets("Product").Cells(i, 6).Value = Sheets("Log").Cells(j, 8).Value
    Exit For
    End If
    Next j

    ...

    end sub
  • thanks Ram2010 and Skabuts

    actually sa sharepoint ko sana gagamitin yung formula. What I did as a work around was to ask those doing the encoding to tag each entry as 1-8, 9-15 and so on. It works but it's tedious.
  • not sure if this is the right thread to ask this question.. pero ill post it here anyways, baka may mga bihasa sa excel dyan na makakatulong sa gusto ko sana mangyari.

    sa planta namin, we use aspen process explorer infoplus 21 (ip21 for short) for monitoring equipment status or operation parameters.
    for those not familiar with ip21, software ito na pwedeng gamitin to view live data at pwede rin ireview yung history ng parameters using graphicsl charts/trendings etc.

    I use excel with the 'Aspen' Add-in to pull data from the ip21 server. live data yun na nirerefresh ko every 5 or so seconds using macro, para makita ko overall status or operation nung plant/equipment na binabantayan ko.

    now gusto ko sana mangyari is ibato yung data na yun to google sheets. so everyone in our office can view the data. malaki maitutulong nito para sa mga bossing, isang open lang nila sa isang workbook, kita na nila overall status ng planta. hindi na magtatanong parati sa mga operators.

    Hindi ako IT or CS major so wala ako clear picture kung pano ba isolve yung problem. meron lang ako siguro idea kung paano pero not sure if it is possible.

    ito pa lang naiisip ko with very limited researching done.. ongoing pa ang pag research,
    excel(using macro) > sql or web database > google sheet import from database

    ill post again for more details of the problem. for now ito lang muna. baka may naka encounter na sa ganitong situation e. Thanks!
  • jpaolosy Send Message View User Items on June 06, 2015 11:48 PM

    One safe way is to just request your IT department of a space for shared folder. Doon mo na lang e share ang mga files, excel, pics, charts etc.
    This is what I have experienced working on big overseas and local corporations. Information like this should be controlled by the company.
  • I could already do that. But it would be time consuming in my part and hindi pa rin makakamit ang goal ko.
    My goal is to let certain users with access rights to the said google sheet, view live data to have a general overview of the plant/operation status. Data can be 1 minute old.

    Meron ako naisip kagabi na solution, I would try it on Monday once I have access to the workstation with the ip21 software installed.
    I will try to create custom functions that pulls data directly from the ip21 server, using scripts on google sheets.
    I can review yung VB script na ginagamit nung ip21 and convert ko into javascript which google sheet uses. Long shot yung gusto ko mangyari, pero it would eliminate the need for uploading it to a database.
    Data from ip21 > google sheets
  • Right I understand, you want online solution, live info, just make sure data security is not a problem in the long run.
    Also make sure the exported data by ip21 to the google sheets is correct (thought about internet connections problems) so that any decisions made
    by some people based on the data online is dependable. Some sort of data verifications. Create a function press the button
    and all parameter values are verified to be within a certain margin.
  • Hi sir ram2010 sorry mali ung nacopy paste ko para ipost..

    https://www.dropbox.com/s/ga6m7b84gnmxqor/AdvancedUserform.xls?dl=0


    sir anjan po sa dropbox ung sample na nakita ko dataform ata tawag jan sir ram. need ko lang po pano kaya lagyan ng picture upload po yan para employee database magyayari po sir ram.. maedit rin kaya yan sir ram.

    thanks

    -- edited by fmstatic14 on Jun 16 2015, 09:58 AM
  • good day. wala ako alam sa excel macro, ano ma-suggest niyong good guide in learning?
  • fmstatic14 Send Message View User Items on June 15, 2015 04:23 PM
    hi sir ram2010 can you please help me, my nakita kasi ako sa internet na template database lang po xia pero edit ko na lang po ung form kung masundan ko..
    sa form na to sir ram eh need ko lang po ng picture upload na dalawa kasi before and after po xia. na mgsasabay sa pag save ng data makikita nyo po dito sa dropbox ung excel po sir ram..


    kasi ngpapagawa ang boss ko ng excel database my picture xia na need pero sobrang bagal pg ng attach ako ng picture kaya naicp ko mg hanap ng ganyan, help naman sir ram


    thanks po

    Sorry hindi ko maintindihan mga sinusulat mo.
  • gen0 Send Message View User Items on June 15, 2015 06:05 PM
    good day. wala ako alam sa excel macro, ano ma-suggest niyong good guide in learning?

    Kailangan marunong ka sa excel bago sa mag excel-macro. After that pwede na. Maraming source sa internet.
    Start from simple task, like writing value to cell, or start from recording a macro.
  • Hi sir ram2010 sorry mali ung nacopy paste ko para ipost..

    https://www.dropbox.com/s/ga6m7b84gnmxqor/AdvancedUserform.xls?dl=0


    sir anjan po sa dropbox ung sample na nakita ko dataform ata tawag jan sir ram. need ko lang po pano kaya lagyan ng picture upload po yan para employee database magyayari po sir ram.. maedit rin kaya yan sir ram yan gawa na yan. nakita ko lang kasi yan sa google po..


    thanks
  • need ko lang po pano kaya lagyan ng picture upload po yan para employee database magyayari po sir ram

    Anong ibig mong sabihin dito?
    Ano itong picture upload?
  • sir ram parang sa profile picture sa facebook po. my browse button tapos pag ng save eh kasama xia sa data na nilagay po. para pag ngsearch na makiki2ta pa natin ung picture kung sino ung nsa details p.
  • Wala akong facebook account. Pass muna ako dito, baka may iba dyan na makatulong.
  • huh.. sir ram. upload picture lang po kakalabasan po sir ram.. database with picture details lang po sir..huhu
  • magandang araw po...

    ask lang ako pano gawin yung macro para i-move yung set of files (PDF) into a folder with the same name of the file at dun nakalagay na din yung file(PDF)

    eg

    File1
    PDF1.pdf
    PDF2.pdf
    PDF3.pdf

    to

    File1
    PDF1\PDF1.pdf
    PDF2\PDF2.pdf
    PDF3\PDF3.pdf

    nalilito lang ako sa move move eh.. paki assist naman ho ^_^ TIA

    -- edited by FEDER1C0 on Jun 18 2015, 02:56 PM
  • Kailangan marunong ka sa excel bago sa mag excel-macro. After that pwede na. Maraming source sa internet.
    Start from simple task, like writing value to cell, or start from recording a macro.

    marunong naman ako mag excel, i use it to make template and tally sheets, up to graphs. di ko nga lang siya ma-utilize fully dahil zero ako sa macro, last programming language i learned was basic (ms-dos).

    hirap maghanap ng matinong macro tutorial online. hehe

    -- edited by gen0 on Jun 18 2015, 09:08 PM
  • FEDER1C0 Send Message View User Items on June 18, 2015 02:56 PM


    Try this, you can add some other rules na lang or looping if you want...

    Sub Move_Folder()

    'Moves entire folder from SourcePath to DestinPath

    Dim FSO As Object
    Dim SourcePath As String
    Dim DestinPath As String

    Set FSO = CreateObject("scripting.filesystemobject")

    SourcePath = "C:\Users\jamazi\Desktop\folderA"
    DestinPath = "C:\Users\jamazi\Desktop\folderB\"

    FSO.movefolder SourcePath, DestinPath

    End Sub



    Sub Move_Files()

    'Copies a SPECIFIC file from one path to another

    Dim SourceFile As String
    Dim DestinPath As String
    Dim FileName As String

    SourceFile = "C:\Users\jamazi\Desktop\folderA\"
    DestinPath = "C:\Users\jamazi\Desktop\folderB\"
    FileName = "Notes.txt"

    FileCopy SourceFile & FileName, DestinPath & FileName

    End Sub
  • ^^^^^^^^^^^^^^

    thanks a many

Who's Online

609 active users within the last minute, 296 members, 313 guests.
Our newest member is yembers
Click here to see online members.

Browse Items

More »

Search TipidPC


New Want to Buys

Active Items for Sale

Active Want to Buys