Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Need help in Function call

  1. #1

    Need help in Function call



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Everyone,

    I'm looking for help. so any one can suggest and help me on this.

    call CopyDataFromSheet(workbookFileName, "F_H", sOutputWorkbookName)
    Public Sub CopyDataFromSheet(workbookFileName As String, sSheetName As String, sOutputWorkbookName As String)


    above two lines of code does some function call with three parameters.
    I need to do the same function call in different Method ? Is this possible ? If it so , help me

  2. #2
    Hi all,

    Is this possible ? if so can one give me a piece of code.

  3. #3
    No one is here to help me ? or No one is willing to help me ????


  4. #4
    Explain better what you want

  5. #5
    Hi Conti
    Let me explain you clearly



    Steps:
    1) Browse for .xls file ( i have a code)
    2) Macro to open the workbook and if sheet name contain "F_H" then it should start processing.
    3) Then it should unprotect the sheet
    4) If there is a value in B4 then it should copy from B4 to W4 and paste in the another workbook named as Kavin
    5) If there is a value in B5 to B20(maybe B100) then it should copy from B5 to W20 and paste in the workbook named as Kavin
    6) once 4 & 5 done, it should look for the value in Z3. If value exists then it should paste the Z3 value in the workbook named as Kavin in Z4

  6. #6
    No one is here ready to help me ?

  7. #7
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    Your directions are somewhat ambiguous.

    To check if a workbook is open or a worksheet exists I use the following functions...

    Code:
    Function ISWBOPEN(ByVal wkbName As String) As Boolean
        On Error Resume Next
        ISWBOPEN = CBool(Len(Workbooks(wkbName).Name) <> 0)
        On Error GoTo 0
    End Function
    
    
    Function WSEXISTS(ByVal wksName As String, Optional WKB As Workbook) As Boolean
        If WKB Is Nothing Then
            If ActiveWorkbook Is Nothing Then Exit Function
            Set WKB = ActiveWorkbook
        End If
        On Error Resume Next
        WSEXISTS = CBool(Len(WKB.Worksheets(wksName).Name) <> 0)
        On Error GoTo 0
    End Function
    The rest I believe would just be working out an If/Then structure. Hard to help when we don't have a clear understanding or the rest of your code.
    Regards,
    Zack Barresse

  8. #8
    I ll just create the sample table to show and explain clearly what macro should do.
    Last edited by Vinvin; 2014-01-14 at 12:23 AM.

  9. #9
    Below table will help u to understand.
    Book1 - Sheet name (F_H)
    1 A B C --- W X Y Z
    2 ---
    3 No_1 No_2 No_3 --- No_18 3
    4 1 Name1 CSE --- DEPT Name3
    5 2 Name2 ECE --- DEPT Name4
    Book2 - Sheet name (F_H)
    1 A B C --- W X Y Z
    2 ---
    3 No_1 No_2 No_3 --- No_18 2
    4 1 Name5 EEE --- DEPT Name7
    5 2 Name6 ECE --- DEPT NA
    Master sheet
    1 Sheet name
    2 F_H 1 Name1 CSE --- DEPT Name5 3
    3 F_H 2 Name2 ECE --- DEPT Name6
    4 F_H 1 Name3 EEE --- DEPT Name7 2
    5 F_H 2 Name4 ECE --- DEPT NA
    Last edited by Zack Barresse; 2014-02-07 at 05:57 PM.

  10. #10
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    Sorry, but that's not clear at all. I still have no idea what you want. An actual explanation would help.

    So without knowing more, and going by your numbered list of tasks, you could use something like this...

    Code:
    Option Explicit
    
    Sub dunno()
    
    
        Const FindSheet             As String = "F_H"
        Const DestinationFile       As String = "Kavin"
        Const DestinationSheet      As String = "Master sheet"
        Const SheetPassword         As String = "password for sheet goes here"
        Const FilesFilter           As String = "Description (*.xls), *.xls"
    
    
        Dim SourceWB                As Workbook
        Dim SourceWS                As Worksheet
        Dim DestinationWB           As Workbook
        Dim DestinationWS           As Worksheet
        Dim SourceWasOpen           As Boolean
        Dim SheetIsThere            As Boolean
        Dim SheetProtected          As Boolean
        Dim LastRow                 As Long
        Dim FindFile                As String
        Dim SourceName              As String
        Dim SourcePath              As String
    
    
        If ISWBOPEN(DestinationFile) = True Then
            Set DestinationWB = Workbooks(DestinationFile)
            If WSEXISTS(DestinationSheet, DestinationWB) = True Then
    
    
                FindFile = Application.GetOpenFilename(FilesFilter)
                If FindFile = "False" Then
                    'user pressed cancel
                    Exit Sub
                End If
    
    
                Call TOGGLEEVENTS(False)
    
    
                SourcePath = Left(FindFile, InStrRev(FindFile, "\"))
                SourceName = Right(FindFile, Len(FindFile) - Len(SourcePath))
    
    
                SourceWasOpen = ISWBOPEN(SourceName)
                If SourceWasOpen = True Then
                    Set SourceWB = Workbooks(SourceName)
                Else
                    Set SourceWB = Workbooks.Open(FindFile)
                End If
    
    
                SheetIsThere = WSEXISTS(FindSheet, SourceWB)
                If SheetIsThere = True Then
                    'sheet exists
                    Set SourceWS = SourceWB.Worksheets(FindSheet)
                    SheetProtected = SourceWS.ProtectContents
                    If SheetProtected Then Call UNPROTECTSHEET(SourceWS, SheetPassword)
                    If SourceWS.ProtectContents = False Then
                        'unprotected, do stuff
                        LastRow = SourceWS.Cells(SourceWS.Rows.Count, "B").End(xlUp).Row
                        'check for value in B4
                        If Len(SourceWS.Range("B4").Value) > 0 Then
                            SourceWS.Range("B4:W4").Copy DestinationWS.Range("B4:W4")
                        End If
                        'check for value in B5 to last data in column B
                        If WorksheetFunction.CountA(SourceWS.Range("B5:B" & LastRow)) > 0 And LastRow >= 5 Then
                            SourceWS.Range("B5:W" & LastRow).Copy DestinationWS.Range("B5:W" & LastRow)
                        End If
                        'check for value in Z3
                        If Len(SourceWS.Range("Z3").Value) > 0 Then
                            SourceWS.Range("Z3").Copy DestinationWS.Range("Z4")
                        End If
                    Else
                        'protected still, password didnt' work, do nothing
                    End If
                Else
                    'sheet does not exist, do nothing
                End If
    
    
                If SourceWasOpen = True Then
                    If SheetIsThere = True Then
                        SourceWB.Close SaveChanges:=True
                    Else
                        SourceWB.Close SaveChanges:=False
                    End If
                End If
    
    
                Call TOGGLEEVENTS(True)
    
    
            Else
                MsgBox "Worksheet not found in destination file."
            End If
        Else
            MsgBox "Destination workbook not open."
        End If
    
    
    End Sub
    
    
    Sub UNPROTECTSHEET(ByVal WKS As Worksheet, ByVal TryPassword As String)
        If WKS.ProtectContents = False Then Exit Sub
        On Error Resume Next
        WKS.Unprotect TryPassword
        On Error GoTo 0
    End Sub
    
    
    Sub TOGGLEEVENTS(ByVal blnState As Boolean)
        'Originally written by Zack Barresse
        Application.DisplayAlerts = blnState
        Application.EnableEvents = blnState
        Application.ScreenUpdating = blnState
        If blnState Then Application.CutCopyMode = False
        If blnState Then Application.StatusBar = False
    End Sub
    
    
    Function ISWBOPEN(ByVal wkbName As String) As Boolean
        On Error Resume Next
        ISWBOPEN = CBool(Len(Workbooks(wkbName).Name) <> 0)
        On Error GoTo 0
    End Function
    
    
    Function WSEXISTS(ByVal wksName As String, Optional ByVal WKB As Workbook) As Boolean
        If WKB Is Nothing Then
            If ActiveWorkbook Is Nothing Then Exit Function
            Set WKB = ActiveWorkbook
        End If
        On Error Resume Next
        WSEXISTS = CBool(Len(WKB.Worksheets(wksName).Name) <> 0)
        On Error GoTo 0
    End Function
    There is much I assumed here, so you would need to check the code, constants, etc.
    Regards,
    Zack Barresse

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •