how to copy workbook names and worksheet names to columns in acces

[Guest]

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

 

[Mike Fogleman]

It looks like a syntax error using variables for the values.

DoCmd.RunSQL "INSERT INTO MultiSheet_Example (CCCode ,GCode) VALUES (&
strFileNameValue, & wkShName)"

The variables should be outside the quotes

DoCmd.RunSQL "INSERT INTO MultiSheet_Example (CCCode ,GCode) VALUES ("&
strFileNameValue &"," & wkShName &")"

Carefully undo the word wrap in your code.
To remove the .xls from the name use the LEFT and LEN function to remove the
last 4 characters from the string.

strFileNameValue = LEFT(strFileNameValue, LEN(strFileNameValue)-4)

So your code would look like this:
strFileNameValue = LEFT(strFileNameValue, LEN(strFileNameValue)-4)
DoCmd.RunSQL "INSERT INTO MultiSheet_Example (CCCode ,GCode) VALUES ("&
strFileNameValue &"," & wkShName &")"

Good luck,
Mike F

 

[Guest]

Dear Mike,

Thank you very much. I will try it right away.

Kind regards
Gokop

 

[Guest]

Dear Mike,

Thank you once more. When I ran the code after making the adjustments you
made i.e
strFileNameValue = LEFT(strFileNameValue, LEN(strFileNameValue)-4)

DoCmd.RunSQL "INSERT INTO MultiSheet_Example (CCCode ,GCode) VALUES ("&
strFileNameValue &"," & wkShName &")"

It was prompting me to enter the strFileVameName and wkShName. When I
entered them, it appended the entries in the correct columns (CCCode and
GCode) but not beside the corresponding data. I mean it appended them above
the data. Again I thought it should not ask for parameter, it should just use
the workbook names and worksheet names. How cai I specifiy the values of the
workbook names and worksheet names?

Kind regards
Gokop

 

[Mike Fogleman]

I am not that great with Access, so I really am not sure how to get you what
you want. Have you tried posting in the Access newsgroup?

Mike F