Import cell value from a file that is hyperlink opened

[Darnoky]

Hello,

I need a help. I have an excel sheet with hyperlink pathes in Column A. (e.g.:A2, A3...etc.)

My purpose is to open each of them, so loop through

and when opened one check the opened one's sheet (1).Range("BP3").Value

and paste it back to orginal excel sheet, just next to the path (that is in column B) of the opened file.
Actually I could loop through the hyperlink pathes to open them, but do not know how to "look into" the opened
file for data.

And of course close the opened file after a cell value is pasted.
One more info, all the hyperlinked files are ".CSV" files

Thank you for your help.

 

[JazzSP8]

Hi

This might work for you;

Sub OpenFilesAndCopy()
Application.ScreenUpdating = False

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For x = 1 To LastRow

    Workbooks.Open Cells(x, "A").Value
    NoS = Left(Dir(Cells(x, "A").Value), Len(Dir(Cells(x, "A").Value)) - 4)
    Worksheets(NoS).Range("BP3").Copy Range("B" & x)
    ActiveWorkbook.Close False
    
Next x

Application.ScreenUpdating = True
End Sub

This code should be pasted into the Sheet and not a Module.

 

[Darnoky]

Hi

I copied it to the sheet and run it.
It stopped with error message of "Run-time eror '1004'.
Debugged: in codeline of Workbooks.Open Cells(X,"A").Value.

 

[JazzSP8]

Do you have an example of how your workbooks are listed in Column A?

 

[JazzSP8]

Uhm.... Not very helpful that

Can you just paste a text example, I just need to see what format the text string takes, how it looks in the cell.

Thanks

 

[Darnoky]

Sorry for that. This was not my intention.

Here is one:
C:\Users\KALLAIL\Documents\2018\01\25\8A125A025_07_06_46_NOK_382332.csv

 

[JazzSP8]

Odd, that is how I assumed it would look.

OK - Try this?

Sub OpenFilesAndCopy()
Application.ScreenUpdating = False

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For x = 1 To LastRow

    ThisWorkbook.FollowHyperlink Cells(x, "A").Value
    NoS = Left(Right(Cells(x, "A").Value, Len(Cells(x, "A").Value) - InStrRev(Cells(x, "A").Value, "\")), Len(Right(Cells(x, "A").Value, Len(Cells(x, "A").Value) - InStrRev(Cells(x, "A").Value, "\"))) - 4)
    Worksheets(NoS).Range("BP3").Copy Range("B" & x)
    ActiveWorkbook.Close False
    
Next x

Application.ScreenUpdating = True
End Sub

 

[Darnoky]

This time,

Run-time error '5
Invalid procedure call or argument

I am not sure but maybe FollowHyperlink is a trouble one.

Or FollowHyperlink needs parenthesis expression.

 

[JazzSP8]

As much as I hate to say it, "it works fine on my machine" :-/

Sounds like the name of something is the problem.

When you open your CSV file normally, what name is Excel giving the worksheet?

I'd expect it to give it as "8A125A025_07_06_46_NOK_382332", the CSV files I have here and have been using are all named like PS0711, PS0611 etc and when I renamed one "8A125A025_07_06_46_NOK_382332" it named the sheet as that.

 

[Darnoky]

Hi,

At end I found the problem was that x=1 but my Hyperlinked pathes started from A2, so it could not find any hyperlink to open.
After I removed empty row immediatelly solved the problem

Thank you very much.

It realy helped me and I could learned many things from that.

I appreciate your help very much!