Dear All,

Please how can I copy or insert the filename (workbook) without the .xls
extension in the first column of the access table and the worksheet name
(worksheet tab) in the second column of an access table?

Please I am working on a cost accounting document of
a company. The workbook is named after the cost center name eg Sales and the
Worksheet is named after the general ledger code (GLCode eg EE00875). After
trasfering all worksheets of all workbooks into one access table using the
TransferSpreadsheet method (this is successful), I created two columns using
the RunSQL and ALTER TABLE statement (this also is successful). I now want
to fill
these two access columns with the workbooknames without the .xls extension
and the
worksheetnames in the access table. This does not work. I am using the INSERT
INTO-- VALUES statement. See the code below. Should I also try the UPDATE
statement.


Private Sub Command7_Click()
Dim xlApp As Excel.Application
Dim xlWS As Excel.Worksheet
Dim xlWB As Excel.Workbook
Dim i As Integer
Dim strFileName As String


Dim wkShName As String
Dim strFolderPath As String
Dim strPath As String
Dim strPathBrowser As String
Dim bookName As String
Dim strFileNameValue As String
Dim strFullPath As String
Dim j As Integer

Set xlApp = New Excel.Application
On Error Resume Next
strPath = "C:\Documents and Settings\a99858\My Documents\"
strFileName = Dir(strPath & "*.xls")

strFullPath = strPath & strFileName
Do While Len(strFileName) 0


strFullPath = strPath & strFileName
strFileNameValue = strFileName

xlApp.Workbooks.Open (strFullPath)

For j = 1 To xlApp.Worksheets.count
Set xlWS = xlApp.ActiveWorkbook.Worksheets(j)
wkShName = xlWS.Name

DoCmd.TransferSpreadsheet acImport, , "MultiSheet_Example", strFullPath, -1,
wkShName & "!A1:F8"

DoCmd.RunSQL "ALTER TABLE MultiSheet_Example ADD COLUMN CCCode CHAR, GCode
CHAR", -1
DoCmd.RunSQL "INSERT INTO MultiSheet_Example (CCCode ,GCode) VALUES (&
strFileNameValue, & wkShName)"

Next j

strFileName = Dir()
Loop

End Sub


Please any idea on how to insert the workbooknames and corresponding
worksheet names into the access columns created?

Thanks
Gokop