Need help in Function call

Status
Not open for further replies.

Vinvin

New member
Joined
Jan 11, 2014
Messages
12
Reaction score
0
Points
0
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
 
Hi all,

Is this possible ? if so can one give me a piece of code.
 
No one is here to help me ? or No one is willing to help me ????

:help: :help: :help:
 
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
 
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.
 
I ll just create the sample table to show and explain clearly what macro should do.
 
Last edited:
Below table will help u to understand.
Book1 - Sheet name (F_H)
1ABC---WXYZ
2---
3No_1No_2No_3---No_183
41Name1CSE---DEPTName3
52Name2ECE---DEPTName4
Book2 - Sheet name (F_H)
1ABC---WXYZ
2---
3No_1No_2No_3---No_182
41Name5EEE---DEPTName7
52Name6ECE---DEPTNA
Master sheet
1Sheet name
2F_H1Name1CSE---DEPTName53
3F_H2Name2ECE---DEPTName6
4F_H1Name3EEE---DEPTName72
5F_H2Name4ECE---DEPTNA
 
Last edited by a moderator:
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.
 
HI Zack,

Thanks for the heads up.
Find the flow below

1) I need to run macro from master sheet
2) Once macro run, Pop up should raise to ask to select folder contain n no of .xls file
3) File should process one by one till last .xls file
4 ) Example - first file contain the sheet name F_H then function call to process further.
else file contain the sheet name F_D then separate function call to process further.
5) Rest what the code you gave is really helpfull

Thanks
 
I'm sorry, but neither repeating yourself or reporting the post will help get it solved any quicker. The only thing which will get you what you need is you. We need a paragraph or two from you, not a condensed, numbered list of high-level objectives. Details. We need details. Let me give you an example.
3) File should process one by one till last .xls file

This is meaningless to me. Perhaps you could evolve a few sentences, possibly even a few paragraphs, out of this to make it not only legible but understandable. Help us help you.

The code I provided is still quite literally the best I can do for you without further explanation.
 
3.1 ) Folder has n no of .xls file
3.2 ) Macro to open .xls file one by one to check wheather it has sheet name FH(Example) and FD.

Kindly revert back if u need more clarification
 
I'm afraid we will have to close this thread.
@vinvin we'd like to help but you've been asked many times to provide a fuller explanation, instead you keep repeating your numbered list, when you have things a little clearer and a better explanation please start a new thread where we'll do our best to try to help. :)
 
Status
Not open for further replies.
Back
Top