Importing Excel spreadsheet

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

Hello!

I am trying to import a particular sheet in a workbook,
not the entire workbook.

The entire workbook is imported when I use the following:

DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, "TempTable",
FilePathName, , "A2:F2000"

The name of the sheet (tab) I want to import
is "upload_data" Where should I designate this? I
thought if I activated the sheet in a line prior to the
above, Access would know to just get data from that
sheet . . . but I guess that would be too easy!

Any help anyone can give me will be greatly appreciated!

Sandy
 
No can do using TransferSpreadsheet. You'll need to do one of the following
things:

1) Link to the spreadsheet as a table.

2) Copy the spreadsheet into its own file and them import it (or link to
it).

3) Use Automation to open the EXCEL file and read the spreadsheet and write
the values into a recordset based on the table that is to receive the data.
 
Hello Ken!

Thanks for your response!

Below is my code. Is that the type of thing you're
talking about re Choice #3? Can I make some of this work,
or do I have to start from scratch?

Sandy


Public Function ImportFromTemplates()

Dim dbs As Database, rst As Recordset, ls_sql As String
Dim xl As Object, Sheet As Object
Dim DBPath As String, FileName As String, ls_area As
String, ls_msg As String, FilePathName As String
Dim CurrentValue As Variant, CurrentField As Variant
Dim li_return As Integer, i As Integer, iCols As
Integer
Dim ls_destination As String

Set dbs = DBEngine.Workspaces(0).Databases(0)

' create temp table for importing
If TableExists("TempTable") Then
DoCmd.DeleteObject acTable, "TempTable"
End If
DoCmd.RunSQL "CREATE TABLE TempTable([F1] text
(100), [F2] text(5)," & _
"[F3] text(4), [F4] text(50), [F5] number, [F6]
date);"

' establish link to Excel
Set xl = CreateObject("Excel.Application")
xl.Application.Visible = True

' get list of all xls files in T_In directory
DBPath = GetDatabasePath()
FileName = Dir(DBPath & "T_In\*.xls")

Do Until FileName = ""

FilePathName = DBPath & "T_In\" & FileName
ls_destination = DBPath & "T_In_Done\" &
FileName

' open workbook and set sheet get area id
FilePathName = DBPath & "T_In\" & FileName
xl.Application.Workbooks.Open FilePathName
xl.Application.Worksheets
("upload_data").Activate
' import data from upload_data sheet to
temp table
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, "TempTable",
FilePathName, , "A2:F2000"

' save, close, and move to _done
xl.Application.Workbooks(FileName).Close
SaveChanges:=False
ls_destination = DBPath & "T_In_Done\" &
FileName
FileCopy FilePathName, ls_destination
Kill FilePathName

FileName = Dir()
Loop

Set Sheet = Nothing
xl.Quit
Set xl = Nothing
End Function
 
I am trying to import a particular sheet in a workbook,
not the entire workbook.
Click to expand...

here is the help file on the IN clause example:

A Microsoft Excel worksheet
SELECT CustomerID, CompanyName
FROM [Customers$]
IN "c:\documents\xldata.xls" "EXCEL 5.0;"
WHERE CustomerID Like "A*"
ORDER BY CustomerID;


A named range in a worksheet
SELECT CustomerID, CompanyName
FROM CustomersRange
IN "c:\documents\xldata.xls" "EXCEL 5.0;"
WHERE CustomerID Like "A*"
ORDER BY CustomerID;

Does that help?


Tim F
 
Sandy -

I've been tied up with work these past few days. I will try to get an answer
to you tonite. Sorry for the delay.
 
Yes, the code that you are using is a good starting point for what you seek
to do. What needs to be added is the use of a recordset variable for the
table into which you want to write the data, and a loop to go through the
worksheet (you'll need some way to know when you've read all the columns for
one row, and all the rows for one worksheet).


With these assumptions (note that I changed Sheet to xlSheet in the Dim
statement):


Public Function ImportFromTemplates()

Dim dbs As Database, rst As Recordset, ls_sql As String
Dim xl As Object, xlSheet As Object
Dim DBPath As String, FileName As String, ls_area As
String, ls_msg As String, FilePathName As String
Dim CurrentValue As Variant, CurrentField As Variant
Dim li_return As Integer, i As Integer, iCols As
Integer
Dim ls_destination As String

Set dbs = DBEngine.Workspaces(0).Databases(0)

' create temp table for importing
If TableExists("TempTable") Then
DoCmd.DeleteObject acTable, "TempTable"
End If
DoCmd.RunSQL "CREATE TABLE TempTable([F1] text
(100), [F2] text(5)," & _
"[F3] text(4), [F4] text(50), [F5] number, [F6]
date);"

' ***NEW CODE:
Set rst = dbs.OpenRecordset("TempTable", dbOpenDynaset, _
dbAppendOnly)
' ***END NEW CODE:

' establish link to Excel
Set xl = CreateObject("Excel.Application")
xl.Application.Visible = True

' get list of all xls files in T_In directory
DBPath = GetDatabasePath()
FileName = Dir(DBPath & "T_In\*.xls")

Do Until FileName = ""

FilePathName = DBPath & "T_In\" & FileName
ls_destination = DBPath & "T_In_Done\" &
FileName

' open workbook and set sheet get area id
FilePathName = DBPath & "T_In\" & FileName
xl.Application.Workbooks.Open FilePathName
xl.Application.Worksheets
("upload_data").Activate
' import data from upload_data sheet to
temp table


' ***NEW CODE:
Set xlSheet = xl.Application.Worksheets("upload_data")
For i = 2 To 2000
rst.AddNew
For iCols = 1 To 6
rst.Fields(iCols-1) = xlSheet.Cells(i,
iCols).Value
Next iCols
rst.Update
Next i
Set xlSheet = Nothing
' ***END NEW CODE:



' save, close, and move to _done
xl.Application.Workbooks(FileName).Close
SaveChanges:=False
ls_destination = DBPath & "T_In_Done\" &
FileName
FileCopy FilePathName, ls_destination
Kill FilePathName

FileName = Dir()
Loop



' ***NEW CODE:
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
' ***END NEW CODE:


xl.Quit
Set xl = Nothing
End Function


Sandy said:
Hello Ken!

Thanks for your response!

Below is my code. Is that the type of thing you're
talking about re Choice #3? Can I make some of this work,
or do I have to start from scratch?

Sandy


Public Function ImportFromTemplates()

Dim dbs As Database, rst As Recordset, ls_sql As String
Dim xl As Object, Sheet As Object
Dim DBPath As String, FileName As String, ls_area As
String, ls_msg As String, FilePathName As String
Dim CurrentValue As Variant, CurrentField As Variant
Dim li_return As Integer, i As Integer, iCols As
Integer
Dim ls_destination As String

Set dbs = DBEngine.Workspaces(0).Databases(0)

' create temp table for importing
If TableExists("TempTable") Then
DoCmd.DeleteObject acTable, "TempTable"
End If
DoCmd.RunSQL "CREATE TABLE TempTable([F1] text
(100), [F2] text(5)," & _
"[F3] text(4), [F4] text(50), [F5] number, [F6]
date);"

' establish link to Excel
Set xl = CreateObject("Excel.Application")
xl.Application.Visible = True

' get list of all xls files in T_In directory
DBPath = GetDatabasePath()
FileName = Dir(DBPath & "T_In\*.xls")

Do Until FileName = ""

FilePathName = DBPath & "T_In\" & FileName
ls_destination = DBPath & "T_In_Done\" &
FileName

' open workbook and set sheet get area id
FilePathName = DBPath & "T_In\" & FileName
xl.Application.Workbooks.Open FilePathName
xl.Application.Worksheets
("upload_data").Activate
' import data from upload_data sheet to
temp table
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, "TempTable",
FilePathName, , "A2:F2000"

' save, close, and move to _done
xl.Application.Workbooks(FileName).Close
SaveChanges:=False
ls_destination = DBPath & "T_In_Done\" &
FileName
FileCopy FilePathName, ls_destination
Kill FilePathName

FileName = Dir()
Loop

Set Sheet = Nothing
xl.Quit
Set xl = Nothing
End Function

-----Original Message-----
No can do using TransferSpreadsheet. You'll need to do one of the following
things:

1) Link to the spreadsheet as a table.

2) Copy the spreadsheet into its own file and them import it (or link to
it).

3) Use Automation to open the EXCEL file and read the spreadsheet and write
the values into a recordset based on the table that is to receive the data.

--
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
Click to expand...
 
Back
Top