Linking an excel spreadsheet 1

[toptech]

Hi All.

I am trying to link an Excel spreadsheet using a macro.

This macro will link the spreadsheet if the file exists in a directory.

Everthing currently works if I specify the exact range.

Macro Looks like this:

I am using: Trasferspreadsheet

Transfer type: Link
Spreadsheet type: Excel 5-7
Table name: abc_Excel
File name: C:\excelfiles
Has field names: Yes
Range: shipments details!A4-BE126


One of the options is to specify a range.

which works for me because my headers start at row A4.

However, as I have various excel files emailed to me every week, I never know where the last row will be.

I therefore will not know where the ending range will be.

Any ideas on how to determine this last row(ending range)?

Range: shipments details!A4-BE????

Thanks in advance.

Jeff

Toptech.

Top Tech Systems

If you help 3 people and each one of those help 3 other people and so on, imagine all the people being helped.

 

[Darrylles]

toptech,

1) Never use MS Access macros - use VBA.
2) Never use linked excel sheets as data-tables per-se.

For 2: Use the link to the sheet, but only to import into a properly defined table.

Once the data is imported, run a query to delete any null records.

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[boblarson]

Darrylle wrote:
"1) Never use MS Access macros - use VBA.
2) Never use linked excel sheets as data-tables per-se."

I highly disagree with your assertion on #1. There is no reason to not use macros. In fact, they are even better now in Access 2007 and 2010 than they have ever been. Now, I typically don't use them but there is no real reason to NOT use them. That is just bias talking.

As for #2, I kind of agree with this because you can run into locking issues quite frequently with either the worksheet not being able to be updated because it is currently in use in the Access database or the database can't use it because someone has the workbook open and are working with it.

Just thought I needed to answer #1 there because it is just a bias and there is no valid reason to avoid the use of macros. (Access developer 14 years)

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[Lilliabeth]

I'm with Bob.

Folks who design in Access usually do so because they work for an organization or in a department that needs a database.

Those busy people should not be burdened with the VBA learning curve, when instead they can use this very nifty interface:

http://blogs.msdn.com/b/access/archive/2009/07/28/meet-the-access-2010-macro-designer.aspx

--Lilliabeth

 

[Lilliabeth]

Forgot to say... I think VBA's days are numbered.

--Lilliabeth

 

[Lilliabeth]

Since you are receiving the workbook in email, would it be feasible to name the active range in Excel something like "data" each time?

--Lilliabeth

 

[SkipVought]

which works for me because my headers start at row A4.However, as I have various excel files emailed to me every week, I never know where the last row will be.I therefore will not know where the ending range will be.

With worksheets("shipments details").[A4].currentregion
activeworkbook.names.add _
Name:="Database", _
RefersTo:="='" & .parent.name & "'!" & .address
End with

I hope the syntax is correct. Don't have Excel on this machine.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[boblarson]

Forgot to say... I think VBA's days are numbered.
--Lilliabeth"

I don't think so. If they are numbered then it will be a long time yet. VBA is going to make it into Access 15 as well, so don't hold your breath.


Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com