Results 1 to 7 of 7

Thread: Shell problem

  1. #1
    Join Date
    Oct 2004
    Posts
    10

    Angry Unanswered: Shell problem

    I am using the Shell command to open excel files. These excel files are on our server. The problem I am finding is that if the path has spaces in especially the file itself e.g purchase order.xls, the shell command will not work it will only try and open the file name up to the first space. For the example given it will try and open a file called purchase.xls

    Is there anyway to use the shell command to open a file which has spaces? I have treied to replace the space with a _ and this did not work. To get around it at the moment I have made sure that all directories and file names I am trying to open do not have spaces.

    Please help

  2. #2
    Join Date
    May 2005
    Posts
    119
    You could try using "purcha~1.xls" as your filename.

    Is your need specific to using the Shell command? If not, you might consider using the following code instead.

    Dim objExcel As Object
    Set objExcel = CreateObject("excel.application")
    objExcel.Workbooks.Open "{pathandfilename.xls}"

    Hope this helps!
    Krista

  3. #3
    Join Date
    Oct 2004
    Posts
    10
    What code do I write if I need to open a folder which has spaces in the name?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Same "code" you would have to write to access it from command line. Encapsulate the string in quotes.

    "ThisIsHowToPassACommandWithNoSpaces"
    """This is how to pass commands WITH spaces - notice the quotes"""
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Krista327
    Is your need specific to using the Shell command? If not, you might consider using the following code instead.

    Dim objExcel As Object
    Set objExcel = CreateObject("excel.application")
    objExcel.Workbooks.Open "{pathandfilename.xls}"
    Not a big deal but one of my little soap box moments. Krista is spot on - I would use automation too. However I would use early binding (google early and late binding to see why). Set a reference (Tools--> References in the VB IDE) to Microsoft Excel x.0 Object Library. Then use the following code:

    Code:
     
    Dim objExcel As New Excel.Application
    Dim exWB as Excel.Workbook
     
    objExcel.Visible = True
     
    Set exWB = objExcel.Workbooks.Open "\\Server\My Folder With A Space\MyWB.xls"
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Oct 2004
    Posts
    10
    Sorry, what I actually meant by the folders is that I would like to open a folder that contains certain files. So I won't actually be referencing a file, just the folder name. It will need to open the folder then the user selects the file.

  7. #7
    Join Date
    May 2005
    Posts
    119
    OK - here's what I would do. I have not yet figured out a way to have the Excel browser open; however, I have worked around this in the past using filesearch. Basically, I use the filesearch method to dump the filenames into a table (filenames in this example). Then I populate a list box with the filenames table, where users can simply click on the filename to open it.

    Here's my filesearch code:
    Sub listfiles(filename, path)
    Dim ssql As String, i As Integer
    With Application.FileSearch
    .NewSearch
    .LookIn = path
    .filename = filename
    .MatchTextExactly = True
    If .Execute() > 0 Then
    DoCmd.SetWarnings False
    For i = 1 To .foundfiles.Count
    ssql = "insert into filenames values('" & Right(.foundfiles(i), Len(.foundfiles(i)) - Len((path)) - 1) & "')"
    DoCmd.RunSQL (ssql)
    Next i
    DoCmd.SetWarnings True
    Else
    MsgBox "There were no files found."
    End If
    End With
    End Sub

    call: listfiles("*.xls","c:\excel")

    Does this help?
    Krista

Posting Permissions

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