Hi Ken,
Try it but have the same result. Run perfect on the first time. After
that,
I got error message (Run time error '1004') and the following code was
highlighted.
objSheet.Columns("J:J").TextToColumns Destination:=Range("J1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Here is the new code:
Public Sub y()
Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Open("C:\temp\Test080305.xls",
ReadOnly:=True)
Set objSheet = objBook.Worksheets("qryTest")
objExcel.Visible = True
'With objSheet
Set rngRow = objSheet.Range(objSheet.Cells(2, 1), objSheet.Cells(2,
1).End(xlDown))
'End With
'With objSheet
Set rngcol = objSheet.Range(objSheet.Cells(1, 1), objSheet.Cells(1,
1).End(xlToRight))
'End With
For j = 1 To rngcol.Count
For i = 1 To rngRow.Count
If objSheet.Cells(i + 1, j).Text = "" Then
objSheet.Cells(i + 1, j).Interior.ColorIndex = 6
objSheet.Cells(i + 1, j).Interior.Pattern = xlSolid
End If
Next
Next
objSheet.Cells(1, 3).Value = "f1"
objSheet.Cells(1, 4).Value = "f2"
objSheet.Cells(1, 5).Value = "f3"
objSheet.Cells(1, 6).Value = "f4"
objSheet.Cells(1, 8).Value = "f5"
objSheet.Cells(1, 11).Value = "f6"
objSheet.Cells(1, 13).Value = "f7"
objSheet.Cells(1, 14).Value = "f8"
objSheet.Cells(1, 15).Value = "f9"
objSheet.Cells(1, 16).Value = "f10"
objSheet.Cells(1, 17).Value = "f11"
objSheet.Cells(1, 18).Value = "f12"
objSheet.Cells(1, 19).Value = "f13"
objSheet.Cells(1, 20).Value = "f14"
objSheet.Rows("1:1").Font.Bold = True
objSheet.Columns("U:U").Delete Shift:=xlToLef
objSheet.Columns("A:T").EntireColumn.AutoFit
objSheet.Columns("J:J").NumberFormat = "[<=9999999]###-####;(###)
###-####"
objSheet.Columns("J:J").TextToColumns Destination:=Range("J1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
objExcel.DisplayAlerts = False
objBook.SaveAs Filename:="C:\temp\TestNew.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Set objSheet = Nothing
objBook.Close
Set objBook = Nothing
objExcel.Quit
Set objExcel = Nothing
End Sub
Ken Snell said:
The problem is in this step (and tho others that are similar):
Set rngRow = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
You need to fully qualify the Cells reference. Even though you may think
that the With ... End With syntax is doing it for you, in this case I
believe VBA is creating a new reference to the worksheet in order to use
the
Cells object:
Set rngRow = .Range(objSheet.Cells(2, 1), objSheet.Cells(2,
1).End(xlDown))
So, I would eliminate the use of your With ... End With in your code, as
nothing is really being gained by it. Change this:
With objSheet
Set rngRow = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
to this:
Set rngRow = objSheet.Range(objSheet.Cells(2, 1), objSheet.Cells(2,
1).End(xlDown))
Additionally, I like to set objects to nothing before I close out the
parent
object through which it's referenced; also helps avoid "lingering"
references just in case. For example, I would change this:
objBook.Close
objExcel.Quit
Set objSheet = Nothing
Set objBook = Nothing
Set objExcel = Nothing
to this:
Set objSheet = Nothing
objBook.Close
Set objBook = Nothing
objExcel.Quit
Set objExcel = Nothing
--
Ken Snell
<MS ACCESS MVP>
Hi Ken,
Here is my full set of code. It ran great at the first time. After
that,
the error will appear. Please help.
Public Sub y()
Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Open("C:\temp\Test080305.xls",
ReadOnly:=True)
Set objSheet = objBook.Worksheets("qryTest")
objExcel.Visible = True
With objSheet
Set rngRow = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
With objSheet
Set rngcol = .Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight))
End With
For j = 1 To rngcol.Count
For i = 1 To rngRow.Count
If objSheet.Cells(i + 1, j).Text = "" Then
objSheet.Cells(i + 1, j).Interior.ColorIndex = 6
objSheet.Cells(i + 1, j).Interior.Pattern = xlSolid
End If
Next
Next
objSheet.Cells(1, 3).Value = "f1"
objSheet.Cells(1, 4).Value = "f2"
objSheet.Cells(1, 5).Value = "f3"
objSheet.Cells(1, 6).Value = "f4"
objSheet.Cells(1, 8).Value = "f5"
objSheet.Cells(1, 11).Value = "f6"
objSheet.Cells(1, 13).Value = "f7"
objSheet.Cells(1, 14).Value = "f8"
objSheet.Cells(1, 15).Value = "f9"
objSheet.Cells(1, 16).Value = "f10"
objSheet.Cells(1, 17).Value = "f11"
objSheet.Cells(1, 18).Value = "f12"
objSheet.Cells(1, 19).Value = "f13"
objSheet.Cells(1, 20).Value = "f14"
objSheet.Rows("1:1").Font.Bold = True
objSheet.Columns("U:U").Delete Shift:=xlToLef
objSheet.Columns("A:T").EntireColumn.AutoFit
objSheet.Columns("J:J").NumberFormat = "[<=9999999]###-####;(###)
###-####"
objSheet.Columns("J:J").TextToColumns Destination:=Range("J1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False,
Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
objExcel.DisplayAlerts = False
objBook.SaveAs Filename:="C:\temp\TestNew.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False
objBook.Close
objExcel.Quit
Set objSheet = Nothing
Set objBook = Nothing
Set objExcel = Nothing
End Sub
:
Hmmm.... Unfortunately, I expect that the problem is in this part of
your
code:
~ ~
~ ~
~ ~
This problem almost always is caused by using an incompletely
qualified
reference to some EXCEL object. Post all your code.
--
Ken Snell
<MS ACCESS MVP>
Hi folks,
I need a help to close Excel.exe in Windows Task Manager under the
tag
"Processes" from Access 2002' module.
I ran a macro Excel from Access 2002. When I ran it at the first
time,
it
works great. But, I would get an error message after the first time
run.
After I spent several hours on it, I found that the problem was
caused
by
Excel bug - Excel was closed but still running in Windows Task
Manager
under
the tag "Processes".
Here is my code segment:
Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
~ ~
~ ~
~ ~
objBook.Close
objExcel.Quit
Set objSheet = Nothing
Set objBook = Nothing
