• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

vba - user enters path and work book name

I am trying to set it where the user enters the workbook path and work book name..but I keep getting an error, saying it cant find it..amI adding/escaping the quotes correctlY?

...code...

dim var_quotes as string
var_quotes = """"

Workbooks.Open Filename:=var_quotes & workbook_path & workbook_name & var_quotes
0
GlobaLevel
Asked:
GlobaLevel
1 Solution
 
Patrick MatthewsCommented:
Why not ask the user to select the file to open with a dialog?

Sub SomeMacro()

    Dim SourcePath As Variant
    Dim SourceWb As Workbook
    Dim SourceWasOpen As Boolean

    SourcePath = Application.GetOpenFilename("Excel files (*.xls*), *.xls*", , _
        "Select file to process", , False)

    If SourcePath = False Then
        MsgBox "No file selected", vbCritical, "Aborting"
        Exit Sub
    End If

    On Error Resume Next
    Set SourceWb = Workbooks(Mid(SourcePath, InStrRev(SourcePath, "\") + 1))
    If Err = 0 Then
        SourceWasOpen = True
    Else
        SourceWasOpen = False
        Err.Clear
        Set SourceWb = Workbooks.Open(SourcePath)
    End If
    On Error GoTo 0

    ' now do whatever it is you have to do

    If Not SourceWasOpen Then SourceWb.Close False

End Sub

Open in new window

0
 
PabilioCommented:
I thin that there is Not need to be double "" when you dim as string...
var_quotes = ""
0
 
Ardhendu SarangiSr. Project ManagerCommented:
Hi,
Can you try something like this below?

- Ardhendu
Sub GetFile()
Dim FLName As String

FLName = Application.GetOpenFilename
Range("a1").Value = FLName

End Sub

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
byundtCommented:
Further to Pabilio's comment, also make sure that there is a path separator between the path and name
0
 
GlobaLevelProgrammerAuthor Commented:
MatthewsPatrick,

I like that route...but how do I then set that so I can add sheets to that workbook they select and name them...
....

Dim sheet_name( 1 to 7) as Variant
Set wb = sourcewb

sheet_name(1) = "test1"
sheet_name(2) = "ttest2"
sheet_name(3) = "try6        "
sheet_name(4) = "testd  "
sheet_name(5) = "tehgyu    "
sheet_name(6) = "Ewsed     "
sheet_name(7) = "ghjk    "


For i = 1 To 7
Set ws = sourcewb.Sheets.Add
ws.Name = "sheet_name(i)"

i = i + 1
Next
0
 
Patrick MatthewsCommented:
GlobaLevel,

With respect, that goes beyond your original question.

In any event, with my example, you end up with an object variable, SourceWb, that represents the selected file.  That object variable gives you an entree to manipulating anything inside that file.  For example:

With SourceWb
    .Worksheets(1).Name = "foo"
    .Worksheets.Add After:=.Worksheets(.Worksheets.Count)
    .Worksheets(.Worksheets.Count).Name = "hee"
End With

Open in new window


Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now