Converting a range of cells to their values

[echo_oscar]

I'm new here so I apologize ahead of time for any errors I make in this thread.

Every week I am sent a small database of information on Excel. I need to take this worksheet and make a variety of conversions to it which involve various formulas, deleting cells, etc. After that is done, the information is imported into Access. Creating the macro and formulas to do this has not been much of an issue for me. However, part of the process is to use a column of formulas to analyze some of the data on the original sheet and create values which are compatible with my access worksheet, after which the columns of data which these values are derived from are deleted. I originally tried to do a simple copy and "paste values" of the worksheet, or the cells in question, which worked fine in Excel. However, I figured out that when I would then try to import the worksheet into Access, Access would not take the worksheet after I had used "paste special". So far the only thing that I have found to work is "F2 F9 Enter." I tried a Sendkey macro which would loop through the filled cells in the worksheet and apply "F2 F9 Enter." This seemed to work fine by itself, however, when I tried to insert that code in the middle of my macro something goes wrong.

Below is my macro:

Please Login or Register  to view this content.

The part toward the bottom where I leave a blank space is where I need to enter the code I've been having issues with. The code I've tried to use it below:

Please Login or Register  to view this content.

Any suggestions would be appreciated, and I would also appreciate any suggestions to my overall macro.

Thanks,
Erik

[wallyeye]

You might shorten it a bit:

Please Login or Register  to view this content.

The range().value = range().value will do the same as pastespecial.values.

[echo_oscar]

Thanks for the reply.

I seem to be getting an error when I try to run your macro.

I attached a test version of what I am trying to do. The content on worksheet "Enter" is an example of the type of content that I would receive. The content of "Sheet2" is what the macro should convert that information to. Your macro is already in this workbook.

[wallyeye]

Yeah, that is a problem with not having test data:

Please Login or Register  to view this content.

This is working better...

I switched it over to normal referencing, but RC would probably work as well.

[echo_oscar]

That code works great, but it brings me back to my original problem:
The range().value = range().value seems to do the same thing as pastespecial.values when I try to import the file into Access. If I try to import this file in an Access table with the same headers, after I've run the macro, Access gives me an error saying "Microsoft Access was unable to append all the data to the table" and none of the information ever makes it into Access. The only way that I've been able to get Access to take this file is if I apply "F2 F9 Enter" to all of the cells which use a formula, rather than pastespecial.values. The problem with that is that I don't know how to add it into a macro. I tried to do so by looping a Sendkey function, as I mentioned in my original post. That function worked fine when I ran it within its own macro, but when I insert the code into the main macro it starts the loop at "A1" and runs down column "A" regardless of what the code says. I also tried to run it as a submacro within the main macro and I got the same results. I don't know if there is another method I could use rather than "F2 F9 Enter" or if there is a way to fix that code, but that is just what I've observed so far.

Thanks again.

[wallyeye]

OK, you got me. I have no idea. When I run the macro, looking at the cell values, I cannot see any differences before and after the .value = .value statement. Do you see values in the cells after the .value = .value, or do you still see formulas? I had to use the .numberformat = "General" to get it to post values rather than the formulas. You might try inserting:

Please Login or Register  to view this content.

Right before your [A1].Select statement, maybe that will help.

[echo_oscar]

After the macro is run the cells are left with only their values. The formulas are no longer there. As far as Excel goes it all looks good. The problem begins when I try to import the worksheet into MS Access. Access won't take worksheet when I apply pastespecial.values or .value = .value. The only time I can get Access to take the worksheet is if I instead apply "F2 F9 Enter". Is there a way to loop a Sendkey function through all of the cells that will apply "F2 F9 Enter"? I tried using:

Please Login or Register  to view this content.

But once I insert it into the main macro is just runs down column "A" rather than running through the cells I specify.

[wallyeye]

That is definitely weird. Try this:

Please Login or Register  to view this content.

Turning off screen updating will speed up the process quite a bit.

[echo_oscar]

I tried to insert it into the main macro, but I got the same results. See below:

Please Login or Register  to view this content.

[wallyeye]

If you are embedding it, you can shorten things up a bit:

Please Login or Register  to view this content.

Any chance you could post your dummied up db? This just doesn't make sense...

Something else that could be done is to post the data directly into Access from within your macro, or write some vba in Access to pull in the Excel data. They work well together that way.

[echo_oscar]

I can't get it to upload for some reason.
Try creating a db table with "Last_Name", "First_Name", "Primary Address", "Secondary Address", "City", "State", "Zip", "Country", "Entering UND", "Response Date", and "Source" as the fields, in that order and try to upload the test data I gave you after you've run the macro.

[wallyeye]

Alrighty then, lets take you home:

Please Login or Register  to view this content.

[wallyeye]

I'm having trouble posting, here is the continuation of my last post:

post.txt

This creates/updates a named range "Enter" to define the range being transferred to Access.

The ExportToAccess procedure has strDbPath that will need to be adjusted for your database, and password if necessary. It uses TestTable, you can change that to match yours as well. Here, it deletes (DROP) the table, recreates it, and inserts records from the named range.

[echo_oscar]

So far it looks great! Where exactly do I need to enter my own information for it to work with my db?

It hits an issue when it gets to "For Each namCurr In ThisWorkbook.Names".

[wallyeye]

In the post.txt file, there was a line:

Please Login or Register  to view this content.

Change that to match the path to your database.

2nd point, what is the issue (error)? It seems to be working ok in my test sheet, and even a test sheet with no names.

And, I forgot to mention you need to set a reference to the most current version of Microsoft ActiveX Data Objects #.# Library. In the VBA IDE, go to Tools, References, scroll down to it in the list, put a check on it and click OK.

[echo_oscar]

Run-time error'-2147418113 (8000ffff)':

Automation error
Catastrophic failure

[echo_oscar]

Run-time error '-2147418113 (8000ffff)':

Automation error
Catastrophic failure

[wallyeye]

A different approach for NameExists:

Please Login or Register  to view this content.