Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

Works on one computer but not on others

I have a script that works on one computer but when I copied and pasted the folder into a USB and then copied it to the desktop of another computer it keeps saying it can't find the file "Names.xlsx" even though it is in the folder.
Wow.vbs
0
Dier02
Asked:
Dier02
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
Dier02Author Commented:
Would the drive references be a part of the problem? (C:D - the one with issues is on a network and the computer the script works on is a stand-alone.)
WOW-issue.PNG
0
 
sunezapaCommented:
so the user is called rmoul6 on both comouters, and the full path is correct? It look a bit strange with the folder "desktop" both on D: and C: ;-)

it it is only a one-time move you could fix it in the code,
but it may be better to open the excel-file with a relative, and not a absolute path,
 - what is the content of your script, around the failing line number 9?
0
 
Bill PrewCommented:
If you do the following from a command prompt on the computer where it fails, what does it show?

DIR "c:\users\rmoul6\desktop\wow project\names.xlsx"

~bp
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jimyXCommented:
You can use the WshSpecialFolders object to get the path to the Desktop/MyDocuments so the top lines in your file will be:

Set objShell = CreateObject("WScript.Shell")

Dim WSHShell, DesktopPath, MyDocumentsPath
Set WSHShell = WScript.CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")
MyDocumentsPath = WSHShell.SpecialFolders("MyDocuments")
strNames = objShell.ExpandEnvironmentStrings(DesktopPath & "\WOW Project\Names.xlsx")
strStudents = objShell.ExpandEnvironmentStrings(MyDocumentsPath & "\Student Spelling.xlsx")
strTemplate = objShell.ExpandEnvironmentStrings(DesktopPath & "\WOW Project\WOW-example.xls")

Open in new window

0
 
RobSampsonCommented:
The C:\Users\rmoul6 reference is generated by
objShell.ExpandEnvironmentStrings("%USERPROFILE%")

so it should be accurate.  As was suggested by jimyX, maybe the SpecialFolders method will work better.

strNames = objShell.SpecialFolders("Desktop") & "\WOW Project\Names.xlsx")
strStudents = objShell.SpecialFolders("MyDocuments") & "\Student Spelling.xlsx")
strTemplate = objShell.SpecialFolders("Desktop") & "\WOW Project\WOW-example.xls")

Regards,

Rob.
0
 
RobSampsonCommented:
The extra brackets should be there....

strNames = objShell.SpecialFolders("Desktop") & "\WOW Project\Names.xlsx"
strStudents = objShell.SpecialFolders("MyDocuments") & "\Student Spelling.xlsx"
strTemplate = objShell.SpecialFolders("Desktop") & "\WOW Project\WOW-example.xls"


Rob.
0
 
Dier02Author Commented:
How does this go into the script below because it comes up with an unterminated string error:

Set objShell = CreateObject("WScript.Shell")

Dim WSHShell, DesktopPath, MyDocumentsPath
Set WSHShell = WScript.CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")
MyDocumentsPath = WSHShell.SpecialFolders("MyDocuments")
strNames = objShell.ExpandEnvironmentStrings(DesktopPath & "\WOW Project\Names.xlsx")
strStudents = objShell.ExpandEnvironmentStrings(MyDocumentsPath & "\Student Spelling.xlsx")
strTemplate = objShell.ExpandEnvironmentStrings(DesktopPath & "\WOW Project
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
Const xlPasteAll = -4104
Set objNames = objExcel.Workbooks.Open(strNames, False, False)
Set objTemplate = objExcel.Workbooks.Open(strTemplate, False, False)
objExcel.Visible = True
Set objStudents = objExcel.Workbooks.Add
While objStudents.Sheets.Count > 1
      objExcel.DisplayAlerts = False
      objStudents.Sheets(objStudents.Sheets.Count).Delete
      objExcel.DisplayAlerts = True
Wend
For intRow = 2 To objNames.Sheets(1).Cells(65536, "B").End(xlUp).Row
      strName = objNames.Sheets(1).Cells(intRow, "B").Value
      If objStudents.Sheets(1).Name = "Sheet1" Then
            objStudents.Sheets(1).Name = strName
      Else
            objStudents.Sheets.Add , objStudents.Sheets(objStudents.Sheets.Count)
            objStudents.Sheets(objStudents.Sheets.Count).Name = strName
      End If
      objTemplate.Sheets(1).Cells.Copy
      objStudents.Sheets(strName).Cells.PasteSpecial(xlPasteAll)
Next
objNames.Close
objStudents.SaveAs strStudents
objStudents.Close
objExcel.Quit
0
 
jimyXCommented:
My mistake I corrected in my test file but when copied your original forgot to correct the parenthesis:
Set objShell = CreateObject("WScript.Shell")

Dim WSHShell, DesktopPath, MyDocumentsPath
Set WSHShell = WScript.CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")
MyDocumentsPath = WSHShell.SpecialFolders("MyDocuments")
strNames = objShell.ExpandEnvironmentStrings(DesktopPath & "\WOW Project\Names.xlsx")
strStudents = objShell.ExpandEnvironmentStrings(MyDocumentsPath & "\Student Spelling.xlsx")
strTemplate = objShell.ExpandEnvironmentStrings(DesktopPath & "\WOW Project\WOW-example.xls")
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
Const xlPasteAll = -4104
Set objNames = objExcel.Workbooks.Open(strNames, False, False)
Set objTemplate = objExcel.Workbooks.Open(strTemplate, False, False)
objExcel.Visible = True
Set objStudents = objExcel.Workbooks.Add
While objStudents.Sheets.Count > 1
      objExcel.DisplayAlerts = False
      objStudents.Sheets(objStudents.Sheets.Count).Delete
      objExcel.DisplayAlerts = True
Wend
For intRow = 2 To objNames.Sheets(1).Cells(65536, "B").End(xlUp).Row
      strName = objNames.Sheets(1).Cells(intRow, "B").Value
      If objStudents.Sheets(1).Name = "Sheet1" Then
            objStudents.Sheets(1).Name = strName
      Else
            objStudents.Sheets.Add , objStudents.Sheets(objStudents.Sheets.Count)
            objStudents.Sheets(objStudents.Sheets.Count).Name = strName
      End If
      objTemplate.Sheets(1).Cells.Copy
      objStudents.Sheets(strName).Cells.PasteSpecial(xlPasteAll)
Next
objNames.Close
objStudents.SaveAs strStudents
objStudents.Close
objExcel.Quit

Open in new window

0
 
Dier02Author Commented:
Thanks JimyX - worked a treat! Thanks Rob as well.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now