Replies

I see the name under names paste also. However, it does not show up in the name box. If I press Ctrl ain excel and name the name the range through the name box, all is well.
I am attaching the error code I get in Access, the macro and the vba code.
Thanks,

ITconc

I am having a problem with the range name. It works the way you suggested. However, during the import, Access does not recognize the range name because it only shows up under insert/name/define not in the regular range name list.
I am not sure if this now goes under Access.
Thanks,

itconc

Looks great. It works in Excel. I will need to thenimport to Access – that is properly automated and works.
Thank you so much for your help!
ITconc

here it is:
Sub ex()
‘
‘ ex Macro
‘

‘
ChDir “C:ArbeitCASData”
ActiveWorkbook.SaveAs Filename:=”C:ArbeitCASDataNewData.XLS”, FileFormat _
:=xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:=”C:ArbeitCASDataNewData.htm”, FileFormat _
:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
Columns(“D:D”).Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=1
Columns(“F:Q”).Select
Selection.Delete Shift:=xlToLeft
Range(“A1″).Select
ActiveWorkbook.Save

‘
ActiveWorkbook.Close
Workbooks.Open Filename:=”C:ArbeitCASDataNewData.XLS”
Columns(“F:F”).Select
Selection.Delete Shift:=xlToLeft

Range(“A2”).Activate

ActiveWorkbook.Save
‘ActiveWorkbook.Close
End Sub
For the rangename I had this:
Sub Macro3()
‘
‘
‘
‘Range(“A1:Q19″).Select – I remarked this out.
‘
ActiveWorkbook.Names.Add Name:=”newdata”, RefersToR1C1:=selction.CurrentRegion.Select

Selection.CurrentRegion.Select
Application.Goto Reference:=”newdata”
End Sub

Thanks,
itconc

Thanks Paul. Good answer.
itconc

Thanks, will try asap.
I appreciate your help.

itconc

I have added the numeric identifiers in Excel and done 2 imports. Now all is well – the links worked, the data is clean!! Hyperlinks are OK and the title and Abstract are readable.

Thank you so much – both of you for helping me with this.

itconc

I cannot import the file as an excel spreadsheet without losing the hyperlink. My users are most adamant about the hyperlink. If absolutely necessary, I can live with bad title and abstract but not without the hyperlink.
I will try your suggestion with the numeric field and see what happens.

Thanks,

IT

I apologize. When I set up the test database, I grabbed the wrong table. I have deleted the nonmatching record. There is still no match, no ability to link. When I ran an unmatched query, none of the records matched. I then went in and copied the primary key value for 1 record from one table to the other. That 1 record showed a match.
Is it possible that the import does something to the fields? Wendell had said the the text import does not behave very well.
I am attching the revised test database.
Thanks,

itconc

yes they are. Both key fields are the same.
I am attaching the zipped file.
Thanks.
itconc

no error messages. I link in the relationship window and cannot set a one to one relationship with referential integrity on. When I link in a query I cannot get records from both tables.
I have tried to upload a file with only the two tables but am getting an error message that I am not permitted to upload this kind of file. I tried the mdb and tried chaing the file extension – no luck.

Any help is appreciated.

itconc

Wendell, I have been trying to work around the problem of the html import by doing 2 different imports:
1 straight forward Excel, another a html/ text import for the hyperlink.
I was hoping to be able to link the 2 tables after the import and grab the hyperlink field.
Nothing I have done works – both tables have the same key field. However, they will not link. I have checked field types and sizes – all is correct. Nothing works.
Do you have any idea what I could do here?

Thanks,

itconc

Sorry, at least I am not missing something else.

yes it does. I am going to try 2 imports – one spreadsheet for the memo filed, one htm for the hyperlink. I can recombine the files in Access.
I hope this will work.
Thanks for your answer – at least I am missing something else.

itconc

Well, I must have been out in the sun too long in Houston.
I now have a new problem. The hyperlink comes through just fine. However, the abstract field (set to memo in Access) gets truncated and words run into each other. I can see the problem during the manual import as I scroll from one column to the other.

Is there anything I can do to fix this?

Thanks,

itconc