Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Alternate method to transfer spreadsheet than DOCMD.TRANSFERSPREADSHEET

Posted on 2004-08-04
4
Medium Priority
?
1,097 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 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

782 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