Solved

Alternate method to transfer spreadsheet than DOCMD.TRANSFERSPREADSHEET

Posted on 2004-08-04
4
1,074 Views
Last Modified: 2010-03-01
I currently have some code to transfer all the contents of an excel spreadsheet:

 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblname", filename, True

I however want to transfer only a specific range within one of the worksheets found in the workbook.

Is there anyway to accomplish this...I ahve also been reading that there is a dao method???  Can anyone give me any insight on how I would accomplish this?

THANKS!
0
Comment
Question by:robbdfw
4 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 11720151
DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][, range]

the above is the templar from MSAccess help.  Note that the last parameter of the method is the range to be imported.  I'm presuming that range would necessarily include the name of the worksheet.

I normally do imports from spreadsheets by linking to the sheet and querying the data into place.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11720311

To import an entire worksheet, refer to the worksheet without a named range, but include an exclamation point (!). For example, the following sample code imports Sheet5 from a workbook named T.xls located on drive C.

   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "TestTable", "C:\T.XLS", True, "Sheet5!"


If you do not specify a value for the last argument, Microsoft Access imports the first worksheet that it finds in the workbook. If you specify a range, that range is imported from the first worksheet in the workbook.
To specify a range from a specific worksheet, use the following syntax, which refers to cells A1 through G50:

Sheet5!A1:G50

0
 
LVL 1

Accepted Solution

by:
trueblue53 earned 250 total points
ID: 11730703
use this code and see what it does, you should be able to figure the rest out.

Public Sub test()
' Declare variables
Dim appExcel As Object
Dim aDb As Database
Dim aRecSet As Recordset
Dim anExcelFile As String
Dim aWorksheet As String
Dim aTable As String
Dim rowCount As Integer
Dim fieldNum As Integer

' Open spreadsheet
    anExcelFile = "C:\Test.xls"
    aWorksheet = "Sheet1"
    Set appExcel = CreateObject("excel.application")
    appExcel.Application.Visible = False 'If you want to see the spreadsheet say True
    appExcel.Workbooks.Open anExcelFile
' Open table/query
    aTable = "myTableQuery"
    Set aDb = CurrentDb
    Set aRecSet = aDb.OpenRecordset(aTable)
' this will list all the field names from "aTable" across row 1 of Test.xls
' and the values in columns underneath
' I cheated the table has only three fields
rowCount = 1
appExcel.worksheets(aWorksheet).Cells(rowCount, 1) = "Record Number"
appExcel.worksheets(aWorksheet).Cells(rowCount, 1).Interior.ColorIndex = 15
For fieldNum = 0 To aRecSet.Fields.Count - 1
'note here that the fields in a recordset are number 0 to Fields.Count - 1
' and I want to put record numbers in column A
    appExcel.worksheets(aWorksheet).Cells(rowCount, fieldNum + 2) = aRecSet.Fields(fieldNum).Name
    appExcel.worksheets(aWorksheet).Cells(rowCount, fieldNum + 1).Interior.ColorIndex = 15
Next
rowCount = rowCount + 1
While Not aRecSet.EOF
For fieldNum = 0 To aRecSet.Fields.Count - 1
'note here that the fields in a recordset are number 0 to Fields.Count - 1
    appExcel.worksheets(aWorksheet).Cells(rowCount, 1) = rowCount
    appExcel.worksheets(aWorksheet).Cells(rowCount, fieldNum + 2) = aRecSet.Fields(fieldNum).Value
Next
rowCount = rowCount + 1
aRecSet.MoveNext
Wend
appExcel.activeworkbook.saveas "C:\Test3.xls"
appExcel.Quit
Set appExcel = Nothing
Set aRecSet = Nothing
Set aDb = Nothing
End Sub
0
 

Author Comment

by:robbdfw
ID: 11757152
Trueblue: Thanks for the great code idea.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now