EliseFreedman
Programmer
I have a Visual Basic Program which connects to a spreadsheet and outputs the contents to a text file ready for updating some remote databases
The spreadsheet contains a list of products together with the date that they are due to come back into stock. Further down the same spreadsheet, there are details regarding discontinued products etc. However, the only part that I am interested in is the out of stock products.
My problem is that each week, the number of items which are out of stock will vary and therefore the number of rows to be output to the file will vary. At the moment, I am loading the spreadsheet manually, searching through it to find the last row of the out of stocks and changing the reference in my visual basic program to reflect this
I would like to change it so that my visual basic program searches for the first empty row and then determines the range to outout to the file automatically.
How would I do this. For reference, I have attached a sample spreadsheet and my vb program
A sample spreadsheet is as follows
Code Description ETA
BB-AG101 9" HEAVY DUTY ANGLE GRINDER 24-Nov-03
AG300 115 X 3MM STEEL CUTTING DISC 01-Jan-00
AG301 115 X 3MM STONE CUTTING DISC 06-Nov-03
BB-BD338 2'' X 20' RATCHET TIE DOWN 07-Nov-03
DISCONTINUED
BB-BS202 21" BOW SAW WITH HARDPOINT BLADE
BB-CL111 3PC CLAMP SET WITH STAND
BB-CS800 14" CUT OFF SAW
BB-CW102 5MX0.6MX50mm GALVANISED WIRE NETTING
The VB program is currently as follows
Dim upnum As Integer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strcn As String
Dim strsql As String
Dim mousepointer As Integer
Screen.mousepointer = 11
'delete existing file
Kill "c:\order entry\oos.dat"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
strcn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\order entry\oos.xls;" & "Extended Properties=""Excel 8.0;"""
cn.Open strcn
Open "c:\order entry\OOS.DAT" For Random As #3 Len = 60
upnum = 0
With rs
strsql = "Select * from [OofSReport$b1:d135]" ' change each week according to spreadsheet reference
.Open strsql, cn, adOpenDynamic, , adCmdText
If .BOF = False Then
While .EOF = False
upnum = upnum + 1
UPSOS.SRCODE = !Code
UPSOS.SRDESC = !Description
UPSOS.SRDATE = Format(!ETA, "dd/mm/yyyy"
Put 3, upnum, UPSOS
.MoveNext
Wend
End If
.Close
End With
Close #3
Set rs = Nothing
cn.Close
Screen.mousepointer = 1
End
The spreadsheet contains a list of products together with the date that they are due to come back into stock. Further down the same spreadsheet, there are details regarding discontinued products etc. However, the only part that I am interested in is the out of stock products.
My problem is that each week, the number of items which are out of stock will vary and therefore the number of rows to be output to the file will vary. At the moment, I am loading the spreadsheet manually, searching through it to find the last row of the out of stocks and changing the reference in my visual basic program to reflect this
I would like to change it so that my visual basic program searches for the first empty row and then determines the range to outout to the file automatically.
How would I do this. For reference, I have attached a sample spreadsheet and my vb program
A sample spreadsheet is as follows
Code Description ETA
BB-AG101 9" HEAVY DUTY ANGLE GRINDER 24-Nov-03
AG300 115 X 3MM STEEL CUTTING DISC 01-Jan-00
AG301 115 X 3MM STONE CUTTING DISC 06-Nov-03
BB-BD338 2'' X 20' RATCHET TIE DOWN 07-Nov-03
DISCONTINUED
BB-BS202 21" BOW SAW WITH HARDPOINT BLADE
BB-CL111 3PC CLAMP SET WITH STAND
BB-CS800 14" CUT OFF SAW
BB-CW102 5MX0.6MX50mm GALVANISED WIRE NETTING
The VB program is currently as follows
Dim upnum As Integer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strcn As String
Dim strsql As String
Dim mousepointer As Integer
Screen.mousepointer = 11
'delete existing file
Kill "c:\order entry\oos.dat"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
strcn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\order entry\oos.xls;" & "Extended Properties=""Excel 8.0;"""
cn.Open strcn
Open "c:\order entry\OOS.DAT" For Random As #3 Len = 60
upnum = 0
With rs
strsql = "Select * from [OofSReport$b1:d135]" ' change each week according to spreadsheet reference
.Open strsql, cn, adOpenDynamic, , adCmdText
If .BOF = False Then
While .EOF = False
upnum = upnum + 1
UPSOS.SRCODE = !Code
UPSOS.SRDESC = !Description
UPSOS.SRDATE = Format(!ETA, "dd/mm/yyyy"
Put 3, upnum, UPSOS
.MoveNext
Wend
End If
.Close
End With
Close #3
Set rs = Nothing
cn.Close
Screen.mousepointer = 1
End