excel data into access

  • Thread starter Thread starter james
  • Start date Start date
J

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
 
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
 
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
Click to expand...

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.
 
Back
Top