Access VBA delete entire column or columns and shift lef in Excel

J

jydbman

Registered User.
Local time
Today, 15:58
Joined
Nov 23, 2007
Messages
40
Hi VBA gurus,

Sorry for my very limited VBA skill level in access especially when need to work on Excel automation. I need to write a function in Access vba to delete entire column or columns and shift lef in Excel. After delete the column(s), it will save the change. This function can be reused so it will allow me to pass parameters - Excel file name, sheet name, and column number(column ranges).

I tried but my code does not work. Any help will be greatly appreciated ! Thank you very much!

Public Sub DeleteColumn(ByVal bookname As String, ByVal sheetname As String, ByVal cellcolumn As String) 'does not work

Dim oXL As Excel.Application
Dim oSheet As Excel.Worksheet

Dim oWKSource As Excel.Workbook

Dim strSourceBook As String

Dim strSourceSheet As String

Dim raSource As Excel.Range

strSourceBook = bookname

strSourceSheet = sheetname


Set oXL = CreateObject("Excel.Application")

Set oWKSource = oXL.Workbooks.Open(strSourceBook)
Set oSheet = oWKSource.Worksheets(strSourceSheet)
oXL.Visible = False

oXL.DisplayAlerts = False

oWKSource.Worksheets(strSourceSheet).Range(cellcolumn).EntireColumn.Delete xlShiftToLeft

oXL.DisplayAlerts = False

'Close all workbooks
For Each oBook In oXL.Workbooks
oBook.Close False
Next
oXL.DisplayAlerts = True

oXL.Quit
Set oXL = Nothing
Set oWKSource = Nothing
Set oWKDestination = Nothing

Set oBook = Nothing

End Sub
 
Change:
Code: 
oWKSource.Worksheets(strSourceSheet).Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
...to:
Code: 
oWKSource.Worksheets(strSourceSheet).[B][COLOR="Red"]Columns(cellcolumn)[/COLOR][/B].Delete xlShiftToLeft
 
ByteMyzer said:
Change:
Code: 
oWKSource.Worksheets(strSourceSheet).Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
...to:
Code: 
oWKSource.Worksheets(strSourceSheet).[B][COLOR=Red]Columns(cellcolumn)[/COLOR][/B].Delete xlShiftToLeft
Click to expand...

ByteMyzer,

Thank you so much for help.
I got an run-time error '1004';
Application defined or Object defined error

on the line oWKSource.Worksheets(strSourceSheet).Columns(cellcolumn).Delete xlShiftToLeft

Please help me again! Thank you very much!
 
Try:
Code: 
oSheet.Columns(cellcolumn).Delete xlShiftToLeft
 
What are the values that you are passing to the following variables?
* bookname
* sheetname
* cellcolumn
 
ByteMyzer said:
What are the values that you are passing to the following variables?
* bookname
* sheetname
* cellcolumn
Click to expand...

Thank you. Here is the command button which calls the function.

Private Sub cmd1_Click()
Dim bookname As String
bookname = "c:\file1.xls"

Dim sheetname As String
sheetname = "file1"

Dim cellcolumn As String

cellcolumn = "H2"

Call DeleteColumn(bookname, sheetname, cellcolumn)


End Sub
 
In that case:
Code: 
oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
...should be the correct code.

What error do you get if you use this code?
 
ByteMyzer said:
In that case:
Code: 
oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
...should be the correct code.

What error do you get if you use this code?
Click to expand...

Thank you for keep helping me.

I got the error: Run-time error '1004'; Method 'Range' of object '_Worksheet' failed.

The error falls on the line: oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
 
Try this somewhat more compact code:
Code: 
[COLOR="Navy"]Public Sub[/COLOR] DeleteColumn( _
    [COLOR="navy"]ByVal[/COLOR] bookname [COLOR="navy"]As String[/COLOR], _
    [COLOR="navy"]ByVal[/COLOR] sheetname [COLOR="navy"]As String[/COLOR], _
    [COLOR="navy"]ByVal[/COLOR] cellcolumn [COLOR="navy"]As String[/COLOR])

    [COLOR="navy"]Const[/COLOR] xlShiftToLeft = -4159

    [COLOR="navy"]Dim[/COLOR] oXL [COLOR="navy"]As Object
    Dim[/COLOR] oBook [COLOR="navy"]As Object
    Dim[/COLOR] oSheet [COLOR="navy"]As Object

    Set[/COLOR] oXL = CreateObject("Excel.Application")
    [COLOR="navy"]Set[/COLOR] oBook = oXL.Workbooks.Open(bookname)
    [COLOR="navy"]Set[/COLOR] oSheet = oBook.Worksheets(sheetname)

    oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
    [COLOR="navy"]Set[/COLOR] oSheet = [COLOR="navy"]Nothing[/COLOR]

    oBook.Close [COLOR="navy"]True
    Set[/COLOR] oBook = [COLOR="navy"]Nothing[/COLOR]

    oXL.Quit
    [COLOR="navy"]Set[/COLOR] oXL = [COLOR="navy"]Nothing

End Sub[/COLOR]
 
ByteMyzer said:
Try this somewhat more compact code:
Code: 
[COLOR=Navy]Public Sub[/COLOR] DeleteColumn( _
    [COLOR=navy]ByVal[/COLOR] bookname [COLOR=navy]As String[/COLOR], _
    [COLOR=navy]ByVal[/COLOR] sheetname [COLOR=navy]As String[/COLOR], _
    [COLOR=navy]ByVal[/COLOR] cellcolumn [COLOR=navy]As String[/COLOR])

    [COLOR=navy]Const[/COLOR] xlShiftToLeft = -4159

    [COLOR=navy]Dim[/COLOR] oXL [COLOR=navy]As Object
    Dim[/COLOR] oBook [COLOR=navy]As Object
    Dim[/COLOR] oSheet [COLOR=navy]As Object

    Set[/COLOR] oXL = CreateObject("Excel.Application")
    [COLOR=navy]Set[/COLOR] oBook = oXL.Workbooks.Open(bookname)
    [COLOR=navy]Set[/COLOR] oSheet = oBook.Worksheets(sheetname)

    oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
    [COLOR=navy]Set[/COLOR] oSheet = [COLOR=navy]Nothing[/COLOR]

    oBook.Close [COLOR=navy]True
    Set[/COLOR] oBook = [COLOR=navy]Nothing[/COLOR]

    oXL.Quit
    [COLOR=navy]Set[/COLOR] oXL = [COLOR=navy]Nothing

End Sub[/COLOR]
Click to expand...

Thank you so much! I still got "Run-time error '1004'; Application-defined or object-defined error" on the line oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft

I wonder that if the version of access make difference. I am using access 2000.

Thank you!
 
Have you tried executing the column delete statement directly in Excel?

Try the following:
1) Open the spreadsheet in Microsoft Excel
2) Select the sheet with the column that you wish to delete (Example: Sheet file1, Column H)
3) Press Alt-F11 to open the Visual Basic window
4) Press Ctrl-G to open the Debug window
5) Paste the following text into the Debug window and press Enter:
Code: 
Activesheet.Range("H2").EntireColumn.Delete xlShiftToLeft
 
ByteMyzer said:
Have you tried executing the column delete statement directly in Excel?

Try the following:
1) Open the spreadsheet in Microsoft Excel
2) Select the sheet with the column that you wish to delete (Example: Sheet file1, Column H)
3) Press Alt-F11 to open the Visual Basic window
4) Press Ctrl-G to open the Debug window
5) Paste the following text into the Debug window and press Enter:
Code: 
Activesheet.Range("H2").EntireColumn.Delete xlShiftToLeft
Click to expand...
Thank you!
Yes, it did the deletion successfully.
 
Then I don't know what else to tell you. Both the Excel-direct method I just gave you, and the DeleteColumn Sub in Access, worked for me.
I can only suggest that you double-check your input parameters against the actual names of:

* The File
* The Sheet
* The Range reference for the column deletion
 
ByteMyzer said:
Then I don't know what else to tell you. Both the Excel-direct method I just gave you, and the DeleteColumn Sub in Access, worked for me.
I can only suggest that you double-check your input parameters against the actual names of:

* The File
* The Sheet
* The Range reference for the column deletion
Click to expand...

Thank you so much for keep trying to help me! The input parameters are like this. It's weird that the code error out. What version of Access you are using? Thanks.

Private Sub cmd1_Click()
Dim bookname As String
bookname = "c:\file1.xls"

Dim sheetname As String
sheetname = "file1"

Dim cellcolumn As String

cellcolumn = "H2"

Call DeleteColumn(bookname, sheetname, cellcolumn)


End Sub
 
Code: 
Private Sub cmd1_Click()
Dim bookname As String
bookname = "c:\file1.xls"

Dim sheetname As String
sheetname = "file1"

Dim cellcolumn As String

cellcolumn = "[COLOR=red]H:H[/COLOR]"

Call DeleteColumn(bookname, sheetname, cellcolumn)
End Sub

See if this dosen't help you delete the column. (marked in red)

JR
 
JANR said:
Code: 
Private Sub cmd1_Click()
Dim bookname As String
bookname = "c:\file1.xls"

Dim sheetname As String
sheetname = "file1"

Dim cellcolumn As String

cellcolumn = "[COLOR=red]H:H[/COLOR]"

Call DeleteColumn(bookname, sheetname, cellcolumn)
End Sub
See if this dosen't help you delete the column. (marked in red)

JR
Click to expand...

Thank you so much, JR.

It did not error out, but it pop out a windows save as dialog box. When I say I want to save (replace the original file) it says the file is already existing, do you want to replace it? If I say yes, it say you can not access the file, it may be read only.
I don't want have the save as box and I want it save behind the scene.
Thanks!
 
Code: 
....
    oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
    [COLOR=red]oXL.Save[/COLOR]
    
    Set oSheet = Nothing
    oBook.Close True
 
    Set oBook = Nothing
 
    oXL.Quit
    Set oXL = Nothing
End Sub
Issue a save before you close excel

JR
 
Last edited:
Come to think about it, I think the code you where given will save by default, The reason of the error is that you probably have some rouge excel instances in memory. clear them out and you'll be good to go.

JR
 
JANR said:
Code: 
....
    oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
    [COLOR=red]oXL.Save[/COLOR]
    
    Set oSheet = Nothing
    oBook.Close True
 
    Set oBook = Nothing
 
    oXL.Quit
    Set oXL = Nothing
End Sub
Issue a save before you close excel

JR
Click to expand...

Thank you so much, JR!

It works better! The first time I ran, there is no error or any pop up dialog box and it did delete the column. However, the second time and later, when I click the button, I got a pop up dialog : A file name 'RESUME.XLW' already exists in this location. Do you want to replace it? Yes, No, Cancel

I have to click yes. Anyway to get rid of the dialog? Thanks!
 
You must log in or register to reply here.

Similar threads

N
Solved Receiving Emails in Access Database using CDO 2000 library
Replies
10
Views
286
sonic8
S
S
Solved Timing or other issue when closing Excel from Access
Replies
13
Views
485
sxschech
S
oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
504
oxicottin
oxicottin
R
Closing Excel from Access VBA
2 3
Replies
59
Views
2,475
ebs17
E
K
Solved Why setting the backstyle in vba fails?
2
Replies
26
Views
592
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom