[RESOLVED] vbs file to convert excel and then delete

[kamal876.5]

pls find the below code below for the specifications below - only vb to run as automation. Pls help to find solution.

1. it tries to find excel file from the current location
2. it gets excel file name
3. it gets the file name of the excel
4. it converts the password protected excel file to unprotected excel file saves in a different location and names new file using the same old file name
5. it deletes the old file.

I have tried it but do not get any output.

Set objExcel = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
objExcel.Visible = TRUE
objExcel.DisplayAlerts = FALSE
Dim fullpath
fullpath=fso.getAbsolutePathName(".")
Set objFile=fso.GetFile(fullpath)
Set objName=fso.GetFileName(objFile)
Path2="C:\Users\share\Box\objName.xlsx"
Set objWorkbook = objExcel.Workbooks.Open(fullpath,,,,"1234")
objWorkbook.Password = ""
objWorkbook.SaveAs Path2
fso.DeleteFile objFile
objExcel.Quit

[westconn1]

fullpath=fso.getAbsolutePathName(".")
Set objFile=fso.GetFile(fullpath)
Set objName=fso.GetFileName(objFile)

i do not see how this part will find the xlsx file, you would need to supply the filename somewhere, even if there is only one workbook in the folder

fullpath=fso.getAbsolutePathName(".\myfile.xls")

Set objFile=fso.GetFile("myfile.xls")

either of those options would do, don't need both

if you do not know the name of the workbook, but do know the password, that seems strange, you could pass the workbook name to the .vbs as a parameter, or you could loop through all the files in the folder to find a single xl file, but if there are multiple xl files, i am not sure how you would determine the correct file

[kamal876.5]

Hello,

The folder is going to have only one excel file. But the file will be changed everyday with new filename. Example:todayfile230418 and next day it will be todayfile240418. So once the file is copied to another location after removing password. It is deleted from old folder.
Please help me in the code how to get the excel file from the folder without the name so that i can automate it to take the file everyday.

[ChrisE]

Hi,

where does the Data come from

regards
Chris

[westconn1]

Set objfolder = fso.GetFolder(".")
For Each objfile In objfolder.Files
    If objfile.Type = "Microsoft Office Excel 2007 Workbook" Then   ' .xlsx file
      fullpath = objfile.Path
      Exit For
    End If
Next
Path2 = "C:\Users\share\Box\objName.xlsx"
Set objWorkbook = objExcel.Workbooks.Open(fullpath, , , , "1234")

this should get the first (or only) workbook in the current folder and open in excel instance

[kamal876.5]

pls review the code and where am is the mistake: it came up with error : in line 21: The remote procedure call failed. The only file inside the folder is just book1.xlsx with password - 1234 as example file. I have placed this .vbs file in the same folder as is the file.

Set objExcel = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")

objExcel.Visible = TRUE
objExcel.DisplayAlerts = FALSE

Dim fullpath
Dim objFile


Set objfolder = fso.GetFolder(".")
For Each objfile In objfolder.Files
If objfile.Type = "Microsoft Office Excel 2007 Workbook" Then ' .xlsx file
fullpath = objfile.Path
Set objName=fso.GetFileName(objFile)
Exit For
End If
Next
Path2 = "C:\Users\ADMIN\Box\objName.xlsx"

Set objWorkbook = objExcel.Workbooks.Open(fullpath, , , , "1234")
objWorkbook.Password = ""
objWorkbook.SaveAs Path2

fso.DeleteFile objFile
objExcel.Quit

[ChrisE]

Hi,

where does the Data come from ?

copying this everyday seems strange,
well here a sample to copy ...

Option Explicit

Private Sub Command1_Click()
CopyToNew
End Sub

Sub CopyToNew()
'falls ein Fehler auftritt gehe zur Fehlerbehandlung
On Error GoTo errhandler

Dim objAppExcel As Object
Dim objWb As Object
Dim objSH As Object

Set objAppExcel = CreateObject("Excel.Application")
 'Excel to Copy
Set objWb = objAppExcel.Workbooks.open("C:\ExcelDaten99.xls")
 'Sheet to copy
Set objSH = objWb.Sheets(1)

With objSH
objAppExcel.DisplayAlerts = False
'Copy To
objWb.SaveAs "C:\new_Excel99.xls"
objAppExcel.DisplayAlerts = True
End With
 
objAppExcel.Quit
Set objSH = Nothing
Set objWb = Nothing
Set objAppExcel = Nothing
Exit Sub
 
errhandler:
MsgBox "Fehlernr:" & Err.Number & " " & Err.Description
objAppExcel.Quit
Set objSH = Nothing
Set objWb = Nothing
Set objAppExcel = Nothing
End Sub

EDIT:
why don't you just Rename the Sheet(newSheet) of the original Sheet(oldSheet)
regards
Chris

[kamal876.5]

Hello,

The data file comes from another folder that is copied here.

The solution code provided has file name given but i want to automate by not giving the file name so that it picks the excel file that is there from the folder.

the data file that comes is password protected that needs to be removed and unprotected new file with the same name of old file needs to be copied to different folder.

[ChrisE]

Hello,
The data file comes from another folder that is copied here.

I mean where does the come from...Database;Internet ???
what does the Data contain ?

perhaps a creating a Database to hold the Data would be better?
what if somebody(or just you) want's to take a look at the Data from last week Monday

regards
Chris

[kamal876.5]

Pls help me with my old post that i had sent with code : error in line 21: fail remote procedure call. pls can you solve that?

[ChrisE]

did you try my sample in Post# 7

[westconn1]

as requested

Set fso = CreateObject("Scripting.FileSystemObject")

Dim fullpath
Dim objFile, fname


Set objfolder = fso.GetFolder(".")
For Each objfile In objfolder.Files
If objfile.Type = "Microsoft Office Excel 2007 Workbook" or objfile.type = "Microsoft Excel Worksheet"  Then ' .xlsx or .xls file
fullpath = objfile.Path
fname = objfile.name
''Set objName=fso.GetFileName(objFile)
Exit For
End If
Next
Path2 = "C:\Users\share\Box\" & fname
'msgbox fullpath
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = TRUE
objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Open(fullpath, , , , "1234")
objWorkbook.unprotect "1234"
objWorkbook.SaveAs Path2
objexcel.displayalerts = true
objExcel.Quit
fso.DeleteFile objFile

[kamal876.5]

Thanks for solutions.much appreciated.