Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Alternate method to transfer spreadsheet than DOCMD.TRANSFERSPREADSHEET

Posted on 2004-08-04
4
Medium Priority
?
1,090 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 1000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

722 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