Is there a way to Automate imports?

  • Thread starter Thread starter Cloudy
  • Start date Start date
C

Cloudy

Basically i have a huge excel file with many sheets in it. Usually it will be
the manual way of importing these sheets in. Is there anyway to automate this
process? It is going to be a table per sheet.

Please advise if there is any solutions. Thanks
 
Cloudy,

Use automation to do this. If each worksheet is going into a separate
table, and the table name and worksheet name are not the same, I would
create a table to store the worksheet name and the destination table name.
If you use such a table, you can remove all of the code in this module
except the last loop, and instead of looping through the array, you would
open a recordset based on the afore mentioned table, and loop through the
various records. If, on the other hand, you just want to import all of the
worksheets in the workbook into tables with the same name as the worksheet,
it might look somthing like the following.This subroutine uses late binding
so it doesn't matter which version of Excel you have.

Public Sub ImportFromExcel(Filename As String)

Dim oXL As Object ' Excel.Application
Dim oWbk As Object ' Excel.Workbook
Dim oSht As Object 'Excel.Worksheet
Dim intSht As Integer
Dim xlOpen As Boolean
Dim wkshtArray() As String
Dim strTableName As String, strRange As String

'Set inline error handling for this initial step
On Error Resume Next

'Use GetObject to select the open instance of Excel, if one is open
'If open, then set the variable xlOpen to true, so as not to close it on
the way out
'If XL is not already open, then open it and set xlOpen to False
Set oXL = GetObject(, "Excel.Application")
If Err.Number = 0 Then
xlOpen = True
Else
xlOpen = False
Set oXL = CreateObject("Excel.Application")
End If

'Make Excel visible
oXL.Visible = True

'Open the workbook
Set oWbk = oXL.Workbooks.Open(Filename)

ReDim wkshtArray(oWbk.Sheets.Count - 1)
intSht = 0
For Each oSht In oWbk.Sheets
wkshtArray(intSht) = oSht.Name
intSht = intSht + 1
Next

oWbk.Close
Set oWbk = Nothing

oXL.Quit
Set oXL = Nothing

For intSht = LBound(wkshtArray) To UBound(wkshtArray)
Debug.Print wkshtArray(intSht)
strTableName = wkshtArray(intSht)
strRange = wkshtArray(intSht) & "!"
DoCmd.TransferSpreadsheet acImport, , strTableName, Filename, True,
strRange
Next

End Sub
 
Okay thanks for responding but i am having INVALID SQL ERROR after trying
what you gave me.

Lets say my excel file name is "apple".

So basically what i input is:


Public Sub ImportFromExcel(apple As String)

Dim oXL As Object ' Excel.Application
Dim oWbk As Object ' Excel.Workbook
Dim oSht As Object 'Excel.Worksheet
Dim intSht As Integer
Dim xlOpen As Boolean
Dim wkshtArray() As String
Dim strTableName As String, strRange As String


On Error Resume Next


Set oXL = GetObject(, "Excel.Application")
If Err.Number = 0 Then
xlOpen = True
Else
xlOpen = False
Set oXL = CreateObject("Excel.Application")
End If


oXL.Visible = True


Set oWbk = oXL.Workbooks.Open(apple)

ReDim wkshtArray(oWbk.Sheets.Count - 1)
intSht = 0
For Each oSht In oWbk.Sheets
wkshtArray(intSht) = oSht.Name
intSht = intSht + 1
Next

oWbk.Close
Set oWbk = Nothing

oXL.Quit
Set oXL = Nothing

For intSht = LBound(wkshtArray) To UBound(wkshtArray)
Debug.Print wkshtArray(intSht)
strTableName = wkshtArray(intSht)
strRange = wkshtArray(intSht) & "!"
DoCmd.TransferSpreadsheet acImport, , strTableName, Filename, True, strRange
Next

End Sub
 
What line gets highlighted when you get this Invalid SQL Error?

You subroutine statement should still read:

Public Sub ImportFromExcel(Filename as String)

and the following line should still state "Filename"

Set oWbk = oXL.Workbooks.Open(Filename)


When you call the subroutine you should use something like:

Call ImportFromExcel("C:\Temp\apple.xls")

Dale
 
After re-reading all the replies on this post, i went to try again but
unfortunately it still doesn't work for me.

These are the steps i did.

1. Open a blank access file.

2. Go to Insert --> Module

3. Input:

Public Sub ImportFromExcel(filename As String)

Dim oXL As Object ' Excel.Application
Dim oWbk As Object ' Excel.Workbook
Dim oSht As Object 'Excel.Worksheet
Dim intSht As Integer
Dim xlOpen As Boolean
Dim wkshtArray() As String
Dim strTableName As String, strRange As String


On Error Resume Next


Set oXL = GetObject(, "Excel.Application")
If Err.Number = 0 Then
xlOpen = True
Else
xlOpen = False
Set oXL = CreateObject("Excel.Application")
End If


oXL.Visible = True


Set oWbk = oXL.Workbooks.Open(filename)

ReDim wkshtArray(oWbk.Sheets.Count - 1)
intSht = 0
For Each oSht In oWbk.Sheets
wkshtArray(intSht) = oSht.Name
intSht = intSht + 1
Next

oWbk.Close
Set oWbk = Nothing

oXL.Quit
Set oXL = Nothing

For intSht = LBound(wkshtArray) To UBound(wkshtArray)
Debug.Print wkshtArray(intSht)
strTableName = wkshtArray(intSht)
strRange = wkshtArray(intSht) & "!"
DoCmd.TransferSpreadsheet acImport, , strTableName, filename, True, strRange
Next




End Sub

Call ImportFromExcel("P:\Segment\Damien\AT_MIS\Testings\test")

4. Click on run and save it as a macro, "ABC".

5. Lastly i go to SQL view and input:

Run sub(ABC);



An error that says "expected delete, insert.. blah blah" popped up.
Can you advise where did i went wrong?
 
OK,

Now I understand your problem. I did not realize you were trying to run
this code from a query. Sorry, you cannot do that, you will have to run it
from a command button , some other event tied to another control, or from
the VB editor. For the time being, lets start with the VB editor.

1. Just before the End Sub statement, add another line that reads:

msgbox "Done!"

2. Just before your step 4, you have a line that reads:

Call ImportFromExcel("P:\Segment\Damien\AT_MIS\Testings\Test")

Cut this line out of the module. It should not be in a module, but should
actually be in the click event of a command button. But as I indicated
above, lets crawl first and run it from the VB editor. Do you have the
Immediate window visible in the VBA Editor? If not, go to View and click on
the selection for "Immediate Window". This will add a box below the code
window. Paste the command you cut out of your module into this area.

Next. Does the file "test" have an extension? Maybe "test.xls"? If so,
make sure that it says "test.xls" instead of just "test". Now, go to the
end of that line and hit the 'Enter' button. That should run the code, and
you should see the names of the worksheets that are being loaded being
printed in the Immediate window. When it is done, you should see a dialog
box pop-up on your screen.

Just forget about the steps you describe as #4 and #5. Let me know how this
works.

Dale
 
Back
Top