Import Excel data - File not open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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.
 
I have never experienced anything like that. Is there something unusual
about the formatting of the spreadsheet?
 
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
 
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.
 
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.
 
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!
 
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.
 
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.
 
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.
 
Back
Top