Solved

Comparing file structures

Posted on 2013-06-03
4
204 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now