Solved

Alternate method to transfer spreadsheet than DOCMD.TRANSFERSPREADSHEET

Posted on 2004-08-04
4
1,085 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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

628 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