Solved

Comparing file structures

Posted on 2013-06-03
4
206 Views
Last Modified: 2013-06-10
Is there a way to compare the structure of a file, e.g. Excel file that you are going to import into an Access table before you actually do the import?  If the order of the columns or the column names in the spreadsheet do not match that of the table, Access will issue a warning and abort the procedure. I would like a more "elegant" way of dealing with it, i.e., being able to determine any discrepancy beforehand and not allow the user to proceed before that happens.
0
Comment
Question by:geeta_m9
  • 2
  • 2
4 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 39216897
You'd need to open the Excel workbook through automation and examine the structure, and make your determination from there.

Here's a good example on automating Excel:

http://access.mvps.org/access/modules/mdl0006.htm

In the code at the link above, you could expand on that to review the structure. For example:

    With objActiveWkb
        If .Worksheets(1).Cells(1, 1) = "SomeValue" Then
           Msgbox "Problem found in workbook"        
    End With
0
 

Author Comment

by:geeta_m9
ID: 39216963
So, I should put this code in a module in Access?
0
 
LVL 84
ID: 39217717
Basically, yes. You'd add code like that to your Access application and run that code before you did the import. Obviously you'd have to flesh it out quite a bit to accurately review your structures and such.
0
 

Author Closing Comment

by:geeta_m9
ID: 39235558
Thanks.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question