Insert File Name in Excel cell

[JaneB19]

Insert possibly isn't the correct word but I can't think of another was of explaining what I am attempting to do.

I have a spreadsheet which contains data that is uploaded into it. I have the data from the uploaded spreadsheet in the 'database' spreadsheet but I would like the filename shown on the 'database' so that users know which files have already been uploaded.

Does anybody know of a way of doing this? The uploaded file is found using the following code:

DataFile = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls&quot
Application.WindowState = xlMaximized
Workbooks.Open (DataFile)

I hope somebody can help, as this is one of the few things I have left to do!

Thanks

Jane

[PC2]

 

[Bowers74]

Can't you just use:

range("A1&quot = DataFile

Where Range("A1&quot is the location where the file name is supposed to be "input"?

Or have I missed something?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JaneB19]

Hate to say this but "you've missed something" :-D

The 1st row is used for the column headings, for example: Scorecard name, Job Start Date, Company, etc

I have VBA coding in which allows me to find the 1st empty row and insert the uploaded data into the cells, starting at the 2nd column because I'd like the filename to be shown in the 1st. I hope this is making sense?!? Therefore I would like to find some coding that will allow me to return the file name and insert it into the 1st cell in the row.

I really hope that makes some sense

Thank you for your quick reply

Jane

[PC2]

 

[Bowers74]

No I didn't miss anything, I just assumed that you would understand what I meant.

You already have the file name - DataFile

range(WhereTheNameIsSupposedToAppear) = DataFile

Just shift your "Import" Macro over one Coulmn. ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JaneB19]

Hmm, maybe it's because I'm new to this (unfortunately I've had to teach myself as I've gone along, they don't teach VB or VBA at Uni!), but I didn't manage to get your help to work.

I tried the following:

Worksheet("Slickline&quot.Range(LastRow.Column("A&quot) = DataFile

I read your tip earlier about not having Workbooks().Worksheet("Sheet1&quot but at the moment I'd just like to get the main code working

It doesn't like LastRow, where
LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row
LastRow = LastRow + 1

I'm sorry if I seem to be being really stupid, but as I said, I'm very much new to this

Thanks again

Jane

[PC2]

 

[Bowers74]

You have declared the LastRow correctly with:

LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

And since you have already "told" the procedure where the last row is you need to change:

Worksheet("Slickline&quot.Range(LastRow.Column("A&quot) = DataFile

to

Worksheet("Slickline&quot.cells(LastRow, 1) = DataFile

or you could use:

Worksheet("Slickline&quot.Range("A" & LastRow) = DataFile

I hope this helps!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JaneB19]

Hi Mike,

The coding is now working!!!

Thank you very much for your help and patience! :-D

Jane

[PC2]