Access 2007 Import Wizard - Any Way to Grab Data from It? 1

[kjv1611]

I'm just wondering, as it would be helpful to me at the moment, if there were a way when calling the data import wizards, to return the data back to VBA from the import wizard.

For instance, to return the file location/name, actual object name(s) imported - what worksheets if Excel, what tables/queries, if Access, that sort of thing.g

I realize that you can get the table names from Access, and what file type based on the type of import wizard run... if I'm telling Access which wizard via code.

Is there any way to dig in and grab the additional data from the wizard itself during or just after an import routine?

Thanks for any thoughts/references/suggestions.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Here's another thought on the same topic, but a different approach... and may not be possible.

Would it be possible to feed the file name/location into the import wizard when opening it from code?

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Any thoughts?

--

"If to err is human, then I must be some kind of human!" -Me

 

[ZiggyS1]

It sounds like you want the File Open Dialogue, then select the file and Import it? You only need the wizard the first time you import a table, once it is created you can automate with the "Transferspreadsheet" method.

I have code that I use to either Search a directory and import all files found ( of a specific filename), or to use the file open to select the one you want, in either case you will be able to return the File location, name etc.


I'll post what I have, but it will look confusing, but you will only need to edit a few places to make it work.

 

[ZiggyS1]

File Open Dialogue

* Note I didn't write it just don't remember where I got it from, but made a few changes here and there....

Step 1 make a form with a command button and Text box

name them...

cmdFileOpen
txtDefpath

Step 2

in the On click event of the button....

Note you only need to edit..

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "YourTablename" , OFN.lpstrFile, True

Private Sub cmdFileOpen_Click()
' this is the click event from a button on your form

  Dim OFN As OPENFILENAME
  On Error GoTo Err_cmdFileOpen_Click

' add a text box to hold a path name for your files.
' it works 2 ways, if you fill it in, it will go to the default
' if you leave it blank the file you select will populate into it.

  ' Set options for dialog box.
  With OFN
    .lpstrTitle = "Select Spreadsheet"
    If Not IsNull([txtDefpath]) Then .lpstrFile = [txtDefpath]
    .flags = &H1804 ' OFN_FileMustExist + OFN_PathMustExist + OFN_HideReadOnly
    .lpstrFilter = MakeFilterString("All files (*.*)", "*.*")
  End With

  If OpenDialog(OFN) Then
    [txtDefpath] = OFN.lpstrFile
    
                'you only need to add your table name and it will import the excel files
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "YourTablename", OFN.lpstrFile, True

  End If
  Exit Sub

Err_cmdFileOpen_Click:
  MsgBox Err.Description, vbExclamation

End Sub

Step 3

Create Module and place this code in it....

Option Compare Database


Option Explicit

' Code to display standard "Open File" dialog.

Type OPENFILENAME
  lStructSize As Long
  hwndOwner As Long
  hInstance As Long
  lpstrFilter As String
  lpstrCustomFilter As String
  nMaxCustFilter As Long
  nFilterIndex As Long
  lpstrFile As String
  nMaxFile As Long
  lpstrFileTitle As String
  nMaxFileTitle As Long
  lpstrInitialDir As String
  lpstrTitle As String
  flags As Long
  nFileOffset As Integer
  nFileExtension As Integer
  lpstrDefExt As String
  lCustData As Long
  lpfnHook As Long
  lpTemplateName As String
End Type

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
  "GetOpenFileNameA" (OFN As OPENFILENAME) As Boolean

Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias _
  "GetSaveFileNameA" (OFN As OPENFILENAME) As Boolean

Private Const ALLFILES = "All files"

Function MakeFilterString(ParamArray varFilt() As Variant) As String
' Create filter string.
' Returns "" if there are no arguments.
' Expects an even number of arguments (filter name, extension).
' Adds *.* if the number of arguments is odd.

  Dim strFilter As String
  Dim intRes As Integer
  Dim intNum As Integer

  intNum = UBound(varFilt)
  If (intNum <> -1) Then
    For intRes = 0 To intNum
      strFilter = strFilter & varFilt(intRes) & vbNullChar
    Next
    If intNum Mod 2 = 0 Then
      strFilter = strFilter & "*.*" & vbNullChar
    End If

    strFilter = strFilter & vbNullChar
  End If

  MakeFilterString = strFilter
End Function

Private Sub InitOFN(OFN As OPENFILENAME)
  With OFN
    ' Initialize fields user doesn't want to know about
    .hwndOwner = hWndAccessApp
    .hInstance = 0
    .lpstrCustomFilter = vbNullString
    .nMaxCustFilter = 0
    .lpfnHook = 0
    .lpTemplateName = 0
    .lCustData = 0
    .nMaxFile = 511
    .lpstrFileTitle = String(512, vbNullChar)
    .nMaxFileTitle = 511
    .lStructSize = Len(OFN)
    ' Use default filter if not specified.
    If .lpstrFilter = "" Then
      .lpstrFilter = MakeFilterString(ALLFILES)
    End If
    ' Pad lpstrFile with null chars.
    .lpstrFile = .lpstrFile & String(512 - Len(.lpstrFile), vbNullChar)
  End With
End Sub

Function OpenDialog(OFN As OPENFILENAME) As Boolean
  Dim intRes As Integer
  InitOFN OFN
  intRes = GetOpenFileName(OFN)
  If intRes Then
    ' Remove trailing null chars from lpstrFile.
    With OFN
      .lpstrFile = Left$(.lpstrFile, InStr(.lpstrFile, vbNullChar) - 1)
    End With
  End If
  OpenDialog = intRes
End Function

 

[kjv1611]

You only need the wizard the first time you import a table, once it is created you can automate with the "Transferspreadsheet" method.

Are you saying this for importing the same worksheet multiple times? In other words, same format, different data?

If so, that's not what this one will be. There is no "known" set format that will work 100% of the time. I've actually already got an automated setup to import any files the user selects, but there are some instances when it will not import 100% correctly. For those, I was hoping to run the manual import wizard, and grab the file name, object name, file location, from the wizard, to store in a table for future use. I've got the automated one doing so, b/c the file-open dialog is all VBA code, and therefore I've got access to everything selected..

I hope I'm making sense. The other possibility which I keep meaning to get around to trying is creating a custom import file (.schema file) on the fly at each import, to make sure the file is imported correctly.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, are there any system tables that would store the necessary data I would need?

The system tables showing are:

MSysRelationships
MSysQueries
MSysObjects
MSysNavPaneObjectIDs
MSysNavPaneGroupToObjects
MSysNavPaneGroups
MSysNavPaneGroupCategories
MSysComplexColumns
MSysACEs
MSysAccessStorage

--

"If to err is human, then I must be some kind of human!" -Me

 

[ZiggyS1]

well, then maybe this will work for you... a simple adjustment

IMPfile = InputBox("Enter a Table name")


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, IMPfile , OFN.lpstrFile, True


or grab the filename dir(txtDefpath) and use it in place of the inputbox and your table will save as the excel filename

 

[kjv1611]

Well, handling the table name isn't the problem. In every instance, it will be a new table. I already have the "automatic" routine creating the table, table name, and all, which points to the original file_object combination. And in that, I even have some small bits fixing up any possible problems between things allowed in say Excel spreadsheet names and allowed Access table names.

But the problem that could come up (though I do think rarely) is that there are some occasions when importing an Excel worksheet, at least, that the spreadsheet would not import correctly, and therefore give a "importerrors" table alongside the imported data.

Now, for errors where the data type is off, I can adapt some code from another database which would basically open each workbook in Excel, and grab the data for those which threw errors, and then go on about business. That's another option I'm considering of falling back on.

Of course another option I see at this point is just to not worry about the original file name/location, as I don't HAVE to store that data for future use. I could just have conditions in the code to handle those that do not have a file location on record... and that may be the quickest option at this point.. I think.

--

"If to err is human, then I must be some kind of human!" -Me

 

[ZiggyS1]

can you explain why you want to import all these random Excel workbooks, and what you are doing with them?

 

[kjv1611]

Well, it's an Access database I setup at least 2 or 3 years ago for "randomizing" data. The data is for audits, and before this, we were just "randomly" picking records out of a list manually. Not very scientific, prone to human error, and people could argue that we showed favoritism pretty easily, really... it'd be impossible to prove/disprove, almost.

So, I searched around, and found a method in Access to take care of it. And I set it up, pretty loosely, originally, b/c I didn't think my coworkers would use it all that much - and boy was I wrong... it's used for every audit, now, where they need a random sample of data.

At least 98 or 99% of all data that gets randomized is in the form of Excel workbooks.

Anyhow, the old version doesn't work in MS Access 2007. Therefore, it got me thinking of a rewrite - one that could basically automatically import most data sets without hardly any user input necessary. Then the randomizer process runs, they pick their sample count, bla bla bla...

What I'd prefer to use is the SQL Server method of using NewID(), but that doesn't work in Access, at least every time I've tried. And to do that, everyone would have to have access to the MS SQL Server, which everyone does not always have... and at least most of the others don't seem to have a desire to learn it well enough to make SQL useful for them.

So, of course, with the redesign of the database, I've decided to make it more user friendly, and that sort of thing - more of an end user application than it is currently. The old one still works, as the other audits all currently have Office 2003, not 2007. The new one "works", but I'm trying to fine tune things such as this import deal.

So hopefully now you now see why it's always using "new" tables, and never the same table.

One other piece is that new audits do occasionally surface - they're not all repeat audits. So, it won't always have the exact same format.

Really, I may be overthinking the thing, and trying to cover for things that need not be worried about, but if nothing else, I try to be that way at times, so that I can learn more about the tools I use.


--

"If to err is human, then I must be some kind of human!" -Me

 

[ZiggyS1]

ok,

so back to your original request.....you have an Import process in place, that process should be able to give you the Path and Filename ( as my example does), the object name you should know because the import process would have to be coded to choose or you could evaluate based on file extension.

isn't that enough info to store in a table? What else are you trying to grab...sorry if you said it above.

 

[kjv1611]

No problem.

Let me try to rephrase the setup and explain differently...

I have an "automated" import process which does not use the built-in import wizards. As such, b/c of doing the import via VBA, I indeed have access to all the possible info - that's great.

What I'm asking about in this thread is for those that might need to use the "manual" import method, as I'm calling it which is where they will use the Access import wizards. Currently, if I tell Access:
DoCmd.RunCommand acCmdImportAttachExcel
for example...

I cannot grab the file and object information from the Access import wizard. THAT's where I'm going to be missing data if I can't get around that.

The reason I want to allow for that is it seems innevitable that someone will try to import something into Access that just won't work correclty with the automatic methods. And as I said earlier, it's very possible I'm putting just too much concern on the possibilities.

--

"If to err is human, then I must be some kind of human!" -Me

 

[ZiggyS1]

ok I get it now. that has always been a problem with the wizard because it only reads the first few rows to determine the datatype. The only thing I an suggest is to add the Wizard control references, but I don't know how to work with them if you can even find it.

Other than that, use a schema to import all (manuals) as Text into a temp table, using your automation and Pass the info to a table but also with A Flag. then create a process to deal with those tables...get creative with making a options to convert fields to data types specified on a form and run a maketable query to create the corrected table and delete the old one....some of what I'm saying may be more work than it's worth, but I just thought I'd put it out there.

 

[kjv1611]

Yeah, that's what I'm growing afraid of - I'm delving off into something that just aint worth the effort involved. [LOL]

I mean, the whole purpose is just to pull a random sampling of data - no calculations necessary. One thing I've tinkered with is just having an "all text" Import Spec, and reference that from the TransferSpreadsheet event. Even with that, I had one or two worksheets I tested that gave "import error" tables. But for the life of me, I don't remember if they were the standard - text in a numeric field type deal or whether it was unrelated. I'll get something sorted out, one way or another.

Thanks for the thoughts, though. I mean, that methodology sounds like it would work, if I can pull together the correct references, objects, etc.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, what I think I'll try to do (which is one of the options I had thought of earlier, but just didn't get around to implementing) is to force all through the automated process first. Then those with import errors, I can either spit out to a manual process (and that way I'll have the file info anyway), and/or run the other VBA I've got in another database to go and fix up those that errored out. It worked well for the other database, even though it's practically not necessary anymore, thankfully! :0)

Just thought I'd post the thought process.

--

"If to err is human, then I must be some kind of human!" -Me