Importing SQL Server Express 2008 Data into specific Cells in Excel

[deyken]

Howdy,

I seem to be getting some annoying errors with the following block of code, fired from a command button sitting on an Excel worksheet:

--------------------------------------------------------------------

Sub Import_Packing_List()
' This will connect to the database, collect a single Packing List by Number
' and import THAT P/L's header data from the tblPACKINGLISTm table and display
' the relevant information in the required Cells.
' The user will then be asked to save this template as a file on the Server separately

' First Declare connection variables and connect to the database:
Dim BSS_PL As ADODB.Connection
Dim PL_Cmd As ADODB.Command
Dim PL_RecordSet As ADODB.Recordset
Dim PackingList As String


If Cells(2, "H") = "" Then
MsgBox "You have to enter a Packing List Number before you can continue!", vbCritical
Else
If MsgBox("Import Packing List Number " & Cells(2, "H"), [vbOKCancel], "Packing List Import confirmation", 0, 0) = vbOK Then
' Begin the import process

PackingList = "'" & Cells(2, "H") & "'"

' Set and open Database connection
Set BSS_PL = New ADODB.Connection
BSS_PL.CursorLocation = adUseClient
BSS_PL.Open "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SHIPPING002\SQLEXPRESS;Database=BrolazShipping"

' Now create a Command Query to collect the Required Packing List...
Set PL_Cmd = New ADODB.Command
With PL_Cmd
Set .ActiveConnection = BSS_PL
.CommandType = adCmdText
.CommandText = "SELECT * FROM tblPACKING_LISTS_m WHERE (tblPACKING_LISTS_m.PACKINGLIST_NO = PackingList)"
End With

' Now open this query into our RecordSet
Set PL_RecordSet = New ADODB.Recordset
'PL_RecordSet.Open PL_Cmd, , adOpenDynamic, adLockReadOnly

PL_RecordSet.Open "SELECT * FROM tblPACKING_LISTS_m WHERE (tblPACKING_LISTS_m.PACKINGLIST_NO = PackingList)", BSS_PL, adOpenDynamic, adLockReadOnly


' Now place the RecordSet data into the relevant Cells on the worksheet
With Sheets("Sheet1")
Cells(8, "F") = PL_RecordSet!Field0 ' Packing List #
Cells(9, "F") = PL_RecordSet!Field13 ' Order No
Cells(10, "F") = PL_RecordSet!Field2 ' Project Code
Cells(11, "F") = PL_RecordSet!Field1 ' Packing List Date
Cells(12, "F") = PL_RecordSet!Field14 ' UCR No
Cells(13, "F") = PL_RecordSet!Field17 ' Transaction Value
Cells(14, "F") = PL_RecordSet!Field18 ' Advance Payment
Cells(15, "F") = PL_RecordSet!Field19 ' Credit Terms
Cells(18, "F") = PL_RecordSet!Field20 ' IDF/PIP Number
Cells(16, "F") = PL_RecordSet!Field21 ' Country of Origin
Cells(17, "F") = PL_RecordSet!Field22 ' Port of Loading (City Origin)
Cells(33, "B") = PL_RecordSet!Field2 ' Project Description
Cells(44, "B") = PL_RecordSet!Field3 ' Total Pieces [footer]
Cells(45, "B") = PL_RecordSet!Field3 ' Total Volume Weight [footer]
Cells(46, "B") = PL_RecordSet!Field3 ' Total Weight [footer]

Cells(9, "B") = PL_RecordSet!Field10 ' Importer/Client Number
Cells(16, "B") = PL_RecordSet!Field11 ' Consignee Number
Cells(23, "B") = PL_RecordSet!Field12 ' Notify Number
End With


' *************************************************************************** *
' END OF PACKING LIST MASTER HEADER IMPORT. FOR NOW ITEMS (DETAIL) WILL BE
' MANUALLY INPUT INTO PACKING LIST TEMPLATE. HERE WE MUST REMIND THE USER TO
' SAVE THIS PACKING LIST MANUALLY ONTO HIS/HER COMPUTER AND/OR THE SERVER
' *************************************************************************** *

MsgBox "Remember to save this Packing List manually!", vbExclamation


End If
End If


End Sub
--------------------------------------------------------------------

I get the following errors:

1) Invalid Column Name for my variable "PackingList", which will contain the Packing List Record Number (PK) as a Text/String entered by the user into a worksheet cell located at Cells(2, "H").

2) On my With block, when trying to reference Cells to which certain Fields from my Recordset must be sent/copied it gives me 'Subscript is out of range'

Am I doing something wrong?

Any help would be greatly appreciated!

Thanks a lot!

EDIT: Added VBA code tags. Tommy

[Tommy]

Hi deyken, :hi:

The biggest problem I see was the cells(2,"H") should be cells(2,8) so for the sake of let's see try this:

Sub Import_Packing_List()
' This will connect to the database, collect a single Packing List by Number
' and import THAT P/L's header data from the tblPACKINGLISTm table and display
' the relevant information in the required Cells.
' The user will then be asked to save this template as a file on the Server separately
' First Declare connection variables and connect to the database:
Dim BSS_PL As ADODB.Connection
Dim PL_Cmd As ADODB.Command
Dim PL_RecordSet As ADODB.Recordset
Dim PackingList As String
If Cells(2, 8) = "" Then
MsgBox "You have to enter a Packing List Number before you can continue!", vbCritical
Else
If MsgBox("Import Packing List Number " & Cells(2, 8), [vbOKCancel], "Packing List Import confirmation", 0, 0) = vbOK Then
' Begin the import process
PackingList = "'" & Cells(2, 8) & "'"
' Set and open Database connection
Set BSS_PL = New ADODB.Connection
BSS_PL.CursorLocation = adUseClient
BSS_PL.Open "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SHIPPING002\SQLEXPRESS;Database=BrolazShipping"
' Now create a Command Query to collect the Required Packing List...
Set PL_Cmd = New ADODB.Command
With PL_Cmd
Set .ActiveConnection = BSS_PL
.CommandType = adCmdText
.CommandText = "SELECT * FROM tblPACKING_LISTS_m WHERE tblPACKING_LISTS_m.PACKINGLIST_NO = PackingList"
End With
' Now open this query into our RecordSet
Set PL_RecordSet = New ADODB.Recordset
'PL_RecordSet.Open PL_Cmd, , adOpenDynamic, adLockReadOnly
PL_RecordSet.Open "SELECT * FROM tblPACKING_LISTS_m WHERE tblPACKING_LISTS_m.PACKINGLIST_NO = PackingList", BSS_PL, adOpenDynamic, adLockReadOnly
' Now place the RecordSet data into the relevant Cells on the worksheet
With Sheets("Sheet1")
.Cells(8, 6) = PL_RecordSet!Field0 ' Packing List #
.Cells(9, 6) = PL_RecordSet!Field13 ' Order No
.Cells(10, 6) = PL_RecordSet!Field2 ' Project Code
.Cells(11, 6) = PL_RecordSet!Field1 ' Packing List Date
.Cells(12, 6) = PL_RecordSet!Field14 ' UCR No
.Cells(13, 6) = PL_RecordSet!Field17 ' Transaction Value
.Cells(14, 6) = PL_RecordSet!Field18 ' Advance Payment
.Cells(15, 6) = PL_RecordSet!Field19 ' Credit Terms
.Cells(18, 6) = PL_RecordSet!Field20 ' IDF/PIP Number
.Cells(16, 6) = PL_RecordSet!Field21 ' Country of Origin
.Cells(17, 6) = PL_RecordSet!Field22 ' Port of Loading (City Origin)
.Cells(33, 2) = PL_RecordSet!Field2 ' Project Description
.Cells(44, 2) = PL_RecordSet!Field3 ' Total Pieces [footer]
.Cells(45, 2) = PL_RecordSet!Field3 ' Total Volume Weight [footer]
.Cells(46, 2) = PL_RecordSet!Field3 ' Total Weight [footer]
.Cells(9, 2) = PL_RecordSet!Field10 ' Importer/Client Number
.Cells(16, 2) = PL_RecordSet!Field11 ' Consignee Number
.Cells(23, 2) = PL_RecordSet!Field12 ' Notify Number
End With
' *************************************************************************** *
' END OF PACKING LIST MASTER HEADER IMPORT. FOR NOW ITEMS (DETAIL) WILL BE
' MANUALLY INPUT INTO PACKING LIST TEMPLATE. HERE WE MUST REMIND THE USER TO
' SAVE THIS PACKING LIST MANUALLY ONTO HIS/HER COMPUTER AND/OR THE SERVER
' *************************************************************************** *
MsgBox "Remember to save this Packing List manually!", vbExclamation
End If
End If
End Sub

[deyken]

Hi Tommy,

Thank you very much for your help! I replaced my code with yours but it still delivers error: 'Subscript out of range', referring to line:
With Sheets("Sheet1") ... End With

It seems that it is either looking for a valid range of Cells within Sheet1, or it does not recognise Sheet1 somehow...

Any suggestions?

[deyken]

Hi Tommy,

A moment ago I tested for records on the RecordSet and it checks out. It certainly fetches the correct data from the correct table etc., but I am only having a problem with displaying my RecordSet's Fields in the particular Cell on the Worksheet...

[deyken]

Hi Tommy,

OK I found a solution:

Sheet1.Cells(8, "F") = PL_RecordSet.Fields(0).Value

Thanks for your help!