473,320 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need Access VBA code to ungroup columns in excel file

126 100+
I have an excel file, which has columns C and D grouped together, I am trying to delete blank columns and rows from the excel file, ungroup the columns and import the file to MS Access using Access VBA code.

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
  1. Sub RunMacro()
  2. On Error GoTo Err_RunMacro
  3.     Dim XL As Excel.Application
  4.     Dim xlRange As Range
  5.     Set XL = New Excel.Application
  6.     XL.Workbooks.Open "C:\Test_file1.xls"
  7.  
  8.     Dim lastrow As Long, lastcol As Long
  9.     Dim NullRange As Range
  10.     Set NullRange = Nothing
  11.  
  12.     XL.ScreenUpdating = False
  13.     XL.Calculation = xlCalculationManual
  14.  
  15.     With XL.ActiveSheet
  16.     On Error GoTo Exits
  17.     Set xlRange = .Range("A:A").SpecialCells(xlLastCell)
  18.     lastcol = xlRange.Column
  19.     .Range(.Cells(1, 1), .Cells(lastcol, 1)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
  20.     lastrow = xlRange.Row
  21.     .Range(.Cells(1, 1), .Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  22.  
  23. Exits:
  24.     XL.ScreenUpdating = True
  25.     XL.Calculation = xlCalculationAutomatic
  26.     If Err.Description = "" Then
  27.         'MsgBox "lastrow" & lastrow
  28.         'MsgBox "lastcol" & lastcol
  29.     Else
  30.         MsgBox Err.Description
  31.     End If
  32.     End With
  33.  
  34.     XL.Workbooks("Test_file1.xls").SaveAs FileName:="C:\ExportFile\Test_file1.xls"
  35.     XL.Quit
  36.     XL.Workbooks.Close
  37.     Set XL = Nothing
  38. Exit_RunMacro:
  39.     Exit Sub
  40. Err_RunMacro:
  41.     MsgBox Err.Description
  42. End Sub
Jul 10 '08 #1
0 2969

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: mr_ocp | last post by:
Hi friends I need routines to create an excel file with worksheets for each customer and a text file as well for each customer, first worksheet would be a "Summary Report", here is the code that...
1
by: Ronny Sigo | last post by:
Hello all, I am trying to import the contents of an Excel sheet into my Access Database. When clicking the button excel opens allright and does what I programmed (the cells get updated with the...
8
by: mytfein | last post by:
Hi Everyone, Background: Another department intends to ftp a .txt file from the mainframe, for me to process. The objective is to write a vb script that would be scheduled to run daily to...
3
by: Lynn A. | last post by:
I have to perform an import from an excel file to Access. The file comes originally as a csv file. I have to delete some rows and columns, then change the formatting of some columns. I am using...
25
by: DFS | last post by:
I have a job to automatically import Excel data and post to database tables, via a point-click interface. Choose-file-and-it-does-the-rest kind of thing. Cient stores data in columns in his...
5
by: hharriel | last post by:
Hi, I am hoping someone can help me with an issue I am having with excel and ms access. I have collected data (which are in individual excel files) from 49 different school districts. All...
9
by: pic078 via AccessMonster.com | last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files) that remains stuck in task manager after exiting the application - you can't reopen database after exiting as a result...
4
by: Melissa | last post by:
I currently have VBA written to export query results into an Excel file. That file is then formatted using the code below. The problem I'm having is that it keeps throwing Error 91 (Object...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.