Using Row headings instead of Column Headings with DoCmd.TransferSpreadsheet

[rblivewire]

Is there any way for Access to read the row heading instead of the
column headings when using DoCmd.TransferSpreadsheet. I have an excel
sheet with the information cantained going down the spreadsheet instead
of across and need this information transfered to my database. Is this
possible?

 

[Ken Snell \(MVP\)]

No. You'll need to use VBA programming to either transpose the data and then
import the data; or to read the data and write the data into a recordset
that is created from a table in your database.

 

[rblivewire]

So by transpose, you mean I will have to switch positions from vertical
to horizontal. I have a "form" made in excel where users input data
going vertical (read like a piece of paper). I need to put the
information from some of the cells into Access. I have a button on a
form in Access where if the user presses it, it should automatically
takes the information from the "form" in excel and puts it in the form
in Access.

Ex. (Excel Form)
A B C
1 Name XXX
2 Addr. XXX
3 Phone XXX

(Access Form)

Name XXX
Addr. XXX
Phone XXX

I want to take the XXX above and put it in the 3 text boxes that
correspond to its particular cell on the Access form at the press of
that button. Any coding would be helpful to do this.

 

[Ken Snell \(MVP\)]

Something like this, perhaps:

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Set xlx = CreateObject("Excel.Application")
xlx.Visible = False
Set xlw = xlx.Workbooks.Open("C:\Filename.xls")
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("B1")
Me.TextBox1.Value = xlc.Value
Set xlc = xls.Range("B2")
Me.TextBox2.Value = xlc.Value
Set xlc = xls.Range("B3")
Me.TextBox3.Value = xlc.Value
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing

 

[rblivewire]

Ken,
Thanks alot... this may be the answer.
The only problem I am having is with the line
Me.ID.Value = xlc.Value

It gives me the error "You can't assign a value to this object"
Any way around this?

 

[Ken Snell \(MVP\)]

What is "ID"? a control? a field? does it involve an autonumber field?

 

[rblivewire]

ID will be a autonumber field, but not the one in Access. I have a
code set to generate the next number.

 

[rblivewire]

Thanks, that was it the autonumber....

One more question (maybe...haha)

Is there any way to ask the user which excel spreadsheet he wants to
open. I know when writing queries, you can have a box pop up to ask
what to input (ex. input date). Can this be done using VB also?

 

[Ken Snell \(MVP\)]

See the code at http://www.mvps.org/access/api/api0001.htm for a way to let
the user actually browse to and select the file.

 

[rblivewire]

Ken,
You've been a great help.
Within that code you sent me, I am having trouble with the line
ahtAddFilterItem. Do I need to define it within a function or
something? I keep getting the error that the sub or function is not
defined.

Thanks

 

[Ken Snell \(MVP\)]

Did you copy all the code that is posted in that article? There are many
functions in that code (including the ahtAddFilterItem function), and all
are needed.

 

[rblivewire]

OK ... got that, the box opens ... Almost done

Now I am trying to combine the code that you gave me (that works great)
with the open dialog box ... basically have the user choose the file he
wants then use that info to transfer. I am guessing its not the
difficult, just have to adjust the code a little.

Your help is greatly appreciated.

Thanks

 

[Ken Snell \(MVP\)]

Not sure what you need to do. In that sample code, the code is using
strInputFileName as the variable that holds the full path and file name of
the selected file. Just use that variable in the TransferSpreadsheet code
step as the "source EXCEL file".