Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1103
  • Last Modified:

Alternate method to transfer spreadsheet than DOCMD.TRANSFERSPREADSHEET

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
robbdfw
Asked:
robbdfw
1 Solution
 
jadedataMS Access Systems CreatorCommented:
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
 
Rey Obrero (Capricorn1)Commented:

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
 
trueblue53Commented:
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
 
robbdfwAuthor Commented:
Trueblue: Thanks for the great code idea.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now