excel data into access

[james]

Hi,
I am trying to use the following code below from
(http://www.paulsadowski.com/WSH/cdo.htm#DB) but it doesn't seem to be
working.
Can anyone help? I have created a new workbook and changed the directory in
the code. The data I need is on sheet1 in my workbook. I am not sure the code
below directs it to the correct place though.

Private Function Command2_Click()

Dim x, strTemp, objExcel, objWB

Set objExcel = Wscript.CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open("J:\TCD\Database\testing.xls")
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Make Excel visible while debugging
objExcel.Visible = True

' This is the row of our first cell.
x = 1

Do While objExcel.Cells(x, 1).Value <> ""
strTemp = strTemp & objExcel.Cells(x, 1).Value & _
Space(10 - Len(objExcel.Cells(x, 1).Value))
strTemp = strTemp & objExcel.Cells(x, 2).Value & _
Space(50 - Len(objExcel.Cells(x, 2).Value))
strTemp = strTemp & objExcel.Cells(x, 3).Value & vbCrLf
x = x + 1
Loop

' This will prevent Excel from prompting us to save the workbook.
objExcel.ActiveWorkbook.Saved = True

' Close the workbook and exit the application.
objWB.Close
objExcel.Quit

Set objWB = Nothing
Set objExcel = Nothing

Command2_Click = strTemp
End Function

 

[JonWayn]

While I didnt see a declaration for objSheet I am assuming that you did
declare it in your live script, maybe at module level. Anyway, looking at
your code I cant see why it wouldnt work, provided that sheet "Sheet1"
actually refers to the first worksheet in that workbook

 

[John W. Vinson]

Hi,
I am trying to use the following code below from
(http://www.paulsadowski.com/WSH/cdo.htm#DB) but it doesn't seem to be
working.
Can anyone help? I have created a new workbook and changed the directory in
the code. The data I need is on sheet1 in my workbook. I am not sure the code
below directs it to the correct place though.

Private Function Command2_Click()

Dim x, strTemp, objExcel, objWB

Set objExcel = Wscript.CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open("J:\TCD\Database\testing.xls")
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Make Excel visible while debugging
objExcel.Visible = True

' This is the row of our first cell.
x = 1

Do While objExcel.Cells(x, 1).Value <> ""
strTemp = strTemp & objExcel.Cells(x, 1).Value & _
Space(10 - Len(objExcel.Cells(x, 1).Value))
strTemp = strTemp & objExcel.Cells(x, 2).Value & _
Space(50 - Len(objExcel.Cells(x, 2).Value))
strTemp = strTemp & objExcel.Cells(x, 3).Value & vbCrLf
x = x + 1
Loop

' This will prevent Excel from prompting us to save the workbook.
objExcel.ActiveWorkbook.Saved = True

' Close the workbook and exit the application.
objWB.Close
objExcel.Quit

Set objWB = Nothing
Set objExcel = Nothing

Command2_Click = strTemp
End Function

This code assembles three cells - columns A, B and C of the last row with a
nonblank A column - into a variable named strTemp, and assigns that variable
to the Click event. This does nothing.

If you want to put data from a spreadsheet into a Table, take a look at the
Help file for TransferSpreadsheet.

 

[Ken Snell \(MVP\)]

This code assembles three cells - columns A, B and C of the last row with
a
nonblank A column - into a variable named strTemp, and assigns that
variable
to the Click event. This does nothing.

If you want to put data from a spreadsheet into a Table, take a look at
the
Help file for TransferSpreadsheet.

And here:
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm