We help IT Professionals succeed at work.

Import Specific non-contiguous columns in Excel spreadsheet to Access

dottieelliott
on
752 Views
Last Modified: 2010-03-01
From inside Access, I need to import an Excel spreadsheet to Access daily. There are reasons that I cannot use the TransferSpreadsheet in a macro. I would like to do it in VBA code. Once the data is in Access in the temp table, I will use queries to manipulate it into a final format and move some of the data to a different table.

This is what I need help figuring out how to do:
1. I need to ask the user for the spreadsheet file name and location (ie I need to browse for the location and file). The name is different every day.  It will always import the first sheet although the name of that sheet changes.
2. I need to import specific non-contiguous columns. If this is not possible, and its looks like it might not be, then I need to import the entire sheet and then delete the fields in the Access table that I don't need.
3. While importing, I need to specify the data format of each field coming in. If possible, I do not want to build a temp database that I use everyday and empty, although I can do this if it is best.
4. While importing, Access should assign a unique id number for each record imported.

I would appreciate all the help I can get. I have been programming for years but I have limited VBA experience, mostly just data validation and manipulation. I have never tried to import with VBA before.

Thank you!

Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Top Expert 2006

Commented:
About your Number 3

When you convert a spreadsheet into a CSV, you can define an import spec at that time to format your fields as they are imported.

If you must, here is a small sample of code that converts your xls into csv

Function ConvertXLS(strXLFile As String)
' This function converts an xls file to a csv file for linking or import
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object
    Dim strCSVFile As String
    Dim strTemplate As String
    strCSVFile = Left(strXLFile, Len(strXLFile) - 4) & ".csv"
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open(strXLFile, True)
' It's here you can either save as a CSV so you can link or import with a spec
' or use the open xls to get data straight from the cells
    With xlApp
    xlApp.Visible = False
    'xlwb.Worksheets(1).Cells(2, 10) You can do cell specific stuff here if you need to
' Save as a csv file
    xlApp.SaveAs strCSVFile, xlCSV
    xlWb.Close False
    xlApp.Quit
    Set xlApp = Nothing
    End With
' Link to the CSV file with an import spec so you can retrieve data from the table
    DoCmd.TransferText acLinkDelim, "MySPECNAME", "MyTABLENAME", strCSVFile, True
End Function


Pete's got your back on the rest.
J

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.