I think you can import from hidden sheets in 2007...at least, I think I tried it and it worked fine. For my need, they have to be hidden for the users who use the sheet, but when it comes time to import I guess I could go in and unhide each of the five sheets manually (or programmatically). No biggie there.
Here is what I am using code wise to list the sheets found in a certain directory in a listbox on an access form, then I have a click that imports them....this is easy when they are simple .csv files (which I am also thinking, hmmm could I just do a programmatic save-as in excel and save the hidden sheet ONLY as a .csv file in this directory, because when that happens, ONLY the non-blank rows come in...perhaps .csv is the way to go?? Just brainstorming.)
Private Sub Form_Load()
Call ListFiles("F:\factsdata\IMPORT\", , , Me.lstFileList)
Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
Optional bIncludeSubfolders As Boolean, Optional lst As ListBox)
On Error GoTo Err_Handler
'Purpose: List the files in the path.
'Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
' lst: if you pass in a list box, items are added to it. If not, files are listed to immediate window.
' The list box must have its Row Source Type property set to Value List.
'Method: FilDir() adds items to a collection, calling itself recursively for subfolders.
Dim colDirList As New Collection
Dim varItem As Variant
Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
If lst Is Nothing Then
For Each varItem In colDirList
Debug.Print varItem
Next
Else
For Each varItem In colDirList
lst.AddItem varItem
Next
End If
Exit_Handler:
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Function
Private Function FillDir(colDirList As Collection, ByVal strFolder As String, strFileSpec As String, _
bIncludeSubfolders As Boolean)
'Build up a list of files, and then add add to this list, any additional folders
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colDirList.Add strTemp
strTemp = Dir
Loop
If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
Next vFolderName
End If
End Function
Public Function TrailingSlash(varIn As Variant) As String
If Len(varIn) > 0& Then
If Right(varIn, 1&) = "\" Then
TrailingSlash = varIn
Else
TrailingSlash = varIn & "\"
End If
End If
End Function
Now on import_click() I have the following for importing .csv files (this is all code from another database of mine I am implementing for this new database).
Private Sub import_Click()
Const strPath As String = "F:\factsdata\IMPORT\" 'Directory Path
Dim strFile As String 'Filename for importing from
Dim oItem As Variant 'each item selected in listbox
Dim iCount As Integer 'how many iterations
Dim tablenum As String 'Table name importing to
iCount = 0
' Loop through the list
If Me!lstFileList.ItemsSelected.Count <> 0 Then
For Each oItem In Me!lstFileList.ItemsSelected
If iCount = 0 Then
strFile = strFile & Me!lstFileList.ItemData(oItem)
iCount = iCount + 1
tablenum = Mid(strFile, 9, 6)
Else
strFile = strFile & Me!lstFileList.ItemData(oItem)
tablenum = Mid(strFile, 9, 6)
DoCmd.TransferText acImportDelimi, , tablenum, strPath & strFile, True
iCount = iCount + 1
End If
strFile = ""
Next oItem
MsgBox Me!lstFileList.ItemsSelected.Count & " Files were Imported"
DoCmd.SetWarnings (warningsoff)
'Logging of import function
mysql = "UPDATE UserLogtbl SET UserLogtbl.imported = -1, dateimported = Now()"
mysql = mysql + " WHERE ((UserLogtbl.LogID) = [Forms]![Main Menu Switchboard]![logid])"
DoCmd.RunSQL mysql
DoCmd.SetWarnings (warningson)
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If
This will probably do everything I need - it works fine for my old dbase except:
1. It imports into separate tables
2. I need help with getting the worksheet from Excel into a .csv file 
or somehow just listing the workbooks and then importing the ONE hidden sheet without the blank lines 
(preferred).
LinkBack URL
About LinkBacks
Import from 5 Excel Workbooks into one Access Table
Register To Reply
Bookmarks