UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export Xlsx File From Access 2010 To Excel Using Macro, Access 2010    
 
   
MikeWaring1
post Dec 29 2017, 08:28 AM
Post#1



Posts: 71
Joined: 7-November 12



Hi, I've created a new macro to export an xlsx file to my desktop. This needs to be done every day and the amount of data rows varies day by day. The only issue is that instead of overwriting the workbook on the desktop, it just adds a new sheet to that workbook.

How do I make the export overwrite the existing workbook so that only the most recent download data is in the file?

I'm using the ImportExport function, with "Spreadsheet type" as Excel Workbook. The "Range" field is blank, as the number of records changes every day, as already noted.

I've also tried going down a different route by trying to export the file as CSV, but I can't find the correct action anywhere in the macro action lists that allows this (I'm looking for export text action, or whatever it may now be named).

I have an old macro in this database that came from an old 2007 version which uses the ImportExportText. and Transfer Type "Export Delimited". I've pasted the Export Delimited bit into the new macro but it errors out with "the ImportExportSpreadsheet action has an invalid value for the "Transfer Type" argument"

So I'm stuck; I'm very inexperienced with VBA code so would rather keep it in a macro. pullhair.gif

Any ideas??
Go to the top of the page
 
MadPiet
post Dec 29 2017, 08:54 AM
Post#2



Posts: 2,412
Joined: 27-February 09



use KILL to delete the original file?
http://codevba.com/office/delete_file_kill.htm#.WkZI-9-nHIU
Go to the top of the page
 
MikeWaring1
post Dec 29 2017, 09:01 AM
Post#3



Posts: 71
Joined: 7-November 12



Hi, thanks for the rapid response. Is there a means of doing the process without using VBA?

As I mentioned, I'm very inexperienced in VBA so wouldn't even know how to write the action into a macro. dunce.gif

Thanks again
Mike
Go to the top of the page
 
GroverParkGeorge
post Dec 29 2017, 09:22 AM
Post#4


UA Admin
Posts: 32,361
Joined: 20-June 02
From: Newcastle, WA


Sometimes we have to expand our horizons.

While macros are handy little tools, and while they have a place in our tool kits, VBA is much more powerful, robust, flexible and, frankly, much better suited to tasks like this. Don't be afraid to acquire new skills as your needs grow.

--------------------
Go to the top of the page
 
MikeWaring1
post Dec 29 2017, 10:10 AM
Post#5



Posts: 71
Joined: 7-November 12



Hi, thanks for the comment.

Yes, I fully agree - I've got a book on VBA reading.gif but at the moment its just gobbledygook; as I need to get this project finished quickly I just wanted a little help on my way.

The macro does other actions as well as just exporting the file, so until I'm confortable with more VBA I'll have to confine myself to macros.

If you could guide me on how to write this code into the macro I'd be most grateful. I've seen a macro action called "Run Code" - do I create a module, then copy the code into it, then call this module from that "open Code" action?

My overriding concern is that I might do something that will have unintended consequences further down the line.... blush.gif

Kind regards
Mike
Go to the top of the page
 
GroverParkGeorge
post Dec 29 2017, 10:18 AM
Post#6


UA Admin
Posts: 32,361
Joined: 20-June 02
From: Newcastle, WA


Well, if the macro language doesn't support it, there's not a lot one can do about that, is there?

I don't see any macro action that would work to delete a file. It can be done with VBA.

Here's another, complete module, which includes a step to delete an existing file.

--------------------
Go to the top of the page
 
MikeWaring1
post Dec 29 2017, 10:48 AM
Post#7



Posts: 71
Joined: 7-November 12



Hi, thanks for the quick response again.

Yes, I understand your point, but as I've tried to say, I don't know by which method I'd call the code.

I understand the principle of creating a module and then pasting the code into it, but from then on, I'm lost, i.e. I don't know how I would then activate that code.

From your response, I presume that "Run Code" action within the macro option wouldn't work?


I've looked at that link, but again to my complete numpty VBA brain, it means nothing at all, for the same reason as before - I haven't a clue on how to use it in practice.

I'm sorry if I seem to be annoying you with what must seem to you a pathetic lack of knowledge and if I've offended you I'm sorry; I'm just trying to get the job done.

Unfortunately I have my own retail business so haven't had the time to study or practice using VBA to the extent that I feel comfortable.

Kind regards
Mike
Go to the top of the page
 
GroverParkGeorge
post Dec 29 2017, 11:08 AM
Post#8


UA Admin
Posts: 32,361
Joined: 20-June 02
From: Newcastle, WA


RUnCode would work, yes.

Create the procedure, using one of the examples provided.

Call in it a macro using the RunCode macro action.

You'll get it. Don't worry.


--------------------
Go to the top of the page
 
JonSmith
post Dec 29 2017, 11:37 AM
Post#9



Posts: 3,280
Joined: 19-October 10



QUOTE
I'm sorry if I seem to be annoying you with what must seem to you a pathetic lack of knowledge and if I've offended you I'm sorry; I'm just trying to get the job done.

Unfortunately I have my own retail business so haven't had the time to study or practice using VBA to the extent that I feel comfortable.


The thing here is, something has to give, you cannot do certain actions in macro's, they have to be VBA, you want actions that are outside the scope of macros but don't want to invest the time right now to learn it, thats oke. But then you cannot implement your new functionality as a consequence, you cannot have both.
It probably makes more sense to focus on other areas of your business if this isn't as important but until you can spend time learning or pay someone to help you then you have to shelf your enhanced functionality.

The frustrating part is likely less that you don't know VBA but when thats presented as the method since macros aren't good enough you reject it and insist you have to do it without putting in the effort required. You want to make lemonade without squeezing lemons.
Go to the top of the page
 
MikeWaring1
post Dec 29 2017, 11:51 AM
Post#10



Posts: 71
Joined: 7-November 12



Hi George, thanks for your reassurance. I created a module and added the following code:

CODE
Option Compare Database

Private Sub Kill_File()
Dim strFile  As String: strFile = "C:\Users\Mike\Desktop\Tracking Records For Ebay Upload.xlsx"
If Len(Dir$(strFile)) > 0 Then Kill strFile
End Sub


In the "Call Code" function, it asks me for the Function Name, But when I paste "Private Sub Kill_File", and then run the macro, it bring up an error box "Microsoft Access cannot find the name 'Private' you entered in the expression"

I've tried any number of alternatives, ie "Kill_File", changed the code to Public from Private, but I keep getting the same message.

Seems I can't even carry out a simple instruction....

Kind regards
Mike
Go to the top of the page
 
JonSmith
post Dec 29 2017, 12:00 PM
Post#11



Posts: 3,280
Joined: 19-October 10



So, as far as I recall, one of the dumb limitation of RunCode macros is that it only works with Functions and not Subs. This is really weird if you ask me as I don't think you can return a value from RunCode (totally not sure though) meaning it having to be a function is even weirder.

Scope wise, it should be Public, Private means it can only be called by other code within that module, there are small exceptions, for example Ribbon Callbacks can be private but it is a general rule of thumb.
Then change Sub to Function, its a bad habit to make Subs into Functions when they aren't really Functions. Another reason to totally ditch macros and go for VBA.
Go to the top of the page
 
MikeWaring1
post Dec 29 2017, 12:20 PM
Post#12



Posts: 71
Joined: 7-November 12



Hi George, I finally sussed it - I created a function which called the sub...

CODE
Public Sub KillFile()
Dim strFile  As String: strFile = "C:\Users\Mike\Desktop\Tracking Records For Ebay Upload.xlsx"
If Len(Dir$(strFile)) > 0 Then Kill strFile
End Sub

Public Function runKill()
Call KillFile
End Function


I had a bit of a wobble as the Sub KillFile was originally called Kill_File but for some reason it didn't like the underscore (I thought "_" was a neutral character? clearly not.

Anyway, thanks a million for all your help notworthy.gif - it now works fine.

I hope you have a Happy New Year wine.gif

Kindest Regards
Mike
Go to the top of the page
 
JonSmith
post Dec 30 2017, 08:40 AM
Post#13



Posts: 3,280
Joined: 19-October 10



George? I though I said it has to be made into a function? Either way that is awful VBA code.

You just need this and use RunCode to call KillFile. A function that only runs a sub is really bad.

CODE
Public Function KillFile()
Dim strFile  As String: strFile = "C:\Users\Mike\Desktop\Tracking Records For Ebay Upload.xlsx"
If Len(Dir$(strFile)) > 0 Then Kill strFile
End Sub
Go to the top of the page
 
GroverParkGeorge
post Dec 30 2017, 08:51 AM
Post#14


UA Admin
Posts: 32,361
Joined: 20-June 02
From: Newcastle, WA


Happy to hear you found a WORKING solution.

As your skills improve, you'll find that it gets easier and quicker to do the things you need to do, both with macros and VBA procedures.

I've often pointed out that the solutions we develop ourselves are much more satisfying and rewarding than those that are canned and handed to us. It represents growth, learning and achievement.

Never mind that the first attempt isn't perfect. Some of the earliest work of every developer can be cringe worthy, but each new challenge met and overcome builds on the last.

Please don't let the negativity of others interfere with your continued success.

Good luck with the rest of your project.



--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th April 2018 - 06:58 PM