Import Excel data - File not open

[KJ45]

I have a macro built that imports data from an excel file. This works fine
when the actual excel spreadsheet is open. It will not work if the
spreadsheet is closed.

Can I add a command to my macro that opens the specific spreadsheet, then
closes it when the data is imported?

or

Can I import the data without opening the spreadsheet some other way?

Thanks for your time.

 

[Klatuu]

what command in your macro are you using to import the excel file. It should
not be necessary for it to be open. You should be using the
TransferSpreadsheet Action.

 

[KJ45]

I am using TransderSpreadsheet Action.

When I run my macro without the spreadsheet open I get the following error:

External Table is not in the expected format.

When I hit OK, the data is not imported. If I open the spreadsheet and run
the same macro, everything works just fine.

Thanks for taking the time to help me.

 

[Klatuu]

I have never experienced anything like that. Is there something unusual
about the formatting of the spreadsheet?

 

[KJ45]

Me making it is very unusual...

This is my first ever database, and I have two macros that import data and
both are doing the same thing.

The only thing I can think is that it is importing from a range of cells, if
that is not normal? Import from A3:N100

It is also on a network drive, if that could be the problem?

Thanks

 

[Klatuu]

It being on the network drive makes no difference. The only issue you have
there is how you map to the spreadsheet's location. If you are coding it as
"E:\SomeFolder..... and a user has that path mapped as G:, then they will
not find it. In a network situation, it is better ot map using UNC mapping
as in
\\MyServer\SomeFolder
You may need to get your network person to help with that.
I tried a few different syntax versions of a range to see what happened if I
did not do it correctly, but I could not get the error you are experiencing.

I did just think of one thing. Try copying the spreadsheet from the network
to your computer and see what happens. I just realized that some network
jockeys impose a higher level of macro security than you may have on your
computer. I have not seen the problem you have, but I did have an issue
using the followhyperlink method because of macro security settings on the
network. It allowed by to use the method, but I got a warning message.

 

[KJ45]

I copied it to my hard drive and changed the path in the macro.

I had the same problems. It did not import the data unless the file was open.

I am at a loss.

 

[Klatuu]

me too.
try a different spreadsheet to see if the same thing happens.

 

[KJ45]

It isn't the network, because I created a new simple database and imported an
excel file located on the network.

Here is what the macro is doing, incase you notice something odd here:

SetWarnings - No
OpenQuery - Runs delete query to remove old data from table
TransferSpreadsheet - My problem

It is obviously set up right, because it will import the correct data when
the excel file is open. Or at least I think that is obvious??????????

I can really mess things up!

 

[Klatuu]

I really have no clue what is going on here. This is only a wild guess, but
I wonder if it is a timing problem. I assume the delete query is deleting
data from the table you are importing in to. Try running each action
separately. Do the delete, wait a few seconds, then run the query.

Again, it is only a wild guess, but sometime I will try the most off the
wall tricks to help determine what a problem is.

I don't remember if you said you had the same problem with any other
spreadsheet files or if you had tried.

 

[KJ45]

I copied the macro and removed all operations other than the import, then
went and manually deleted all data from the table that the information would
be imported into.

I ran the new macro that only imports that data and recieved the same error.
I opened the excel file and ran it again and it worked.

I have two macros that import data from different spreadsheets and both of
them are acting this way.

I can only assume it is something wrong with the command to import, but
can't find anything wrong with it - most of it is basic. I have looked and
looked for a box that I can uncheck that requires the spreadsheet to be open.

I am still searching for anything that can help!

Thanks for all your help.

 

[Klatuu]

Okay, there is something wrong with your database. I don't know if it is a
corruption problem, a reference problem, a macro security problem, or
something else.

Try going to Tools, Macro, Security and see what the setting is there. If
this is the problem, then I know setting it to low allows you to open excel
files without any warnings. Also check macro security in Excel.

Try using a different mdb file.