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

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

"gokop" <(E-Mail Removed)> wrote in message
news919570B-CA97-4817-B197-(E-Mail Removed)...
> 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
>
>

Dear Mike,

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

Kind regards
Gokop

"Mike Fogleman" wrote:

> 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
>
> "gokop" <(E-Mail Removed)> wrote in message
> news919570B-CA97-4817-B197-(E-Mail Removed)...
> > 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
> >
> >
>
>
>

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" wrote:

> 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
>
> "gokop" <(E-Mail Removed)> wrote in message
> news919570B-CA97-4817-B197-(E-Mail Removed)...
> > 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
> >
> >
>
>
>

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
"gokop" <(E-Mail Removed)> wrote in message
news:B2280238-FA6B-40E7-ACB8-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>> "gokop" <(E-Mail Removed)> wrote in message
>> news919570B-CA97-4817-B197-(E-Mail Removed)...
>> > 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
>> >
>> >
>>
>>
>>