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

  • xxiidark Send Message View User Items on July 17, 2014 09:04 AM

    Paki explain mo nga ano ito, ngtututs, T.T?
    ngtututs kc ako now pero ngruruntime error 9 kc. dndeclare pb sa module yun pg tawag sa sheets. T.T

    Also anong ginagawa ng macro mo? Copy sheet1, sheet2 and sheet3 all to sheet4, isang workbook or file lang ba ito? o dalawa? one from sister company at yong isa sa main?

    -- edited by ram2010 on Jul 17 2014, 01:24 PM
  • ahm ganito po sir yung ngtuturs tutorial po hehehe sorry.

    isang workbook lang po ito.. ano ba mas madali magkakahiwaly ng workbook?.
    ito po example sir.
    ito po yung mga sister company.
    bendai,audi,basicwear. bale 3 sheet po sila and yung pang 4 sheet name is final.
    so gusto ko po sana si bendai,audi,basicwear ay magmemerge po ky final (sheet4) mgsosort po sya and mgtototal.
    possible po ba? thanks po sir ^^
  • Good Day TPC'er,

    Pa help naman - Sorting / re-grouping in another work book.

    details:
    First request:
    - file name: May 16-31 2014.xlsx
    - raw data under "Data File" then need to regroup to another sheet called "Per Name"
    - twist dito is dapat per transaction ng isang name is meron total based on "Actual Pay computation"
    - done.
    - See example in link below.

    Second request:
    - after regrouping, need ko itransfer per name sa isang workbook.
    - twist again, ung file name is kailangan is "Pay confirmation - Name (May16-31 2014) ex. Pay confirmation - Larry Benigno (May 16-31 2014)
    - kung ilan ung name un din ung dami nung file, hanggang sa matapos.

    Hindi ko kasi xa magawa need ko pa talaga mag-aral ng ng excel prog. Sana matulungan nyo ko.


    Here's the link para mas clear (final output): https://drive.google.com/folderview?id=0B_Vrkz2MJm5mcVlKSmR1VHAyWDg&usp=sharing

    TIA!
  • xxiidark Send Message View User Items on July 17, 2014 01:38 PM
    ahm ganito po sir yung ngtuturs tutorial po hehehe sorry.

    isang workbook lang po ito.. ano ba mas madali magkakahiwaly ng workbook?.
    ito po example sir.
    ito po yung mga sister company.
    bendai,audi,basicwear. bale 3 sheet po sila and yung pang 4 sheet name is final.
    so gusto ko po sana si bendai,audi,basicwear ay magmemerge po ky final (sheet4) mgsosort po sya and mgtototal.
    possible po ba? thanks po sir ^^

    Pag isang workbook lang mas ok sya kasi isang file lang ang e open mo at manipulate ka na lang sa sheets.
    Pwede itong gawan ng macro, pero dapat uniform ang formats sa supplied 3 sheets, para hindi complicated ang macro.
    Same column kung saan kunin ang total, at at what column mag sort ng data. Madali itong e manual, unless ina update ang 3 sheets
    every hour or less.

    -- edited by ram2010 on Jul 17 2014, 03:08 PM
  • ah oky ty sir.
    ito po kc format sir
    Name Salary ERShare EEShare Total.
    yung isosort ko po yung name.
    possible po ba na si name pg nasort lahat ng nsa column nya masasama din sa pagsosort?
    thanks ^^
  • Before sorting, select all data, then sort it by name for example, all data in other columns will follow doon sa sorted name column.
  • @ sir Ram and khedfreck thanks po sa help niyo.

    what if for example. iba ibang klase ng text style siya pero yung gusto ko lang maextract po is yung number. for ex:

    Cell A
    asdasd 1230 sdad
    da 3450 sd
    a 6780 b
    dsd sdsd 9101
    asd sdsd 9888 asd sds

    Cell B (results)
    1230
    3450
    6780
    9101
    9888

    -- edited by Dstar015 on Jul 17 2014, 05:20 PM
  • @ sir Ram and khedfreck thanks po sa help niyo.

    what if for example. iba ibang klase ng text style siya pero yung gusto ko lang maextract po is yung number. for ex:

    Cell A
    asdasd 1230 sdad
    da 3450 sd
    a 6780 b
    dsd sdsd 9101
    asd sdsd 9888 asd sds

    Cell B (results)
    1230
    3450
    6780
    9101
    9888

    Yong nasa Cell A, pwede mo itong gamitan ng text to column such that mahihiwalay sila per line. Saka mo na e extract ang number doon sa mga individual cells. Once ma extract na ang mga numbers, pwede mo na uli itong e combine sa isang cell.

    Hindi ito madali, pero pwede siyang gawan ng paraan.

    Pag marami ang mga ito, macro na lang siguro.
  • johnToT Send Message View User Items on July 17, 2014 01:40 PM
    Good Day TPC'er,

    Pa help naman - Sorting / re-grouping in another work book.

    details:
    First request:
    - file name: May 16-31 2014.xlsx
    - raw data under "Data File" then need to regroup to another sheet called "Per Name"
    - twist dito is dapat per transaction ng isang name is meron total based on "Actual Pay computation"
    - done.
    - See example in link below.

    Second request:
    - after regrouping, need ko itransfer per name sa isang workbook.
    - twist again, ung file name is kailangan is "Pay confirmation - Name (May16-31 2014) ex. Pay confirmation - Larry Benigno (May 16-31 2014)
    - kung ilan ung name un din ung dami nung file, hanggang sa matapos.

    Hindi ko kasi xa magawa need ko pa talaga mag-aral ng ng excel prog. Sana matulungan nyo ko.


    Here's the link para mas clear (final output): https://drive.google.com/folderview?id=0B_Vrkz2MJm5mcVlKSmR1VHAyWDg&usp=sharing

    TIA!

    Naintindihan ko ang gusto mong mangyari, I will create such a macro, but not sure about the time.
  • @ ram2010

    Thanks Ram,

    kahit simple macro lang - lam ko complicated and may kahirapan xa gawin, Basta maraming salamat, any help and effort is really appreciated and thanks for your time answering may post :)

    again many thanks :)
  • johnToT Send Message View User Items on July 18, 2014 12:33 PM
    @ ram2010

    I have completed the macro for creating files. I tested this and it worked.

    Create a module and paste the macro below to the file you uploaded. The active sheet should be the "per name" before running this macro.
    If you found problems saving this macro I will send the excel file where this macro is already saved.

    A bit complicated since while looping thru the main sheet searching for name and total, you have to change focus and paste the data of a new file created then loop again on the main sheet searching for other names for saving. See some comments in macro, you can uncomment the msgbox I commented if you want to see the process.

    There are limits in the macro, for scanning the rows and loops you can increase those if needed. The created files will be in the location where your main file is located. This is displayed in the msgbox when you start running the macro.


    Sub create_files()
    ' This macro will create and save files based on the data
    ' in the active sheet. The range to be saved in individual files
    ' are marked from "KeyDevID" to "TOTAL" in this sheet.

    Dim mainFilePath As String
    Dim mainWorkSheetName As String

    Dim title As String
    Dim name As String
    Dim filename As String
    Dim xfilename As String

    Dim r As Integer, c As Integer, r1 As Integer, c1 As Integer
    Dim f As Boolean
    Dim limit As Integer, maxLimit As Integer, maxRow As Integer

    limit = 0
    maxLimit = 1000
    maxRow = 1000

    r = 0

    ' Location of this file
    mainFilePath = Application.ActiveWorkbook.Path
    MsgBox "This file is located in: " & mainFilePath & ". New files will also be created in this path."

    mainWorkSheetName = ActiveSheet.name
    MsgBox "Active sheet name is: " & mainWorkSheetName

    ' Outside loop to check name and total, this is done since
    ' the macro changes from main workbook to created workbook
    Do While (limit < maxLimit)

    ' Flag if we found a name and a total
    f = False

    ' Debug
    'MsgBox "The Value of overall incrementer r after the do-while loop is " & r

    'loop thru the rows in column B
    For x = r + 1 To maxRow

    v = Cells(x, 2).Value
    If v = "KeyDevID" Then
    r = x
    c = 2

    ' Debug
    'MsgBox "We found a " & Cells(r, c).Value & " at row " & x

    ' Continue to search and find "TOTAL" with offset 27 from column 2
    For y = x + 1 To maxRow
    v1 = Cells(y, 2).Offset(0, 27).Value
    If v1 = "TOTAL" Then
    r1 = y
    c1 = 29

    ' Debug
    'MsgBox "We found a total of " & Cells(r1, c1).Offset(0, 2).Value

    f = True
    Exit For

    End If

    Next y

    If f Then
    Exit For
    End If

    End If

    Next x

    If f Then

    title = "Pay confirmation - "
    name = ActiveSheet.Cells(r + 1, 3)

    ' Debug
    MsgBox "Name: " & name & ", Total: " & Cells(r1, c1).Offset(0, 2).Value

    filename = ActiveWorkbook.name
    xfilename = filename

    If Right(filename, 4) = ".xls" Then
    xfilename = Left(filename, Len(filename) - 4)
    ElseIf Right(filename, 5) = ".xlsx" Then
    xfilename = Left(filename, Len(filename) - 5)
    ElseIf Right(filename, 5) = ".xlsm" Then
    xfilename = Left(filename, Len(filename) - 5)
    End If

    savedFile = mainFilePath & "\" & title & name & " (" & xfilename & ")" & ".xlsx"

    ' Debug
    MsgBox "Next filename to be saved is: " & title & name & " (" & xfilename & ")" & ".xlsx"

    'Add the workbook
    Workbooks.Add
    ActiveWorkbook.SaveAs (savedFile)

    ' Remember the filename of this work book
    file_copy = ActiveWorkbook.name

    ' Activate the orig file, since after creation of new file, we lost focus
    Workbooks(filename).Activate

    ' Select orig main sheet
    Worksheets(mainWorkSheetName).Select

    ' Copy the data for this specific person
    Range(Cells(r, c), Cells(r1, c1 + 5)).Copy

    ' Change workbook focus and paste to sheet1 of this workbook
    Workbooks(file_copy).Activate
    ActiveSheet.Paste

    ' Save the new file and close
    ActiveWorkbook.Save
    ActiveWorkbook.Close

    limit = limit + 1

    ' Debug
    'MsgBox "The value of r after closing of new workbook is " & r
    'MsgBox "The value of limit is " & limit

    Else
    ' Exit the loop since f is not found

    ' Debug
    'MsgBox "Exiting the do-while loop"

    Exit Do

    End If

    Loop

    ' Debug
    MsgBox "Done!! number of files created: " & limit

    End Sub


    -- edited by ram2010 on Jul 20 2014, 01:14 AM
  • pa OT mga bossing ito kc yung code ko
    pero my lumalabas syang error na .
    "Either BOF or EOF is true, or the current records has been deleted. Requested operation requires a current record" vb6 po gamit kong software.
    ito po yung code
    Set rst = New ADODB.Recordset
    rst.Open "select Name to tbl_Philhealth = '" & txtname.Text & "'", con, adOpenKeyset, adLockOptimistic
    x = 0
    If txtname.Text = Empty Then
    x = x + 1
    End If
    If x = 0 Then

    txtname.Text = rst!Name
    cmbsalbra.Text = rst!Salarybracket
    txtsal.Text = rst!Salary
    txter.Text = rst!ERShare
    txtee.Text = rst!EEShare
    txttot.Text = rst!Total
    rst.Update
    rst.MoveNext


    MsgBox "Record has been succesfully updated!", vbInformation, "Saved!"
    Unload Me
    Unload frmmain
    frmmain.Show
    frmmain.LV.Refresh
    End If

    If x = 1 Then
    MsgBox "Please Fill up all the information needed!", vbInformation, "Incomplete!"
    End If
    ty ^^
  • xxiidark Send Message View User Items on July 21, 2014 02:42 PM

    Try posting it in vb6 sub-forum.
  • @ sir Ram and khedfreck thanks po sa help niyo.

    what if for example. iba ibang klase ng text style siya pero yung gusto ko lang maextract po is yung number. for ex:

    Cell A
    asdasd 1230 sdad
    da 3450 sd
    a 6780 b
    dsd sdsd 9101
    asd sdsd 9888 asd sds

    Cell B (results)
    1230
    3450
    6780
    9101
    9888


    Yong nasa Cell A, pwede mo itong gamitan ng text to column such that mahihiwalay sila per line. Saka mo na e extract ang number doon sa mga individual cells. Once ma extract na ang mga numbers, pwede mo na uli itong e combine sa isang cell.

    Hindi ito madali, pero pwede siyang gawan ng paraan.

    Pag marami ang mga ito, macro na lang siguro.



    Try mo to: http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/

    Medyo madugo yung formula.
  • a big thanks Sir Ram :)

    Ill will try to follow your instruction and let you know.

    looking at the structure of the codes yeah its complicated nga.

    pero i think pwd natin simplihan just by eliminating my first request(regroup to another sheet called "Per Name") using "Group" function in excel or if not manual sorting like in the Per name sheet (previous process)

    again thanks a lot and ill keep you posted :) YGPM
  • khedfreak Send Message View User Items on July 21, 2014 10:48 PM

    [...]

    Try mo to: http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/

    Medyo madugo yung formula.

    Hindi nga madali yan :).

    Dapat e try ito ni Dstar015.

Who's Online

1033 active users within the last minute, 584 members, 449 guests.
Our newest member is mytddruyo
Click here to see online members.

Browse Items

More »

Search TipidPC


New Items for Sale

New Want to Buys

Active Items for Sale

Active Want to Buys