Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: Problem in accessing file name from Input box

  1. #1
    Join Date
    12th April 2012
    Location
    Chennai India
    Posts
    8

    Problem in accessing file name from Input box

    I am attaching a code where I get the details of folder and file names and based on that do a series of actions on two files. The code gets stuck in the step "Workbooks(MyFilename).Activate". Basically the file name "MyFilename" from Open_File function is not getting passed to the " OpenWorkbookFromFolder" sub.

    VB:
    Sub OpenWorkbookFromFolder() 
         'To open files and / or folders using the FollowHyperlink method
         'Open Folder
        Dim strFolder As String 
        Dim strXLSFile As String 
        Dim strXLSFilepath As String 
         'Open excel workbook=source
        Open_File 
        Msgbox "My File is" & "  " & MyFilename 
         'Open excel workbook activate workbook and worksheet to copy from
        Workbooks(MyFilename).Activate 
        Sheets("Sheet1").Activate 
        Range("A1:M3").Select 
        Selection.Copy 
         
         'Open excel workbook -destination
        Open_File 
        Workbooks(MyFilename).Activate 
        Sheets("Sheet1").Activate 
        Range("A1").Select 
        ActiveSheet.Paste 
        Rows("4:11").Select 
        Application.CutCopyMode = False 
        Selection.Delete Shift:=xlUp 
    End Sub 
     
    Function Open_File() As String() 
        Dim sFile As String 
        Dim MyFilename As String 
        Dim MyFolder As String 
        MyFolder = InputBox("folder name", "enter folder name", "C:\") 
         
         'this is just to check the input box selects correctly
        Msgbox "My Folder is" & "  " & MyFolder 
         
        MyFilename = InputBox("Enter file number", "enter file name", "Test") & ".xls" 
         
         'this is just to check the input box selects correctly
        Msgbox "My File  is" & "  " & MyFilename 
        sFile = MyFolder & MyFilename 
         
        ActiveWorkbook.FollowHyperlink Address:=sFile, NewWindow:=True 
         
         'Workbook.Open Filename:=sFile
    End Function 
    
    
    any suggestions on where the problem could be?
    Last edited by Dave Hawley; April 14th, 2012 at 19:03.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    Re: Problem in accessing file name from Input box

    Use the GetOpenFileName Method because of typos. How to Use the GetOpenFilename Method

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: October 28th, 2010, 22:32
  2. Accessing large file across WAN: Windows XP; Word 2007.
    By Dave Hawley in forum Office 2007 Migration and Application Compatibility
    Replies: 0
    Last Post: December 12th, 2009, 14:45
  3. Accessing the file system with VBA
    By MrC in forum EXCEL HELP
    Replies: 1
    Last Post: September 21st, 2005, 00:41
  4. Problem Accessing Clipboard
    By studentVB in forum EXCEL HELP
    Replies: 1
    Last Post: April 4th, 2005, 17:00

Bookmarks

Posting Permissions

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