Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
byundtCommented:
Further to Pabilio's comment, also make sure that there is a path separator between the path and name
0
 
GlobaLevelAuthor 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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