Script to import from Excel sheets

K

-Karl

I know this seems to be a huge topic but so little working code on
this. I found a script for access 2007 that will allow me to open an
excel file.

The problem is now I need to tell it to focus (Activate) on a
particular sheet abnd copy all the data from it then put it into an
Access table that I have already created.

Below is the code I have that actually opens the file. I have a form
that has INPUT1 for the path and INPUT 2 for the file name.

Can anyone explain what I need to do to finish the process?

Many thanks!!




Sub test()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String
Dim rs, intMaxCol

'Dim objWkb As Workbook
'Dim objSht As Worksheet


Set rs = CurrentDb.OpenRecordset("Test", dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast:
End If



' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = """" & Forms.Form1!Text1.Value & "\" & Forms.Form1!
Text2.Value & ".xlsm" & """"
'MsgBox sFullPath
'End



' Open it
With oXL
.Visible = True
.Workbooks.Open (sFullPath)
'.Workbooks.Activate ("switches") <-- Not supported?!
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub
 
K

-Karl

Wow, thanks. I'm playing around with it and am having an issue with
the error "Field F1 is not valid" or something to that effect.So I
looked around a bit on the internet and it seems that it's not reading
my 1st row properly, if I understand it correctly.

I need to start over and look a bit closer into the script. I read a
bit about the function on MSDN but I really hate how little usefull
information they have (Example scripts). Still, thank god for Google!

I might look into the script late tonight if possible.

Thanks for pointing the site out to me!
 
K

-Karl

Have a bit of a snaffu and I haven't figured it out. I am using Excel
2007 macro enabled sheets (.xlxs) and when I try to import the data,
it doesn't work. I've played and looked a bit on the
acSpreadsheetTypeExcel option but I haven't quite seen a list for the
new excel 2007 editions

Any suggestions? I've tried acSpreadsheetTypeExcel12 and
acSpreadsheetTypeExcel9 and oddly, I even saved the swoorkbook as an
excel 2003 edition and got the same results. It creates the table in
access, gives 1 field and names it phillidelphia ?!. I'm sure it's
just a bit confused over the structure in the new .XLXS format???

Thoughts?
 
K

Ken Snell [MVP]

Post your macro actions with arguments that you're trying to use, so that we
can see its structure and syntax.
 
K

-Karl

In 1 command:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Asses Info", "c:\temp\1\GLC_Regional_Database_v.
2.xls", True






or as a Function

Dim strPathFile As String, strFile As String, strPath As
String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL
worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
'strPath = "S:\_oDesk Projects\Project 1\Reporting DB\Reports
7-22-09\GLC\"
strPath = "c:\temp\1\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "Asses Info"

strFile = Dir(strPath & "*.xlsm")
MsgBox acSpreadsheetTypeExcel9

Do While Len(strFile) > 0
strPathFile = strPath & strFile

DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames



' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop
 
K

-Karl

In 1 command:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Asses Info", "c:\temp\1\GLC_Regional_Database_v.
2.xls", True






or as a Function

Dim strPathFile As String, strFile As String, strPath As
String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL
worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
'strPath = "S:\_oDesk Projects\Project 1\Reporting DB\Reports
7-22-09\GLC\"
strPath = "c:\temp\1\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "Asses Info"

strFile = Dir(strPath & "*.xlsm")
MsgBox acSpreadsheetTypeExcel9

Do While Len(strFile) > 0
strPathFile = strPath & strFile

DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames



' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop
 
K

-Karl

In 1 command:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Asses Info", "c:\temp\1\GLC_Regional_Database_v.
2.xls", True






or as a Function

Dim strPathFile As String, strFile As String, strPath As
String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL
worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
'strPath = "S:\_oDesk Projects\Project 1\Reporting DB\Reports
7-22-09\GLC\"
strPath = "c:\temp\1\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "Asses Info"

strFile = Dir(strPath & "*.xlsm")
MsgBox acSpreadsheetTypeExcel9

Do While Len(strFile) > 0
strPathFile = strPath & strFile

DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames



' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop
 
K

-Karl

I think the issue might partly be with Excel 2007 format, but I am
also wondering about the structure of the workbook. I am using field
names that contain spaces (Row 1).
 
K

-Karl

I think the issue might partly be with Excel 2007 format, but I am
also wondering about the structure of the workbook. I am using field
names that contain spaces (Row 1).
 
K

-Karl

I think the issue might partly be with Excel 2007 format, but I am
also wondering about the structure of the workbook. I am using field
names that contain spaces (Row 1).
 
K

-Karl

Ok, I think I see a problem here now. I have mutiple sheets that i
need to import in the workbook. I was thinking that when i specified
the table name, it was looking for the sheet name in the excel
workbook to match.

If that's the case, would the range parameter work?

http://msdn.microsoft.com/en-us/library/bb214134.aspx

I need to verify the format but something like "Sheet1:A1:G12" ??
 
K

-Karl

Ok, time to cry. It helps when you actually read what is on teh page
instead of jumping to the 1st solution. Seems they had the solution
there the entire time. I just didn't see it


OMG So sorry!!!! The code for this works :)

Import Data from Specific Worksheets in All EXCEL Files in a single
Folder via TransferSpreadsheet (VBA)


Sorry for all the pain!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working outlook via vba code with xlsx - error 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Manipulating Excel through Access 4
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Word macro that can call cells out of Excel for a find and replace 0
Delete Excel worksheets from Access. 5
VBA - exporting serial letters to single documents 0
Opening a recordset with parameters 4

Top