babygirls
asked on
Excel and VB?! y it happen???! (urgent for me) thankz
dearest expert,
actually im already successful in writing my excel spreadsheet by using VB. there was no problem at all
this is the format im using to create it.
========================== ========== ========== =======
Function excelIn(ByVal sExcelFile2 As String)
'sExcelFile2 is "Myexcel.xls"
Dim ExcelFSO As New FileSystemObject
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim copyFile As File
On Error GoTo ErrExcel
'Set xlApp = New Excel.Application
On Error GoTo ErrExcel
Set xlApp = CreateObject("Excel.Applic ation")
On Error GoTo ErrExcel
Set copyFile = ExcelFSO.GetFile(sExcelFil e2)
Set xlBook = xlApp.Workbooks.Open(sExce lFile2)
With xlBook.Worksheets("Master TPM").Select
lnum = 5
For Each myroe In Cells().CurrentRegion.Rows
If (xlApp.Cells(lnum, 1)) <> "" Then
lnum = lnum + 1
GoTo CountAgain
Else
GoTo NextStage
End If
Next
NextStage:
xlApp.Cells(lnum, 1) = (sSystemDate, "mm/dd/yy"))
xlApp.Cells(lnum, 2) = sMySite
xlApp.Cells(lnum, 3) = sMyChannel
Columns("A:A").EntireColum n.AutoFit
Columns("B:B").EntireColum n.AutoFit
Columns("C:C").EntireColum n.AutoFit
End With
ErrExcel:
sOUT = "There was an error in excel" & vbCrLf & Error$
sOUT = "ERR:" & str(Now) & " Emg:" & sOUT
exitdoor:
xlBook.Save
xlBook.Close
xlApp.Quit
Set copyFile = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Set ExcelFSO = Nothing
========================== ========== ========== =====
now im need to do another excel file. let say name is as myExcel2.xls
so in this case i still use the same code as wat im mention above.cz this two xls file is in the same format n same data..the different is jst on their file name.
but when come to that "for each myroe.cells().." there got an error message call " METHODS 'CELLS' OF OBJECT '_GLOBAL' FAILED"
hw come this will happen when im wanna write data in to myExcel2.xls but there was no error in myexcel.xls.
why it happen???
can u jst help me ???
need it by tomolo...hw..
actually im already successful in writing my excel spreadsheet by using VB. there was no problem at all
this is the format im using to create it.
==========================
Function excelIn(ByVal sExcelFile2 As String)
'sExcelFile2 is "Myexcel.xls"
Dim ExcelFSO As New FileSystemObject
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim copyFile As File
On Error GoTo ErrExcel
'Set xlApp = New Excel.Application
On Error GoTo ErrExcel
Set xlApp = CreateObject("Excel.Applic
On Error GoTo ErrExcel
Set copyFile = ExcelFSO.GetFile(sExcelFil
Set xlBook = xlApp.Workbooks.Open(sExce
With xlBook.Worksheets("Master TPM").Select
lnum = 5
For Each myroe In Cells().CurrentRegion.Rows
If (xlApp.Cells(lnum, 1)) <> "" Then
lnum = lnum + 1
GoTo CountAgain
Else
GoTo NextStage
End If
Next
NextStage:
xlApp.Cells(lnum, 1) = (sSystemDate, "mm/dd/yy"))
xlApp.Cells(lnum, 2) = sMySite
xlApp.Cells(lnum, 3) = sMyChannel
Columns("A:A").EntireColum
Columns("B:B").EntireColum
Columns("C:C").EntireColum
End With
ErrExcel:
sOUT = "There was an error in excel" & vbCrLf & Error$
sOUT = "ERR:" & str(Now) & " Emg:" & sOUT
exitdoor:
xlBook.Save
xlBook.Close
xlApp.Quit
Set copyFile = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Set ExcelFSO = Nothing
==========================
now im need to do another excel file. let say name is as myExcel2.xls
so in this case i still use the same code as wat im mention above.cz this two xls file is in the same format n same data..the different is jst on their file name.
but when come to that "for each myroe.cells().." there got an error message call " METHODS 'CELLS' OF OBJECT '_GLOBAL' FAILED"
hw come this will happen when im wanna write data in to myExcel2.xls but there was no error in myexcel.xls.
why it happen???
can u jst help me ???
need it by tomolo...hw..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thankz for ur answer...
but i still dun understand y in myexcel.xls i can jst type in For Each myroe In Cells().CurrentRegion.Rows
but for myexcel2.xls
i hv to use that.
wat is the exact problem ?
may i knw about that?
thakz again
babygirls