Inport excel file with several different tabs 1

[puforee]

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.

 

[SkipVought]

https://support.office.com/en-us/ar...workbook-a1952878-7c58-47b1-893d-e084913cc958

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

Select *
From [SheetName$]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[puforee]

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

 

[Andrzejek]

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

Dim xlApp As Object
Dim S As Integer

Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Workbooks.Open FileName:=[red]"C:\Data\ExcelFile.xlsx"[/red]
    .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.

 

[puforee]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Skip,
Can puforee do this in one step?

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

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[sxschech]

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.

 

[puforee]

Skip/Andy,

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

 

[SkipVought]

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...

Dim ws As object

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[puforee]

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.

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,

 

[puforee]

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.

 

[combo]

Depending on the data in the sheet, you could use:
[tt].Sheets(S).UsedRange.Rows.Count[/tt]
or
[tt].Sheets(S).Range("A1").CurrentRegion.Rows.Count[/tt]
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:
[tt].Sheets(S).Cells(.Sheets(S).Rows.Count, "A").End(xlUp).Row[/tt]


combo

 

[puforee]

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

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.

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, "[COLOR=#EF2929]MicrosimDev[/color][b][/b]!"
        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,

 

[puforee]

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.

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

 

[Andrzejek]

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

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
[green]    '.Visible = False  'Default is False, isn't it?[/green]
    For S = 1 To .Sheets.Count
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
              [blue].Sheets(S).Name[/blue], strFileName, True, _
              [blue].Sheets(S).Name[/blue] & "!"
    Next S
End With

End Sub

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

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,

 

[Andrzejek]

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.
[tt]Set xlApp = Nothing[/tt] is not really necessary, since xlApp object will run out of scope, but it is a good practice to clean your stuff after use.

   ...
    Next S[green]
    '.Close[/green]
    .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.

 

[puforee]

Fantastic. And the winner is drum roll .Quit

Thanks again for all the help.

 

[puforee]

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.