INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Inport excel file with several different tabs

Inport excel file with several different tabs

(OP)
I would like to import an Excel Workbook containing multiple dissimilar tabs. I am using office 2013. I have imported excel sheets before using a saved import wizard. But, only for an individual sheet. I could build multiple saved imports but can't because the makeup of the excel file and tabs is dynamic. Imports will happen several times a month.

Situation:
Excel document with multiple tabs (number of tabs not constant)
Each Tab can have different headers and data.
Each tab has a name (Not Sheet1, sheet2, etc)

The excel file name is constant.


Desire:
MS Access searches the excel file and imports data into separate tables based on the excel tab names.

All the tables, if present in Access, will be over written every time the spreadsheet is imported. Of course, some new tables may be created if more tabs are added.

In advance....thanks,

After the data is imported to "x" number of tables I will build code to analyze each and disburse the data.

RE: Inport excel file with several different tabs

https://support.office.com/en-us/article/Import-or...

In general, if you know the sheet name...

CODE

Select *
From [SheetName$] 

Notice 1) the brackets and 2) the $ after the sheet name.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Inport excel file with several different tabs

(OP)
Thanks Skip but I probably will not know the sheet names. Can't access find that from the workbook?

RE: Inport excel file with several different tabs

You can easily find all WorkSheets' names from selected Excel file:

CODE

Dim xlApp As Object
Dim S As Integer

Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Workbooks.Open FileName:="C:\Data\ExcelFile.xlsx"
    .Visible = True
    For S = 1 To .Sheets.Count
        MsgBox "Sheet" & S & " name is " & .Sheets(S).Name
    Next S
End With 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Inport excel file with several different tabs

(OP)
OK Andy. I have seen similar code but yours seems to be less complicated.

So, after your process runs I will want to import data from each excel sheet and store it (Create Table) in tables named the sheet names. Do I store the values your code produces in a table or just as variables to be use when importing the data. I intend to add the rest of the import code - import data and table creation - after the end of your code. The subject spreadsheet will always be stored in the same location with the same name.

RE: Inport excel file with several different tabs

CODE

Dim xlApp As Object
Dim ws As Object
dim sSQL As String

Set xlApp = CreateObject("Excel.Application")

With xlApp
    With .Workbooks.Open (FileName:="C:\Data\ExcelFile.xlsx")
       XlApp.Visible = True
       sSQL = ""
       For Each ws in .Worksheets
           sSQL = "Select * From [" & ws.Name & "$]"
'....
       Next S
       .Close
    End With
End With 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Inport excel file with several different tabs

Skip,
Can puforee do this in one step?

       ...
       For Each ws in .Worksheets
           sSQL = "Select * INTO " & ws.Name & " From [" & ws.Name & "$]"
           CurrentDb.Execute sSQL, dbFailOnError
       Next ws
       ...
 

Assuming there are no tables in the data base....

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Inport excel file with several different tabs

I like this method, but found that it truncates long text. Has anyone encountered that or how to fix it? Ended up doing a standard import using transfer text for the sheets with long text and using query method like above for short text.

RE: Inport excel file with several different tabs

(OP)
Skip/Andy,

So, where are the TAB names stored...for future use when importing and saving each sheets data?

RE: Inport excel file with several different tabs

By "tab" names I assume you mean sheet names.

Name is a property of the Sheet and Worksheet object, so you need to access the Sheets or Worksheet Collection. You don't care about sheets other than worksheets, hence...

CODE

Dim ws As object

For Each ws In Worksheets
   Debug.Print ws.Name
Next 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Inport excel file with several different tabs

(OP)
OK. This is what I have so far and I keep failing the DoCmd step. Please help. I would really like to use the Variable T where the ".Sheets(S).Name" statements are in the DoCmd statement but I don't have to if .Sheets(S).Name can be used. I am sure I have syntax issues.

Am I even close? Do I have the correct DIM statements or do I need more.

CODE -->

Private Sub Command0_Click()
Dim xlApp As Object
Dim S As Integer
'T appended with S will be variable to store Tab names
Dim T As String

Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Workbooks.Open FileName:="C:\Data\2017_BinaryKey_Log_Microsimjjs.xlsx"
    .Visible = False
    For S = 1 To .Sheets.Count
        MsgBox "Sheet" & S & " name is " & .Sheets(S).Name
        T = .Sheets(S).Name
        MsgBox T
        DoCmd.TransferSpreadsheet acImport, 12, .Sheets(S).Name, "C:\Data\2017_BinaryKey_Log_Microsimjjs.xlsx", True, .Sheets(S).Name

'fileName is a variable with the full path and filename of the excel workbook
    Next S
End With

    

End Sub 

Thanks,

RE: Inport excel file with several different tabs

(OP)
PS. The table names I will import the data to, come from the .sheets(S).name and the may or may not exist. In this test DB I have shown the code for...they do NOT exist. I am attempting to have the import create them.

RE: Inport excel file with several different tabs

Depending on the data in the sheet, you could use:
.Sheets(S).UsedRange.Rows.Count
or
.Sheets(S).Range("A1").CurrentRegion.Rows.Count
to get either the number of rows in used range (not necesssary starting in A1 or non empty) or rows in area filled with data around cell A1.
You can find last filled cell in column (here A) with:
.Sheets(S).Cells(.Sheets(S).Rows.Count, "A").End(xlUp).Row

combo

RE: Inport excel file with several different tabs

(OP)
Good morning. Based on the story above I simplified my import to prove it works. Here is what works:

CODE -->

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "MicrosimDev", "C:\Data\2017_BinaryKey_Log_Microsimjjs.xlsx", True, "MicrosimDev!"
MsgBox "Done" 
This is part of the code below.

Note the ! at the end of the DoCmd. This evidently signifies that MicrosimDev is a TAB in the spreadsheet.

Now looking at the complete code that finds each TAB name in the spreadsheet. In this code I substituted T for the Table name in the Transfer statement. This works.

CODE -->

Private Sub Command0_Click()
Dim xlApp As Object
Dim S As Integer
'T appended with S will be variable to store Tab names
Dim T As String

'Start the find TABS code
Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Workbooks.Open FileName:="C:\Data\2017_BinaryKey_Log_Microsimjjs.xlsx"
    .Visible = False
    For S = 1 To .Sheets.Count
        MsgBox "Sheet" & S & " name is " & .Sheets(S).Name
        T = .Sheets(S).Name
        MsgBox T
        'Use the results of the find TAB's code to bring in the first TAB of the spreadsheet.
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, T, "C:\Data\2017_BinaryKey_Log_Microsimjjs.xlsx", True, "MicrosimDev!"
        MsgBox "Done"

'fileName is a variable with the full path and filename of the excel workbook
'Loop back to find TAB code for next TAB inport
    Next S
End With
End Sub 

The final part to this puzzle is to substitute T for the last item in the transfer statement (In Red). The statement needs the TAB name T and the ! character. I have tried severs combinations but can't seem to get it to work.

Thanks,


RE: Inport excel file with several different tabs

(OP)
Wow...I finally hit the correct syntax. Here is the code to Transferspreadsheet with multiple unknown TABs into individual tables in Access. The first part of the code was provided by Andrzejek and uses a loop to find each TAB name in a given spreadsheet one at a time. The Transfer part of the code is used each time a TAB name is found and imports the spreadsheet tab data into a table name as the tab name. The message boxes are commented out but were used to check each step along the way.

Thank you all for your help.

CODE -->

Private Sub Command0_Click()
Dim xlApp As Object
Dim S As Integer
'T appended with S will be variable to store Tab names
Dim T As String

'Start the find TABS code
Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Workbooks.Open FileName:="C:\Data\2017_BinaryKey_Log_Microsimjjs.xlsx"
    .Visible = False
    For S = 1 To .Sheets.Count
        'MsgBox "Sheet" & S & " name is " & .Sheets(S).Name
        T = .Sheets(S).Name
        'MsgBox T
        'Use the results of the find TAB's code to bring in the first TAB of the spreadsheet.
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, T, "C:\Data\2017_BinaryKey_Log_Microsimjjs.xlsx", True, T & "!"
        'MsgBox "Done"

'fileName is a variable with the full path and filename of the excel workbook
'Loop back to find TAB code for next TAB inport
    Next S
End With
End Sub 

RE: Inport excel file with several different tabs

Good job! thumbsup2
Consider this - a little simpler - version:

CODE

Private Sub Command0_Click()
Dim xlApp As Object
Dim S As Integer
Dim strFileName As String

strFileName = "C:\Data\2017_BinaryKey_Log_Microsimjjs.xlsx"
Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Workbooks.Open FileName:= strFileName
    '.Visible = False  'Default is False, isn't it?
    For S = 1 To .Sheets.Count
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
              .Sheets(S).Name, strFileName, True, _
              .Sheets(S).Name & "!"
    Next S
End With

End Sub 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Inport excel file with several different tabs

(OP)
I will use your version...more compact is better. What is the '.Visible = False 'Default is False, isn't it? for. Is it asking me a question? If it is, yes I don't want the spreadsheet to show. And, will the .close code above (from one of Skips answers) cause the spreadsheet to close? If it does, I need it. I get a little interference when I try to open the spreadsheet after running my code.

Thanks,

RE: Inport excel file with several different tabs

If you don't want to see Excel, by default it is not Visible (I think)
To close Excel, try either .Close or .Quit - I can never remember which one, but one of them should close your Excel.
Set xlApp = Nothing is not really necessary, since xlApp object will run out of scope, but it is a good practice to clean your stuff after use.

CODE

...
    Next S
    '.Close
    .Quit
End With
Set xlApp = Nothing

End Sub 

After successful run, hit Ctrl-Shift-Esc (to get to Task Manager) to see if you have any Excel running. Assuming you did not start any other Excel.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Inport excel file with several different tabs

(OP)
Fantastic. And the winner is drum roll .Quit

Thanks again for all the help.

RE: Inport excel file with several different tabs

(OP)
I have worked the process several times and I have found one issue. The TransferSpreadsheet does NOT over write the Table if it is already there. It adds data to the table giving me duplicates. I am now hunting for a solution to this.

RE: Inport excel file with several different tabs

(OP)
I found a way but it is not elegant. Is there something better I hope?

CODE -->

Dim xlApp As Object
Dim S As Integer
Dim strFileName As String

strFileName = "\\nw\data\Maint_Train\MT Administration\DB\2017_BinaryKey_Log_Microsim_Master.xlsx"
Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Workbooks.Open FileName:=strFileName
    '.Visible = False  'Default is False, isn't it?
    For S = 1 To .sheets.Count
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
              .sheets(S).Name & "_Import", strFileName, True, _
              .sheets(S).Name & "!"
        DoCmd.DeleteObject acTable, .sheets(S).Name & "_Import"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
              .sheets(S).Name & "_Import", strFileName, True, _
              .sheets(S).Name & "!"
      
    Next S
    '.Close
    .Quit
End With
Set xlApp = Nothing 

RE: Inport excel file with several different tabs

Try something like this:

CODE

With xlApp
    .Workbooks.Open FileName:= strFileName
    For S = 1 To .Sheets.Count
        DoCmd.RunSQL  "Delete from " & .Sheets(S).Name & "_Import"

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
              .Sheets(S).Name & "_Import", strFileName, True, _
              .Sheets(S).Name & "!"
    Next S
End With 

You may want to wrap all of this in some kind of Error Handler in case a table does not exist that you want to delete the data from.

Or just get rid of first:
DoCmd.TransferSpreadsheet
line

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Inport excel file with several different tabs

(OP)
Won't this still throw an error if the SQL does not find the table?

RE: Inport excel file with several different tabs

Yes, it will error, that's why I said: "You may want to wrap all of this in some kind of Error Handler in case a table does not exist that you want to delete the data from."

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close