|
how to copy workbook names and worksheet names to columns in acces
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 |
how to copy workbook names and worksheet names to columns in acces
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" wrote in message ... 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 |
how to copy workbook names and worksheet names to columns in a
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" wrote in message ... 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 |
how to copy workbook names and worksheet names to columns in a
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" wrote in message ... 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 |
how to copy workbook names and worksheet names to columns in a
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" wrote in message ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 10:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com