Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
I found this code that will copy the data from excel and export it to a
database that i created. Unfor, it only takes goes rows. So when i hit the upload button, it only takes the firs row. I am wondering if anyone can help me out to make it loop until last record I tried the loop command but it was coming back as error. Please help. thanks code: Dim MyCn As ADODB.Connection Dim SQLStr As String Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb" ' Replace actual Access file path here ' Note: If database has userID and password, need to specify them here also ' by appending "; UID=MyUserID; PWD=MyPassword" ' - if trouble accessing the file do a net search for help on Connection Strings SQLStr = "INSERT INTO [test] " _ & "Values ('" & Range("A12").Value & "', '" & Range("B12").Value & "', '" _ & Range("C12").Value & "', '" & Range("D12").Value & "', '" & Range("E12").Value & "', '" _ & Range("F12").Value & "', '" & Range("G12").Value & "', '" & Range("H12").Value & "', '" _ & Range("I12").Value & "', '" & Range("J12").Value & "', '" & Range("K12").Value & "', '" _ & Range("L12").Value & "', '" & Range("M12").Value & "', '" & Range("N12").Value & "')" ' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s; ' might have trouble with other data types unless you match the format expected ' by the database MyCn.Execute SQLStr MsgBox "Data has been uploaded to CDI ERROR DATA" MyCn.Close Set MyCn = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
Untested but try:
Sub loadData() Dim MyCn As ADODB.Connection Dim SQLStr As String Dim i As Long Dim r As Long Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb" i = Cells(Rows.Count, 1).End(xlUp).Row For r = 12 To i '<<change start row SQLStr = "INSERT INTO [test] " _ & "Values ('" & Range("A" & r).Value & "', '" _ & Range("B" & r).Value & "', '" _ & Range("C" & r).Value & "', '" & Range("D" & r).Value _ & "', '" & Range("E" & r).Value & "', '" _ & Range("F" & r).Value & "', '" & Range("G" & r).Value _ & "', '" & Range("H" & r).Value & "', '" _ & Range("I" & r).Value & "', '" & Range("J" & r).Value _ & "', '" & Range("K" & r).Value & "', '" _ & Range("L" & r).Value & "', '" & Range("M" & r).Value _ & "', '" & Range("N" & r).Value & "')" MyCn.Execute SQLStr Next r MsgBox "Data has been uploaded to CDI ERROR DATA" MyCn.Close Set MyCn = Nothing End Sub Hope this helps Rowan Justin wrote: I found this code that will copy the data from excel and export it to a database that i created. Unfor, it only takes goes rows. So when i hit the upload button, it only takes the firs row. I am wondering if anyone can help me out to make it loop until last record I tried the loop command but it was coming back as error. Please help. thanks code: Dim MyCn As ADODB.Connection Dim SQLStr As String Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb" ' Replace actual Access file path here ' Note: If database has userID and password, need to specify them here also ' by appending "; UID=MyUserID; PWD=MyPassword" ' - if trouble accessing the file do a net search for help on Connection Strings SQLStr = "INSERT INTO [test] " _ & "Values ('" & Range("A12").Value & "', '" & Range("B12").Value & "', '" _ & Range("C12").Value & "', '" & Range("D12").Value & "', '" & Range("E12").Value & "', '" _ & Range("F12").Value & "', '" & Range("G12").Value & "', '" & Range("H12").Value & "', '" _ & Range("I12").Value & "', '" & Range("J12").Value & "', '" & Range("K12").Value & "', '" _ & Range("L12").Value & "', '" & Range("M12").Value & "', '" & Range("N12").Value & "')" ' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s; ' might have trouble with other data types unless you match the format expected ' by the database MyCn.Execute SQLStr MsgBox "Data has been uploaded to CDI ERROR DATA" MyCn.Close Set MyCn = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
yes it works, thanks
"Rowan Drummond" wrote: Untested but try: Sub loadData() Dim MyCn As ADODB.Connection Dim SQLStr As String Dim i As Long Dim r As Long Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb" i = Cells(Rows.Count, 1).End(xlUp).Row For r = 12 To i '<<change start row SQLStr = "INSERT INTO [test] " _ & "Values ('" & Range("A" & r).Value & "', '" _ & Range("B" & r).Value & "', '" _ & Range("C" & r).Value & "', '" & Range("D" & r).Value _ & "', '" & Range("E" & r).Value & "', '" _ & Range("F" & r).Value & "', '" & Range("G" & r).Value _ & "', '" & Range("H" & r).Value & "', '" _ & Range("I" & r).Value & "', '" & Range("J" & r).Value _ & "', '" & Range("K" & r).Value & "', '" _ & Range("L" & r).Value & "', '" & Range("M" & r).Value _ & "', '" & Range("N" & r).Value & "')" MyCn.Execute SQLStr Next r MsgBox "Data has been uploaded to CDI ERROR DATA" MyCn.Close Set MyCn = Nothing End Sub Hope this helps Rowan Justin wrote: I found this code that will copy the data from excel and export it to a database that i created. Unfor, it only takes goes rows. So when i hit the upload button, it only takes the firs row. I am wondering if anyone can help me out to make it loop until last record I tried the loop command but it was coming back as error. Please help. thanks code: Dim MyCn As ADODB.Connection Dim SQLStr As String Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb" ' Replace actual Access file path here ' Note: If database has userID and password, need to specify them here also ' by appending "; UID=MyUserID; PWD=MyPassword" ' - if trouble accessing the file do a net search for help on Connection Strings SQLStr = "INSERT INTO [test] " _ & "Values ('" & Range("A12").Value & "', '" & Range("B12").Value & "', '" _ & Range("C12").Value & "', '" & Range("D12").Value & "', '" & Range("E12").Value & "', '" _ & Range("F12").Value & "', '" & Range("G12").Value & "', '" & Range("H12").Value & "', '" _ & Range("I12").Value & "', '" & Range("J12").Value & "', '" & Range("K12").Value & "', '" _ & Range("L12").Value & "', '" & Range("M12").Value & "', '" & Range("N12").Value & "')" ' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s; ' might have trouble with other data types unless you match the format expected ' by the database MyCn.Execute SQLStr MsgBox "Data has been uploaded to CDI ERROR DATA" MyCn.Close Set MyCn = Nothing End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
You're welcome.
Justin wrote: yes it works, thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
I have another question, it has to do with the exporting to excel. Now the
code that you modify actually acts like a module for another excel sheet (template sheet) where I import data from access. Now on this template sheet, there is an upload button, which will call for the code (the one you helped out with) and export all teh data and soo on Now on this template sheet, there is a drop down where the user will pick 5 things I need to have it that once the user press upload, that comment field will lock so that they can not change it. Reason is that this 1 sheet is on a share drive and 3 other people will work it. Now i wish that they can work it in a day but sometimes 1 person will work 1 day and anotehr another and the last a week later. The director wants to upload it even if all the work isn't complete, upload whatever is done, and when the others are completed, upload that data. I already know that it will upload 2wice, that is where the access database will determine if this is a duplicate or not, if it is, then delete, if not, save. I don't want to 1 user to change that comment field a day later when i upload it again. That is why i need to know a way to look a field where it has a comment(there is 5 comments) and lock it once the user press upload. thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
Hi Justin
I would need to know more about the comment field in order to help you. It is not clear if this is a cell on the template sheet (perhaps linked to the dropdown) or maybe the dropdown itself or maybe something completely different. So: 1) Is the commment field an excel cell and if so what is it's address or is it more than one cell we are talking about. 2) If it is not a field what is it eg Forms ComboBox, Controls Toolbox listbox, cell comment etc 3) How is the field updated by the user before the load to access occurs. 4) Is the template sheet protected at all. Regards Rowan Justin wrote: I have another question, it has to do with the exporting to excel. Now the code that you modify actually acts like a module for another excel sheet (template sheet) where I import data from access. Now on this template sheet, there is an upload button, which will call for the code (the one you helped out with) and export all teh data and soo on Now on this template sheet, there is a drop down where the user will pick 5 things I need to have it that once the user press upload, that comment field will lock so that they can not change it. Reason is that this 1 sheet is on a share drive and 3 other people will work it. Now i wish that they can work it in a day but sometimes 1 person will work 1 day and anotehr another and the last a week later. The director wants to upload it even if all the work isn't complete, upload whatever is done, and when the others are completed, upload that data. I already know that it will upload 2wice, that is where the access database will determine if this is a duplicate or not, if it is, then delete, if not, save. I don't want to 1 user to change that comment field a day later when i upload it again. That is why i need to know a way to look a field where it has a comment(there is 5 comments) and lock it once the user press upload. thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
the options for the drop down are
1.Barcode Identity Issue 2.Pending Research 3.Logistics Asset Validated 4.No Trouble found 5.Missed Issues "Rowan Drummond" wrote: Hi Justin I would need to know more about the comment field in order to help you. It is not clear if this is a cell on the template sheet (perhaps linked to the dropdown) or maybe the dropdown itself or maybe something completely different. So: 1) Is the commment field an excel cell and if so what is it's address or is it more than one cell we are talking about. 2) If it is not a field what is it eg Forms ComboBox, Controls Toolbox listbox, cell comment etc 3) How is the field updated by the user before the load to access occurs. 4) Is the template sheet protected at all. Regards Rowan Justin wrote: I have another question, it has to do with the exporting to excel. Now the code that you modify actually acts like a module for another excel sheet (template sheet) where I import data from access. Now on this template sheet, there is an upload button, which will call for the code (the one you helped out with) and export all teh data and soo on Now on this template sheet, there is a drop down where the user will pick 5 things I need to have it that once the user press upload, that comment field will lock so that they can not change it. Reason is that this 1 sheet is on a share drive and 3 other people will work it. Now i wish that they can work it in a day but sometimes 1 person will work 1 day and anotehr another and the last a week later. The director wants to upload it even if all the work isn't complete, upload whatever is done, and when the others are completed, upload that data. I already know that it will upload 2wice, that is where the access database will determine if this is a duplicate or not, if it is, then delete, if not, save. I don't want to 1 user to change that comment field a day later when i upload it again. That is why i need to know a way to look a field where it has a comment(there is 5 comments) and lock it once the user press upload. thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
ok, the comment field is an excel cell, 5 cells to be exact(a5:a9)
the sheet has 3 extra cells after teh data is loaded to the sheet operator ID- writing cell comment field-drop down cell operator comment-writing cell as for the sheet, it isn't protected "Rowan Drummond" wrote: Hi Justin I would need to know more about the comment field in order to help you. It is not clear if this is a cell on the template sheet (perhaps linked to the dropdown) or maybe the dropdown itself or maybe something completely different. So: 1) Is the commment field an excel cell and if so what is it's address or is it more than one cell we are talking about. 2) If it is not a field what is it eg Forms ComboBox, Controls Toolbox listbox, cell comment etc 3) How is the field updated by the user before the load to access occurs. 4) Is the template sheet protected at all. Regards Rowan Justin wrote: I have another question, it has to do with the exporting to excel. Now the code that you modify actually acts like a module for another excel sheet (template sheet) where I import data from access. Now on this template sheet, there is an upload button, which will call for the code (the one you helped out with) and export all teh data and soo on Now on this template sheet, there is a drop down where the user will pick 5 things I need to have it that once the user press upload, that comment field will lock so that they can not change it. Reason is that this 1 sheet is on a share drive and 3 other people will work it. Now i wish that they can work it in a day but sometimes 1 person will work 1 day and anotehr another and the last a week later. The director wants to upload it even if all the work isn't complete, upload whatever is done, and when the others are completed, upload that data. I already know that it will upload 2wice, that is where the access database will determine if this is a duplicate or not, if it is, then delete, if not, save. I don't want to 1 user to change that comment field a day later when i upload it again. That is why i need to know a way to look a field where it has a comment(there is 5 comments) and lock it once the user press upload. thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
The easiest way to lock these cells would be to use sheet protection. At
the end of your load macro add the code: Cells.Locked = False Range("A5:A9").Locked = True ActiveSheet.Protect Password:="thepassword" This will stop cells in the range A5:A9 being changed but still allow changes to other cells in the sheet. Hope this helps Rowan Justin wrote: ok, the comment field is an excel cell, 5 cells to be exact(a5:a9) the sheet has 3 extra cells after teh data is loaded to the sheet operator ID- writing cell comment field-drop down cell operator comment-writing cell as for the sheet, it isn't protected "Rowan Drummond" wrote: Hi Justin I would need to know more about the comment field in order to help you. It is not clear if this is a cell on the template sheet (perhaps linked to the dropdown) or maybe the dropdown itself or maybe something completely different. So: 1) Is the commment field an excel cell and if so what is it's address or is it more than one cell we are talking about. 2) If it is not a field what is it eg Forms ComboBox, Controls Toolbox listbox, cell comment etc 3) How is the field updated by the user before the load to access occurs. 4) Is the template sheet protected at all. Regards Rowan Justin wrote: I have another question, it has to do with the exporting to excel. Now the code that you modify actually acts like a module for another excel sheet (template sheet) where I import data from access. Now on this template sheet, there is an upload button, which will call for the code (the one you helped out with) and export all teh data and soo on Now on this template sheet, there is a drop down where the user will pick 5 things I need to have it that once the user press upload, that comment field will lock so that they can not change it. Reason is that this 1 sheet is on a share drive and 3 other people will work it. Now i wish that they can work it in a day but sometimes 1 person will work 1 day and anotehr another and the last a week later. The director wants to upload it even if all the work isn't complete, upload whatever is done, and when the others are completed, upload that data. I already know that it will upload 2wice, that is where the access database will determine if this is a duplicate or not, if it is, then delete, if not, save. I don't want to 1 user to change that comment field a day later when i upload it again. That is why i need to know a way to look a field where it has a comment(there is 5 comments) and lock it once the user press upload. thanks |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
im looking for the data it self to get locked. that column is always M
but it hsa to lock once te upload button was pressed but whatever is still blank, user should be able to use the drop down "Rowan Drummond" wrote: The easiest way to lock these cells would be to use sheet protection. At the end of your load macro add the code: Cells.Locked = False Range("A5:A9").Locked = True ActiveSheet.Protect Password:="thepassword" This will stop cells in the range A5:A9 being changed but still allow changes to other cells in the sheet. Hope this helps Rowan Justin wrote: ok, the comment field is an excel cell, 5 cells to be exact(a5:a9) the sheet has 3 extra cells after teh data is loaded to the sheet operator ID- writing cell comment field-drop down cell operator comment-writing cell as for the sheet, it isn't protected "Rowan Drummond" wrote: Hi Justin I would need to know more about the comment field in order to help you. It is not clear if this is a cell on the template sheet (perhaps linked to the dropdown) or maybe the dropdown itself or maybe something completely different. So: 1) Is the commment field an excel cell and if so what is it's address or is it more than one cell we are talking about. 2) If it is not a field what is it eg Forms ComboBox, Controls Toolbox listbox, cell comment etc 3) How is the field updated by the user before the load to access occurs. 4) Is the template sheet protected at all. Regards Rowan Justin wrote: I have another question, it has to do with the exporting to excel. Now the code that you modify actually acts like a module for another excel sheet (template sheet) where I import data from access. Now on this template sheet, there is an upload button, which will call for the code (the one you helped out with) and export all teh data and soo on Now on this template sheet, there is a drop down where the user will pick 5 things I need to have it that once the user press upload, that comment field will lock so that they can not change it. Reason is that this 1 sheet is on a share drive and 3 other people will work it. Now i wish that they can work it in a day but sometimes 1 person will work 1 day and anotehr another and the last a week later. The director wants to upload it even if all the work isn't complete, upload whatever is done, and when the others are completed, upload that data. I already know that it will upload 2wice, that is where the access database will determine if this is a duplicate or not, if it is, then delete, if not, save. I don't want to 1 user to change that comment field a day later when i upload it again. That is why i need to know a way to look a field where it has a comment(there is 5 comments) and lock it once the user press upload. thanks |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
Try
Dim rngToLock As Range Set rngToLock = Range("M2:M100") '<<adjust as required Cells.Locked = False rngToLock.Locked = True On Error Resume Next rngToLock.SpecialCells(xlCellTypeBlanks).Locked = False On Error GoTo 0 ActiveSheet.Protect Password:="thepassword" Regards Rowan Justin wrote: im looking for the data it self to get locked. that column is always M but it hsa to lock once te upload button was pressed but whatever is still blank, user should be able to use the drop down "Rowan Drummond" wrote: The easiest way to lock these cells would be to use sheet protection. At the end of your load macro add the code: Cells.Locked = False Range("A5:A9").Locked = True ActiveSheet.Protect Password:="thepassword" This will stop cells in the range A5:A9 being changed but still allow changes to other cells in the sheet. Hope this helps Rowan Justin wrote: ok, the comment field is an excel cell, 5 cells to be exact(a5:a9) the sheet has 3 extra cells after teh data is loaded to the sheet operator ID- writing cell comment field-drop down cell operator comment-writing cell as for the sheet, it isn't protected "Rowan Drummond" wrote: Hi Justin I would need to know more about the comment field in order to help you. It is not clear if this is a cell on the template sheet (perhaps linked to the dropdown) or maybe the dropdown itself or maybe something completely different. So: 1) Is the commment field an excel cell and if so what is it's address or is it more than one cell we are talking about. 2) If it is not a field what is it eg Forms ComboBox, Controls Toolbox listbox, cell comment etc 3) How is the field updated by the user before the load to access occurs. 4) Is the template sheet protected at all. Regards Rowan Justin wrote: I have another question, it has to do with the exporting to excel. Now the code that you modify actually acts like a module for another excel sheet (template sheet) where I import data from access. Now on this template sheet, there is an upload button, which will call for the code (the one you helped out with) and export all teh data and soo on Now on this template sheet, there is a drop down where the user will pick 5 things I need to have it that once the user press upload, that comment field will lock so that they can not change it. Reason is that this 1 sheet is on a share drive and 3 other people will work it. Now i wish that they can work it in a day but sometimes 1 person will work 1 day and anotehr another and the last a week later. The director wants to upload it even if all the work isn't complete, upload whatever is done, and when the others are completed, upload that data. I already know that it will upload 2wice, that is where the access database will determine if this is a duplicate or not, if it is, then delete, if not, save. I don't want to 1 user to change that comment field a day later when i upload it again. That is why i need to know a way to look a field where it has a comment(there is 5 comments) and lock it once the user press upload. thanks |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
Once again, it works perfectly
Last question. I am uploading column a-n to a table in access. now the table has the same headers but I need to have an add'l column (to be when the file was uploaded). Now i tried to adjust it on the xcel sheet to add column 'O' but it comes out as text. I tried to make that column in access a Date/Time attribute but it will only accept text. What can i do so that I can make it a date? Also, is there a way that when you hit the upload button, it ONLY takes rows that have a comment and leave the rest to be worked later on. and maybe delete all records that was just upload so that it doesn't make a duplicate. "Rowan Drummond" wrote: Try Dim rngToLock As Range Set rngToLock = Range("M2:M100") '<<adjust as required Cells.Locked = False rngToLock.Locked = True On Error Resume Next rngToLock.SpecialCells(xlCellTypeBlanks).Locked = False On Error GoTo 0 ActiveSheet.Protect Password:="thepassword" Regards Rowan Justin wrote: im looking for the data it self to get locked. that column is always M but it hsa to lock once te upload button was pressed but whatever is still blank, user should be able to use the drop down "Rowan Drummond" wrote: The easiest way to lock these cells would be to use sheet protection. At the end of your load macro add the code: Cells.Locked = False Range("A5:A9").Locked = True ActiveSheet.Protect Password:="thepassword" This will stop cells in the range A5:A9 being changed but still allow changes to other cells in the sheet. Hope this helps Rowan Justin wrote: ok, the comment field is an excel cell, 5 cells to be exact(a5:a9) the sheet has 3 extra cells after teh data is loaded to the sheet operator ID- writing cell comment field-drop down cell operator comment-writing cell as for the sheet, it isn't protected "Rowan Drummond" wrote: Hi Justin I would need to know more about the comment field in order to help you. It is not clear if this is a cell on the template sheet (perhaps linked to the dropdown) or maybe the dropdown itself or maybe something completely different. So: 1) Is the commment field an excel cell and if so what is it's address or is it more than one cell we are talking about. 2) If it is not a field what is it eg Forms ComboBox, Controls Toolbox listbox, cell comment etc 3) How is the field updated by the user before the load to access occurs. 4) Is the template sheet protected at all. Regards Rowan Justin wrote: I have another question, it has to do with the exporting to excel. Now the code that you modify actually acts like a module for another excel sheet (template sheet) where I import data from access. Now on this template sheet, there is an upload button, which will call for the code (the one you helped out with) and export all teh data and soo on Now on this template sheet, there is a drop down where the user will pick 5 things I need to have it that once the user press upload, that comment field will lock so that they can not change it. Reason is that this 1 sheet is on a share drive and 3 other people will work it. Now i wish that they can work it in a day but sometimes 1 person will work 1 day and anotehr another and the last a week later. The director wants to upload it even if all the work isn't complete, upload whatever is done, and when the others are completed, upload that data. I already know that it will upload 2wice, that is where the access database will determine if this is a duplicate or not, if it is, then delete, if not, save. I don't want to 1 user to change that comment field a day later when i upload it again. That is why i need to know a way to look a field where it has a comment(there is 5 comments) and lock it once the user press upload. thanks |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
Hi Justin
That looks like three questions to me <bg 1) If I understand you correctly you are wanting to have the date (and maybe time) that the data was loaded recorded in the database. If this is the case then you do not need to add any columns to your excel sheet. Add the extra column to your access table, give it a data type of Date/Time and use the Now() or Date functions in your upload query. Now() will give you date and time, Date will give you just the date. I have used Now() in the example below. If you want just the date replace Now() with Date. 2.) Assuming your comment is in Column M (which I think it is from earlier posts) then the example below will only load rows where the value in M is not empty. 3.) Finally this example deletes all the rows which have just been loaded into the database. Sub loadData() Dim MyCn As ADODB.Connection Dim SQLStr As String Dim i As Long Dim r As Long Dim delRows As Range Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb" i = Cells(Rows.Count, 1).End(xlUp).Row For r = 12 To i '<<change start row If Range("M" & r).Value < Empty Then SQLStr = "INSERT INTO [test] " _ & "Values ('" & Range("A" & r).Value & "', '" _ & Range("B" & r).Value & "', '" _ & Range("C" & r).Value & "', '" & Range("D" & r).Value _ & "', '" & Range("E" & r).Value & "', '" _ & Range("F" & r).Value & "', '" & Range("G" & r).Value _ & "', '" & Range("H" & r).Value & "', '" _ & Range("I" & r).Value & "', '" & Range("J" & r).Value _ & "', '" & Range("K" & r).Value & "', '" _ & Range("L" & r).Value & "', '" & Range("M" & r).Value _ & "', '" & Range("N" & r).Value & "', '" & Now() & "')" MyCn.Execute SQLStr If delRows Is Nothing Then Set delRows = Range("A" & r) Else Set delRows = Union(delRows, Range("A" & r)) End If End If Next r delRows.EntireRow.Delete MsgBox "Data has been uploaded to CDI ERROR DATA" MyCn.Close Set MyCn = Nothing End Sub Hope this helps Rowan Justin wrote: Once again, it works perfectly Last question. I am uploading column a-n to a table in access. now the table has the same headers but I need to have an add'l column (to be when the file was uploaded). Now i tried to adjust it on the xcel sheet to add column 'O' but it comes out as text. I tried to make that column in access a Date/Time attribute but it will only accept text. What can i do so that I can make it a date? Also, is there a way that when you hit the upload button, it ONLY takes rows that have a comment and leave the rest to be worked later on. and maybe delete all records that was just upload so that it doesn't make a duplicate. "Rowan Drummond" wrote: |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
1) The problem or error message that i am encountering is this
[Microsoft][ODBC Microsoft Access Driver] Number of Query values and destination fields are not the same. This is when i added another column to the access table "Rowan Drummond" wrote: Hi Justin That looks like three questions to me <bg 1) If I understand you correctly you are wanting to have the date (and maybe time) that the data was loaded recorded in the database. If this is the case then you do not need to add any columns to your excel sheet. Add the extra column to your access table, give it a data type of Date/Time and use the Now() or Date functions in your upload query. Now() will give you date and time, Date will give you just the date. I have used Now() in the example below. If you want just the date replace Now() with Date. 2.) Assuming your comment is in Column M (which I think it is from earlier posts) then the example below will only load rows where the value in M is not empty. 3.) Finally this example deletes all the rows which have just been loaded into the database. Sub loadData() Dim MyCn As ADODB.Connection Dim SQLStr As String Dim i As Long Dim r As Long Dim delRows As Range Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb" i = Cells(Rows.Count, 1).End(xlUp).Row For r = 12 To i '<<change start row If Range("M" & r).Value < Empty Then SQLStr = "INSERT INTO [test] " _ & "Values ('" & Range("A" & r).Value & "', '" _ & Range("B" & r).Value & "', '" _ & Range("C" & r).Value & "', '" & Range("D" & r).Value _ & "', '" & Range("E" & r).Value & "', '" _ & Range("F" & r).Value & "', '" & Range("G" & r).Value _ & "', '" & Range("H" & r).Value & "', '" _ & Range("I" & r).Value & "', '" & Range("J" & r).Value _ & "', '" & Range("K" & r).Value & "', '" _ & Range("L" & r).Value & "', '" & Range("M" & r).Value _ & "', '" & Range("N" & r).Value & "', '" & Now() & "')" MyCn.Execute SQLStr If delRows Is Nothing Then Set delRows = Range("A" & r) Else Set delRows = Union(delRows, Range("A" & r)) End If End If Next r delRows.EntireRow.Delete MsgBox "Data has been uploaded to CDI ERROR DATA" MyCn.Close Set MyCn = Nothing End Sub Hope this helps Rowan Justin wrote: Once again, it works perfectly Last question. I am uploading column a-n to a table in access. now the table has the same headers but I need to have an add'l column (to be when the file was uploaded). Now i tried to adjust it on the xcel sheet to add column 'O' but it comes out as text. I tried to make that column in access a Date/Time attribute but it will only accept text. What can i do so that I can make it a date? Also, is there a way that when you hit the upload button, it ONLY takes rows that have a comment and leave the rest to be worked later on. and maybe delete all records that was just upload so that it doesn't make a duplicate. "Rowan Drummond" wrote: |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
There should be a total of 15 columns (fields) in your table. The last
one will contain the date/time and should be set to that datatype. Regards Rowan Justin wrote: 1) The problem or error message that i am encountering is this [Microsoft][ODBC Microsoft Access Driver] Number of Query values and destination fields are not the same. This is when i added another column to the access table "Rowan Drummond" wrote: Hi Justin That looks like three questions to me <bg 1) If I understand you correctly you are wanting to have the date (and maybe time) that the data was loaded recorded in the database. If this is the case then you do not need to add any columns to your excel sheet. Add the extra column to your access table, give it a data type of Date/Time and use the Now() or Date functions in your upload query. Now() will give you date and time, Date will give you just the date. I have used Now() in the example below. If you want just the date replace Now() with Date. 2.) Assuming your comment is in Column M (which I think it is from earlier posts) then the example below will only load rows where the value in M is not empty. 3.) Finally this example deletes all the rows which have just been loaded into the database. Sub loadData() Dim MyCn As ADODB.Connection Dim SQLStr As String Dim i As Long Dim r As Long Dim delRows As Range Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb" i = Cells(Rows.Count, 1).End(xlUp).Row For r = 12 To i '<<change start row If Range("M" & r).Value < Empty Then SQLStr = "INSERT INTO [test] " _ & "Values ('" & Range("A" & r).Value & "', '" _ & Range("B" & r).Value & "', '" _ & Range("C" & r).Value & "', '" & Range("D" & r).Value _ & "', '" & Range("E" & r).Value & "', '" _ & Range("F" & r).Value & "', '" & Range("G" & r).Value _ & "', '" & Range("H" & r).Value & "', '" _ & Range("I" & r).Value & "', '" & Range("J" & r).Value _ & "', '" & Range("K" & r).Value & "', '" _ & Range("L" & r).Value & "', '" & Range("M" & r).Value _ & "', '" & Range("N" & r).Value & "', '" & Now() & "')" MyCn.Execute SQLStr If delRows Is Nothing Then Set delRows = Range("A" & r) Else Set delRows = Union(delRows, Range("A" & r)) End If End If Next r delRows.EntireRow.Delete MsgBox "Data has been uploaded to CDI ERROR DATA" MyCn.Close Set MyCn = Nothing End Sub Hope this helps Rowan Justin wrote: Once again, it works perfectly Last question. I am uploading column a-n to a table in access. now the table has the same headers but I need to have an add'l column (to be when the file was uploaded). Now i tried to adjust it on the xcel sheet to add column 'O' but it comes out as text. I tried to make that column in access a Date/Time attribute but it will only accept text. What can i do so that I can make it a date? Also, is there a way that when you hit the upload button, it ONLY takes rows that have a comment and leave the rest to be worked later on. and maybe delete all records that was just upload so that it doesn't make a duplicate. "Rowan Drummond" wrote: |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
Need your assistance, hopefully for this last time
From the excel sheet, the new process is deleting all records that have a comment Talked to the boss of the dept and they do not like the idea Wonder if you can help me. I get the excel sheet, it has 14 columns, the last 3 being where the user add comment, a drop down field and add's their ID. Right now, if any of those 3 cells are missing from a row, it won't upload (using office 2000). 1) the cells that are ID's and Comments should not be required when upload 2) if the drop down says "Logistic Asset Validated", then that row is to be uploaded and deleted 3) if the drop down says "Barcode Identity Issue" or "Pending Research" or "No Trouble found" or "Missed Issues", then it will be uploaded BUT will still be visible and still have the ability to modify their drop down. 4) if one day it says "Barcode Identity Issue" and the following day it was changed to "No Trouble found", and i press upload should have a YES/NO warning screen to tell user that file has been uploaded again, do you want to replace, yes to replace, no to just cancel and msg should be like no data has been uploaded 5) When user click on upload button, before uploading, it should check the database for the same Unique Number (cell A in xcel and unique number in access). if there is a new ID, upload, if same, give warning. I'm trying to make it idiot proof, please forgive me for asking all this question. I do appreciate you helping me. Thanks |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
Hi Justin
This is not intended to be a complete solution (it would be very difficult for me to provide that without access to your actual data) but it should get you started. The excel file has 14 columns and column M has the dropdown mentioned in your post. If this column is not populated the row will not be processed. The access table has 15 fields the last one holding the date and time that the row was inserted/updated (called TheDate in my example). The first field is the ID (called TheID in my database) and is a long integer. All other fields are text fields named Field2, Field3, Field4 etc. Regards Rowan '--Declarations------------------------------------------- Option Explicit Private Const m_cDBLocation As String _ = "F:\System Analyst\Logistics\Logistics CDI.mdb" '----Code Start-------------------------------------------- Sub loadData() Dim MyCn As ADODB.Connection Dim SQLStr As String Dim rs As ADODB.Recordset Dim i As Long Dim r As Long Dim delRows As Range Dim TableName As String Dim chkField As String Dim ID As Long Dim DrpDwn As String TableName = "test" '<<change as required chkField = "TheID" '<<change as required Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=" & m_cDBLocation i = Cells(Rows.Count, 1).End(xlUp).Row For r = 12 To i '<<change start row ID = Range("A" & r).Value DrpDwn = Range("M" & r).Value '<<check dropdown column If DrpDwn < "" Then 'only process row if dropdown populated 'check to see if ID is already in table Set rs = RunQuery(TableName, chkField, ID, True) If rs.RecordCount 0 Then 'if id in table then prompt to update If MsgBox("Do you want to update record " & _ ID, vbYesNo) = vbYes Then '<<change all field names in the update query 'Create update query SQLStr = "UPDATE " & TableName _ & " Set Field2 = '" & Range("B" & r).Value & "', " _ & "Field3 = '" & Range("C" & r).Value & "', " _ & "Field4 = '" & Range("D" & r).Value & "', " _ & "Field5 = '" & Range("E" & r).Value & "', " _ & "Field6 = '" & Range("F" & r).Value & "', " _ & "Field7 = '" & Range("G" & r).Value & "', " _ & "Field8 = '" & Range("H" & r).Value & "', " _ & "Field9 = '" & Range("I" & r).Value & "', " _ & "Field10 = '" & Range("J" & r).Value & "', " _ & "Field11 = '" & Range("K" & r).Value & "', " _ & "Field12 = '" & Range("L" & r).Value & "', " _ & "Field13 = '" & Range("M" & r).Value & "', " _ & "Field14 = '" & Range("N" & r).Value & "', " _ & "TheDate = '" & Now() & "'" _ & " Where TheID = " & ID MyCn.Execute SQLStr If DrpDwn = "Logistic Asset Validated" Then If delRows Is Nothing Then Set delRows = Range("A" & r) Else Set delRows = Union(delRows, Range("A" & r)) End If End If End If Else SQLStr = "INSERT INTO " & TableName _ & " Values ('" & Range("A" & r).Value & "', '" _ & Range("B" & r).Value & "', '" _ & Range("C" & r).Value & "', '" & Range("D" & r).Value _ & "', '" & Range("E" & r).Value & "', '" _ & Range("F" & r).Value & "', '" & Range("G" & r).Value _ & "', '" & Range("H" & r).Value & "', '" _ & Range("I" & r).Value & "', '" & Range("J" & r).Value _ & "', '" & Range("K" & r).Value & "', '" _ & Range("L" & r).Value & "', '" & Range("M" & r).Value _ & "', '" & Range("N" & r).Value & "', '" & Now() & "')" MyCn.Execute SQLStr If DrpDwn = "Logistic Asset Validated" Then If delRows Is Nothing Then Set delRows = Range("A" & r) Else Set delRows = Union(delRows, Range("A" & r)) End If End If End If End If Next r If Not delRows Is Nothing Then delRows.EntireRow.Delete MsgBox "Data has been uploaded to CDI ERROR DATA" Set rs = Nothing MyCn.Close Set MyCn = Nothing End Sub Public Function RunQuery(ByVal TableName As String, _ ByVal fieldname As String, ByVal val As Long, _ ByVal blnConnected As Boolean) As ADODB.Recordset 'adapted from post by Jim Thomlinson Dim strConnection As String strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & m_cDBLocation & ";" Set RunQuery = New ADODB.Recordset With RunQuery .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockBatchOptimistic End With RunQuery.Open "Select * from " & TableName & " Where " & _ fieldname & " = " & val, strConnection, , , adCmdText If blnConnected = False Then _ Set RunQuery.ActiveConnection = Nothing End Function '-----End Code------------------------------------------------ Regards Rowan Justin wrote: Need your assistance, hopefully for this last time From the excel sheet, the new process is deleting all records that have a comment Talked to the boss of the dept and they do not like the idea Wonder if you can help me. I get the excel sheet, it has 14 columns, the last 3 being where the user add comment, a drop down field and add's their ID. Right now, if any of those 3 cells are missing from a row, it won't upload (using office 2000). 1) the cells that are ID's and Comments should not be required when upload 2) if the drop down says "Logistic Asset Validated", then that row is to be uploaded and deleted 3) if the drop down says "Barcode Identity Issue" or "Pending Research" or "No Trouble found" or "Missed Issues", then it will be uploaded BUT will still be visible and still have the ability to modify their drop down. 4) if one day it says "Barcode Identity Issue" and the following day it was changed to "No Trouble found", and i press upload should have a YES/NO warning screen to tell user that file has been uploaded again, do you want to replace, yes to replace, no to just cancel and msg should be like no data has been uploaded 5) When user click on upload button, before uploading, it should check the database for the same Unique Number (cell A in xcel and unique number in access). if there is a new ID, upload, if same, give warning. I'm trying to make it idiot proof, please forgive me for asking all this question. I do appreciate you helping me. Thanks |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
Ok, i tried this but I'm getting some error, whenever I try to upload data
that has a comment in cell M. Maybe importing the data to another database is not a good thing. Let me try to explain all the table names and field names of excel and maybe i paint you a better picture of the database. Im thinking, instead of having the data uploaded to another database, why don't it just update the table that made the excel sheet. Just add 4 more columns to that table and it can make everything easier, im guessing anywaz, the databasename and location is \\csc_data2\common1\Logistics\Logistics Aged Inv Process\DATABASE\CDI ERROR\CDI ERROR.mdb the table name is "CDI Import_Detail" The fields a CDI ID (Autonumber) Date (text) Corp (text) Account # (text) Org (text) Locator (text) SubInventory (text) Box Status (text) Serial Number (text) Part # (text) Operator ID (text) Date of Oracle Import (date/time) ----the next 4 are just added, im thinking about updating this table instead of creating a new table---- Coordinator ID (text) CDI Comments (text) Coordinator Comments (text) Date file Uploaded (date/Time) Now, i have a command button that will take data from this table and export CDI ID (Autonumber) Date (text) Corp (text) Account # (text) Org (text) Locator (text) SubInventory (text) Box Status (text) Serial Number (text) Part # (text) Operator ID (text) to excel template. In excel, the fields a Name Column CDI ID A Date B ORG C Sub Inventory D LOC E CVC Part # F Serial # G CableDataBox Status H Opr ID I Corp J Account # K Coordinator ID L CDI Comments M Coordinator Comments N NOw, i want to just update the access table, not insert, but only update if there is a CDI comment (which is a dropdown) also, coordinator ID and Coordinator Comment isn't required. SO the ID is always goin gto be in that table. Just need to insert Coordinator ID, CDI COMMENTS and COordinator Comments, and Date back into that table. and If it says "logistic asset validated" then delet it from the excel sheet, but it says anythign else, upload but have it still stay in the sheet, and if they change that comment warning screen will appear to ask if they want to update or replace, yes to replace, no to not replace. |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
I'll try to get you something tomorrow.
Regards Rowan Justin wrote: Ok, i tried this but I'm getting some error, whenever I try to upload data that has a comment in cell M. Maybe importing the data to another database is not a good thing. Let me try to explain all the table names and field names of excel and maybe i paint you a better picture of the database. Im thinking, instead of having the data uploaded to another database, why don't it just update the table that made the excel sheet. Just add 4 more columns to that table and it can make everything easier, im guessing anywaz, the databasename and location is \\csc_data2\common1\Logistics\Logistics Aged Inv Process\DATABASE\CDI ERROR\CDI ERROR.mdb the table name is "CDI Import_Detail" The fields a CDI ID (Autonumber) Date (text) Corp (text) Account # (text) Org (text) Locator (text) SubInventory (text) Box Status (text) Serial Number (text) Part # (text) Operator ID (text) Date of Oracle Import (date/time) ----the next 4 are just added, im thinking about updating this table instead of creating a new table---- Coordinator ID (text) CDI Comments (text) Coordinator Comments (text) Date file Uploaded (date/Time) Now, i have a command button that will take data from this table and export CDI ID (Autonumber) Date (text) Corp (text) Account # (text) Org (text) Locator (text) SubInventory (text) Box Status (text) Serial Number (text) Part # (text) Operator ID (text) to excel template. In excel, the fields a Name Column CDI ID A Date B ORG C Sub Inventory D LOC E CVC Part # F Serial # G CableDataBox Status H Opr ID I Corp J Account # K Coordinator ID L CDI Comments M Coordinator Comments N NOw, i want to just update the access table, not insert, but only update if there is a CDI comment (which is a dropdown) also, coordinator ID and Coordinator Comment isn't required. SO the ID is always goin gto be in that table. Just need to insert Coordinator ID, CDI COMMENTS and COordinator Comments, and Date back into that table. and If it says "logistic asset validated" then delet it from the excel sheet, but it says anythign else, upload but have it still stay in the sheet, and if they change that comment warning screen will appear to ask if they want to update or replace, yes to replace, no to not replace. |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
thank you once again for your assistance
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
Hi Justin
I have tried to get this working but my solution is not fool proof. The problem is to do with the way Access treats fields that are null or contain the text "" ie look blank but are not null. If you can't get this to work I suggest try one of 3 options: 1) Look into having your users update the Access database directly using an Access Userform. 2) Repost your question giving as much detail as possible and maybe the code below. I know there are other people on this newsgroup that are a lot more skilled at Access than myself. 3) Get some good books <g This is where I got to: '--Declarations------------------------------------------- Option Explicit Private Const m_cDBLocation As String _ = "C:\Temp\db1.mdb" '<<Change Database Path and Name '----Code Start-------------------------------------------- Sub loadData() Dim MyCn As ADODB.Connection Dim SQLStr As String Dim rs As ADODB.Recordset Dim i As Long Dim r As Long Dim delRows As Range Dim TableName As String Dim ID As Long Dim CDICmt As String Dim CoOrdId As String Dim CoOrdCmt As String TableName = "CDI Import_Detail" Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=" & m_cDBLocation i = Cells(Rows.Count, 1).End(xlUp).Row For r = 12 To i '<<change start row ID = Range("A" & r).Value CDICmt = Range("M" & r).Value CoOrdId = Range("L" & r).Value CoOrdCmt = Replace(Range("N" & r).Value, "'", "") CoOrdCmt = Replace(CoOrdCmt, """", "") If CDICmt < "" Then 'only process row if dropdown populated 'check to see if anything changed Set rs = RunQuery(TableName, ID, CDICmt, CoOrdId, CoOrdCmt, True) If rs.RecordCount 0 Then 'if id in table then prompt to update If MsgBox("Do you want to update record " & _ ID, vbYesNo) = vbYes Then 'Create update query SQLStr = "UPDATE [" & TableName & "]" _ & " Set [Coordinator ID] = '" & CoOrdId & "', " _ & "[CDI Comments] = '" & CDICmt & "', " _ & "[Coordinator Comments] = '" & CoOrdCmt & "', " _ & "[Date file Uploaded] = '" & Now() & "'" _ & " Where [CDI ID] = " & ID MyCn.Execute SQLStr If CDICmt = "Logistic Asset Validated" Then If delRows Is Nothing Then Set delRows = Range("A" & r) Else Set delRows = Union(delRows, Range("A" & r)) End If End If End If End If End If Next r If Not delRows Is Nothing Then delRows.EntireRow.Delete MsgBox "Data has been uploaded to CDI ERROR DATA" Set rs = Nothing MyCn.Close Set MyCn = Nothing End Sub Public Function RunQuery(ByVal TableName As String, ByVal CDIID As _ Long, ByVal CDICmt As String, ByVal CoOrdId As String, ByVal CoOrdCmt _ As String, ByVal blnConnected As Boolean) As ADODB.Recordset 'this function is meant to check if anything has changed 'problem is if you change one field but not others it 'appears that the update query above changes the Null fields 'not changed to "" 'The next time you run the function without making any further 'changes you are prompted to update that record again. 'One possible solution: Change the SQLstr update query to 'only update fields where the value of the variale is not "" 'but this would mean the user could never remove a comment Dim strConnection As String strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & m_cDBLocation & ";" Set RunQuery = New ADODB.Recordset With RunQuery .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockBatchOptimistic End With If CoOrdId = "" And CoOrdCmt = "" Then RunQuery.Open "Select * from [" & TableName & _ "] Where ([Coordinator ID] is Not Null" & _ " or [CDI Comments] < '" & CDICmt & _ "' or [CDI Comments] is Null or [Coordinator Comments]" _ & " is Not Null)" _ & " and [CDI ID] = " & CDIID & "", strConnection, , , _ adCmdText ElseIf CoOrdId = "" And CoOrdCmt < "" Then RunQuery.Open "Select * from [" & TableName & _ "] Where ([Coordinator ID] is Not Null" & _ " or [CDI Comments] < '" & CDICmt & _ "' or [CDI Comments] is Null or [Coordinator Comments]" _ & " < '" _ & CoOrdCmt & "' or [Coordinator Comments] is Null)" & _ " and [CDI ID] = " & CDIID & "", strConnection, _ , , adCmdText ElseIf CoOrdId < "" And CoOrdCmt = "" Then RunQuery.Open "Select * from [" & TableName & _ "] Where ([Coordinator ID] < '" & CoOrdId & _ "' or [Coordinator ID] is Null or [CDI Comments]" _ & " < '" & CDICmt & _ "' or [CDI Comments] is Null or [Coordinator Comments]" _ & " is Not Null)" _ & " and [CDI ID] = " & CDIID & "", strConnection, , , _ adCmdText ElseIf CoOrdId < "" And CoOrdCmt < "" Then RunQuery.Open "Select * from [" & TableName & _ "] Where ([Coordinator ID] < '" & CoOrdId & _ "' or [Coordinator ID] is Null or [CDI Comments]" _ & " < '" & CDICmt & _ "' or [CDI Comments] is Null or [Coordinator Comments]" _ & " < '" _ & CoOrdCmt & "' or [Coordinator Comments] is Null)" & _ " and [CDI ID] = " & CDIID & "", strConnection, _ , , adCmdText End If If blnConnected = False Then Set RunQuery.ActiveConnection = _ Nothing End Function '-----End Code------------------------------------------------ Regards Rowan Justin wrote: Ok, i tried this but I'm getting some error, whenever I try to upload data that has a comment in cell M. Maybe importing the data to another database is not a good thing. Let me try to explain all the table names and field names of excel and maybe i paint you a better picture of the database. Im thinking, instead of having the data uploaded to another database, why don't it just update the table that made the excel sheet. Just add 4 more columns to that table and it can make everything easier, im guessing anywaz, the databasename and location is \\csc_data2\common1\Logistics\Logistics Aged Inv Process\DATABASE\CDI ERROR\CDI ERROR.mdb the table name is "CDI Import_Detail" The fields a CDI ID (Autonumber) Date (text) Corp (text) Account # (text) Org (text) Locator (text) SubInventory (text) Box Status (text) Serial Number (text) Part # (text) Operator ID (text) Date of Oracle Import (date/time) ----the next 4 are just added, im thinking about updating this table instead of creating a new table---- Coordinator ID (text) CDI Comments (text) Coordinator Comments (text) Date file Uploaded (date/Time) Now, i have a command button that will take data from this table and export CDI ID (Autonumber) Date (text) Corp (text) Account # (text) Org (text) Locator (text) SubInventory (text) Box Status (text) Serial Number (text) Part # (text) Operator ID (text) to excel template. In excel, the fields a Name Column CDI ID A Date B ORG C Sub Inventory D LOC E CVC Part # F Serial # G CableDataBox Status H Opr ID I Corp J Account # K Coordinator ID L CDI Comments M Coordinator Comments N NOw, i want to just update the access table, not insert, but only update if there is a CDI comment (which is a dropdown) also, coordinator ID and Coordinator Comment isn't required. SO the ID is always goin gto be in that table. Just need to insert Coordinator ID, CDI COMMENTS and COordinator Comments, and Date back into that table. and If it says "logistic asset validated" then delet it from the excel sheet, but it says anythign else, upload but have it still stay in the sheet, and if they change that comment warning screen will appear to ask if they want to update or replace, yes to replace, no to not replace. |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
exporting to excel
In Internet Explorer there is a menu that comes up when you right click on a webpage which allows you to 'export to Excel". I've done this but the data, which comes from flickr is only organised into rows. I'm trying to extract the "views" numbers to add them up. What would be the best way to accomplish this? Are there settings in IE that change how data is exported or is there a function in Excel that will allow me to add up numbers adjacent to text in a cell given the condition that the text is something like "views"? "Justin" wrote: Need your assistance, hopefully for this last time From the excel sheet, the new process is deleting all records that have a comment Talked to the boss of the dept and they do not like the idea Wonder if you can help me. I get the excel sheet, it has 14 columns, the last 3 being where the user add comment, a drop down field and add's their ID. Right now, if any of those 3 cells are missing from a row, it won't upload (using office 2000). 1) the cells that are ID's and Comments should not be required when upload 2) if the drop down says "Logistic Asset Validated", then that row is to be uploaded and deleted 3) if the drop down says "Barcode Identity Issue" or "Pending Research" or "No Trouble found" or "Missed Issues", then it will be uploaded BUT will still be visible and still have the ability to modify their drop down. 4) if one day it says "Barcode Identity Issue" and the following day it was changed to "No Trouble found", and i press upload should have a YES/NO warning screen to tell user that file has been uploaded again, do you want to replace, yes to replace, no to just cancel and msg should be like no data has been uploaded 5) When user click on upload button, before uploading, it should check the database for the same Unique Number (cell A in xcel and unique number in access). if there is a new ID, upload, if same, give warning. I'm trying to make it idiot proof, please forgive me for asking all this question. I do appreciate you helping me. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
exporting to excel | Excel Discussion (Misc queries) | |||
exporting from Excel | Excel Discussion (Misc queries) | |||
exporting to excel | Excel Discussion (Misc queries) | |||
Exporting into Excel | Excel Discussion (Misc queries) | |||
Exporting to Excel | Excel Programming |