How to select a folder without the common dialog in access

[bdthomas02]

Hello everyone,

I am very good with Access and I know VB (I am not an expert but I understand the code). Here is what I am trying to do with less code as possible:

1. I want to create a button in Access that will allow me to navigate to a folder (the folder is located in a tree three or four folders down) using the folder dialog option. I do not want to use the commondialog button but would rather hard code if possible.

2. Once I locate the folder I want to click the folder and automatically import certain files that are in the folder into Access. The folder will contain files such as jpg, doc, txt, xls, xml, and gifs. I only want to import all xml files into Access.

Is there a simple and quick way to do this? I need to be able to use this in other projects as well.

Also, I want to call this procedure from a module.

Thanks to anyone who can help me with this issue.

Thanks,

bdthomas02

:help :help :help :help

[Mavyak]

Option Compare Database
Sub getXMLfiles()
Dim fso As FileSystemObject
Dim fld As Folder
Dim f As File
Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd
.AllowMultiSelect = False
.ButtonName = "Select"
.Title = "Select the folder containing XML files:"
.Show
If .SelectedItems.Count > 0 Then
Set fso = New FileSystemObject
Set fld = fso.GetFolder(.SelectedItems(1))
Else
GoTo ExitSub
End If
End With
For Each f In fld.Files
If UCase(Right(f.Name, 4)) = ".XML" Then
Debug.Print f.Name '<--Here's where you do what you need to do with teh XML files.
End If
Next f
ExitSub:
Set fd = Nothing
Set f = Nothing
Set fld = Nothing
Set fso = Nothing
End Sub


You'll need a reference set to the Microsoft Scripting Runtime Library for the above to work.

[bdthomas02]

Thanks for the code. I found something similar but your code is easier to read and understand. I have run into another obstacle and maybe you can help me out. I need to import the xml files into Access. Is there code out there that will allow me to do this? I do not want to use any controls to do this but would like to hard code it. Can you help me out in this area?

Thanks,

bdthomas02
:friends: :friends: :friends:

[Mavyak]

Replace this line:
Debug.Print f.Name '<--Here's where you do what you need to do with teh XML files.

with this one:
ImportXML f.Path, acStructureAndData

XML is quirky sometimes. It's usually a good idea to have an *.xsd file for each of your *.xml files to make sure the data imports correctly.

[bdthomas02]

Here is the code I am using to import xml files into Access. The first file is imported but I receive an error message when the second file is trying to be imported into Access. Can you tell me what is wrong? Here is the code:
Private Sub cmdClick_Click()

'Directory Path
Const strPath As String = "C:\Files\"

'Filename
Dim strFile As String

'File Array
Dim strFileList() As String

'File Number
Dim intFile As Integer

'Loop through the folder & build file list
strFile = Dir(strPath & "*.xml")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend

'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If

'cycle through the list of files & import to Access
'creating a new table called MyTable
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImport, , _
"MyTable", strPath & strFileList(intFile), True

Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub
~VBA tags added by Oorang

Also, is there a way to capture the total file count and display it in Access?

[Mavyak]

I'm surprised that the first file successfully imports considering the first argument you supply to the DoCmd.TransferText method is not valid according to the help for that command:

TransferType Optional AcTextTransferType.

AcTextTransferType can be one of these AcTextTransferType constants.
acExportDelim
acExportFixed
acExportHTML
acExportMerge
acImportDelim default
acImportFixed
acImportHTML
acLinkDelim
acLinkFixed
acLinkHTML
Are the files you are importing actually xml files or are they text files with *.xml extensions?


Edit: I suspect the first file imports ok because acImport is a named constant representing an integer value (in this case 0). The named constant for the TransferText method that equates to 0 is acImportDelim. Long story short, even though you type in acImport, to Access that means integer value zero which, to the TransferText method equals acImportDelim.

[bdthomas02]

I am trying to import xml files but I was using .txt files as a practice source. How should I write my code? Should I write it as DoCmd.TransferText acImportDelim or another way?

Thanks,

bdthomas

[Mavyak]

Use the ImportXML method (see post #4).