[Karl yarwood]

Hi

I am extracting data from SQL server into Excel through an ODBC datasource. I need to add manual data into the spreadsheet but when I do a data refresh the manual data does not 'link' to the original data it was input against and therefore ends up mis-aligned.

How can I ensure that the manual entries move with the original data when refreshed?

Thanks for any help.

 

[RSpecianjr]

Hey Karl yarwood,

I've only dabbled in ODBC connections so I may not be the most qualified, but im pretty good with problem solving. Can you give me an example of how the data becomes misaligned?

Are you attempting to update the original SQL Database with the manual entries or are the manual entries just for your Excel end?

Regards,

Robert D. Specian Jr.

 

[Karl yarwood]

Hi Robert

Any help is appreciated as I am drawing a blank with this problem.

the spreadsheet I have created is extracting data in columns A through F from an external data source in SQL server. The spreadsheet is being used as a planning tool so the data I am extracting is sales info. I am entering manual data (i.e dates/comments) into columns G & H. When I refresh the extracted data new rows can be added or removed (i.e. if an order had been deleted for example) but because the manual data is not linked in anyway to the extracted data the manual data remains where it was originally input and does not move with the refreshed data.

Eg

In Row 1 is sales order 1234 with a comment 'test' against it. When data is refreshed sales order 1234 moves to row 2 but comment 'test' remains in row 1.

Pleae help!!!!

 

[RSpecianjr]

Hey Karl yarwood,

Alright, I would have two sheets. One with the extracted data and another with extracted data + manual data (lets call it updated data). If when you refresh the extracted data, data is missing, a macro is run on the updated data to remove the entire row of data that was deleted form the database. That way the updated data is tied in by row.

Do you have unique identifiers for the data you are extracting?
Is there a LOT of data, so much where duplication could be detrimental?

Regards,

Robert D. Specian Jr.

 

[Karl yarwood]

Hi Robert

Yes there is a unique identifier for each row. I assume I would have to sort the data so that any new lines of data would be added to the end of the spreadsheet for your idea to work???

Can you advise on how to create the macro?

Thanks

 

[RSpecianjr]

Hey,

No need to sort, though it would make it easier to write the code for it.

I can write the code, I just need a bit of information:

The column the Unique ID is in.
Do you have headers?
The worksheet name that he Extracted data is on.
The worksheet name for the updated data.
What version of Excel do you use?

Regards,

Robert D. Specian Jr.

 

[Karl yarwood]

Hey

Thanks for this, answers as follows:

1) Unique Identifier is in column A
2) Yes I have headers
3) Extracted data worksheet name: WORKS ORDER DOWNLOAD
4) Updated data worksheet name: Data
5) Excel is version 2000

 

[RSpecianjr]

Hey,

I'm not sure if it will work well in 2000, because I am writing in in 2007, but we can troubleshoot it as we go.

With the workbook open hit Alt+F11 this should open up the VBA Editor window. There should be a place to add a new module. Add one in and paste the following code:

Sub test()
Dim cnt, rw As Integer
Dim val As Variant

cnt = Worksheets("Data").Range("A20000").End(xlUp).Row
On Error Resume Next

For rw = cnt To 2 Step -1
val = Empty
val = Application.WorksheetFunction.VLookup(Worksheets("Data").Range("A" & rw).Value, Worksheets("WORKS ORDER DOWNLOAD").Range("A:A"), 1, False)
If val = Empty Then
Worksheets("Data").Rows(rw).Delete
End If
nxt:
Next rw
End Sub

When you hit play, it will delet the lines in Data whose Unique ID is not found in WORKS ORDER DOWNLOAD.

Will that work for you?

Regards,

Robert D. Specian Jr.

 

[Karl yarwood]

Hey

One way to find out.

I now have a problem with my table joins in my original query which I need to sort out before I can test your code otherwise I have duplicate unique identifiers!!!!

Will correct the query tomorrow and test the code at same time - will reply as soon as possible tomorrow.

thank you so much for your help so far..... to be continued.......

 

[RSpecianjr]

No worries, we are always here to help!