The following is the Access VBA code I used to delete blank columns and rows in the excel file. But, unfortunately, the resultant excel file still has two columns (C and D) grouped together, so when I am importing the file to MS Access, the table has one blank field.
Please kindly let me know what is the Access VBA code to ungroup columns in excel file. Thanks in advance.
Expand|Select|Wrap|Line Numbers
- Sub RunMacro()
- On Error GoTo Err_RunMacro
- Dim XL As Excel.Application
- Dim xlRange As Range
- Set XL = New Excel.Application
- XL.Workbooks.Open "C:\Test_file1.xls"
- Dim lastrow As Long, lastcol As Long
- Dim NullRange As Range
- Set NullRange = Nothing
- XL.ScreenUpdating = False
- XL.Calculation = xlCalculationManual
- With XL.ActiveSheet
- On Error GoTo Exits
- Set xlRange = .Range("A:A").SpecialCells(xlLastCell)
- lastcol = xlRange.Column
- .Range(.Cells(1, 1), .Cells(lastcol, 1)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
- lastrow = xlRange.Row
- .Range(.Cells(1, 1), .Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
- Exits:
- XL.ScreenUpdating = True
- XL.Calculation = xlCalculationAutomatic
- If Err.Description = "" Then
- 'MsgBox "lastrow" & lastrow
- 'MsgBox "lastcol" & lastcol
- Else
- MsgBox Err.Description
- End If
- End With
- XL.Workbooks("Test_file1.xls").SaveAs FileName:="C:\ExportFile\Test_file1.xls"
- XL.Quit
- XL.Workbooks.Close
- Set XL = Nothing
- Exit_RunMacro:
- Exit Sub
- Err_RunMacro:
- MsgBox Err.Description
- End Sub