Solved

Comparing file structures

Posted on 2013-06-03
4
208 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

752 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