Open Excel by code

  • Thread starter Thread starter a
  • Start date Start date
A

a

dear friends is there any way to open excel in a hiden
and do some change in sheet
save the work book and sheet
and after that clean the memorey

becuse i have a code
open excel
edit
save
but when i open the file
message box appear (the file is open) do you want to open as read only
this mean is
Excel still in pc memory
What i want
give me code example
open excel in hiden mode
do some small change
save
exit
clean memore
thanks for your time
 
How about if you post the code that you're using, and let us show you how to
modify it to work correctly?
 
Thank you:
The Code Here:


Dim xlObj As Object
Dim wbkOutput As Excel.Workbook
Dim wks1 As Worksheet
Set xlObj = CreateObject("Excel.application")
Set wbkOutput = xlObj.Workbooks.Open("C:\123.xls")
'===============================================================================
'error appear here
'Subscript out of range (Error 9) (why)
Set wks1 = wbkOutput.Sheets(1)
'===============================================================================
xlObj.Visible = False
wks1.Activate
Dim mycsvstrfilename As String
mycsvstrfilename = "C:\123.Csv"

'===============================================================================
'Object variable or With block variable not set (Error 91) (why)
'error appear here
ActiveWorkbook.SaveAs FileName:=mycsvstrfilename, FileFormat:=xlCSVWindows,
CreateBackup:=False

'error appear here
'
'The file is saved as csv
' but with problem
' The problem is all row example row No(1)column (1,2,3,4,5,6....etc) merged
in one cell like (A1)
' The problem is all row example row No(2)column (1,2,3,4,5,6....etc) merged
in one cell like (B1)
' Etc

'===============================================================================


wbkOutput.Close
Set wks1 = Nothing
Set wbkOutput = Nothing
Set xlObj = Nothing

End Sub
'===============================================================================
'this error appear after the code end and when open the 123.csv
'123.csv is already open.reopening will cause any changes you made to be
discarded do you want to repopen 123.csv
 
You're using late binding in your code, so change these two lines of code:
Dim wbkOutput As Excel.Workbook
Dim wks1 As Worksheet

to these:
Dim wbkOutput As Object
Dim wks1 As Object


Also, don't use ActiveWorkbook in your code. That creates a new instance of
EXCEL object. Instead, reference the workbook through the object that you've
established for that workbook. So, this line of code:
ActiveWorkbook.SaveAs FileName:=mycsvstrfilename, FileFormat:=xlCSVWindows,
CreateBackup:=False

should be this:
wbkOutput.SaveAs FileName:=mycsvstrfilename, FileFormat:=xlCSVWindows,
CreateBackup:=False


Also, you need to destroy objects in the correct order. In the following
lines of code, you're closing the workbook while you still have a worksheet
object pointing at a worksheet in that file. You also are destroying the
xlObj object before you close the object:
wbkOutput.Close
Set wks1 = Nothing
Set wbkOutput = Nothing
Set xlObj = Nothing

change the above to this:
Set wks1 = Nothing
wbkOutput.Close
Set wbkOutput = Nothing
xlObj.Quit
Set xlObj = Nothing


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
Back
Top