Set oBook = Nothing
oXL.DisplayAlerts = True '''
Set oXL = Nothing
Another very nice guy also provided the following code to search a string in Excel and return the column number.
Public Function ColumnNo2(ByVal strText As String, ByVal FilePathName As String, ByVal ShtName As String) As Integer
Dim filename As String
Dim sht As Object
Dim objXL As Object
Dim ShtFound As Boolean
ShtFound = False
Const xlFormulas As Integer = -4123
Const xlPart As Integer = 2
Const xlByRows As Integer = 1
Const xlNext As Integer = 1
Set objXL = CreateObject("Excel.Application")
If Dir(FilePathName) <> "" Then
filename = Mid(FilePathName, InStrRev(FilePathName, "\") + 1)
For Each sht In .ActiveWorkbook.Sheets
If sht.Name = ShtName Then
ShtFound = True
On Error Resume Next
ColumnNo2 = sht.Cells.Find(What:=strText, After:=objXL.Cells(1, 1), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
On Error GoTo 0
If ColumnNo2 = 0 Then MsgBox "Text '" & strText & "' not found in file " & filename & " (" & ShtName & ")", vbExclamation, "Find Column"
If Not ShtFound Then MsgBox "Sheet '" & ShtName & "' could not be found in file '" & filename & "'!", vbExclamation, "Find Column"
When I use the following code to call the delete column function, it works perfectly without prompting the save as dialog box.
Private Sub cmd1_Click()
Dim bookname As String
bookname = "c:\file1.xls"
' the following function converts the Excel column from number to letter
Public Function ColumnLetter(ByVal ColumnNumber As Integer) As String
If ColumnNumber > 26 Then
' 1st character: Subtract 1 to map the characters to 0-25,
' but you don't have to remap back to 1-26
' after the 'Int' operation since columns
' 1-26 have no prefix letter
' 2nd character: Subtract 1 to map the characters to 0-25,
' but then must remap back to 1-26 after
' the 'Mod' operation by adding 1 back in
' (included in the '65')
ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
' Columns A-Z
ColumnLetter = Chr(ColumnNumber + 64)
However, when I use the following code, it always prompt save us dialog box to ask me if I want to replace the existing file and I have to click Yes to save the change. I am confused that why calling the same function, but one prompt the save as but the other doesn't prompt the save as dialog box.
Because of the way to pass the parameters???
Is there a way to eliminate the save us dialog box? Thank you very much!
Dim f1 As String
' strpath4 is a variable for a directory path
f1 = strpath4 & "\fOL.xls"
sht = "fOL"
dim skc1 as string
dim skc2 as variant ' getting skc2 from dlookup function from a column in a database table field which may return null or a integer. e.g. 8
dim colno1 as integer
dim colletter1, cl1 as string