i have several workbooks that have multi tabs. i need to generate a workbook
for each tab, so that i can import the books to access to build a master
table. the tabs are different in each excel workbook. therefore, i would like
to generate standard names when extracting each tab. for example, tab 1 would
be stdbook1, tab 2 would be stdbook2. that way i can build an append query
in access that will take each standard name and load to a master table,
regardless of original name in excel.

i got this code from an earlier request:

Sub createbk()
Dim wks As Worksheet
Dim wbkNew As Workbook
Dim lng As Long

lng = 1
For Each wks In ThisWorkbook.Worksheets
wks.Copy
Set wbkNew = ActiveWorkbook
wbkNew.SaveAs ThisWorkbook.Path & "\stdBook" & lng & ".xls"
wbknew.close
lng = lng + 1
Next wks

End Sub

the problem is only takes the first tab and stops.

plus i can not control the path

so i tried something like this:

placed a dim statement above - dim path as long

then in the wks.copy section had
path = "G.\master folder\build access table"

then kept the rest of the code.

that did not work.


i tried building vb code in access to take each tab from the workbook and
then import but that did not work, so i went the above route.

so. how do i get it so it will go past the first tab and how do i build the
path for the output location

thanks much