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
> Delete Specific Columns In Excel Via Access Vba And Save Excel File, Access 2013    
 
   
catbeasy
post Dec 30 2019, 06:18 PM
Post#1



Posts: 491
Joined: 14-March 05



Hello All

As topic title indicates, want to edit an Excel file by deleting a specific column in one instance and then also, if i need to adjust it, how do i select 3 columns. The multi column deletions would be 3 adjacent columns, they will not be separated..

I found some stuff on the internet, but it wasn't clear how to select columns and how to save the file and when I tried to do it myself, i got errors and weird stuff happening like messages about saving sharepoint stuff(???) <don't ask!>

Also, one other funky thing that happened was that though the code I pilfered from the web appeared to have code that set the sheet/book/application variables to nothing when complete, when i tried opening the target workbook, i got a message saying it was already open do i want to open in read only etc. I had to shut my PC down to get rid of that!

Also2, don't need to have the excel spreadsheet actually open if it doesn't need to be..

So, just some simple code, I know how to grab the file name and file path into a variable, so if it makes it easier, assume that aspect is taken care of..

Thanks for any help!

Go to the top of the page
 
MadPiet
post Dec 30 2019, 06:47 PM
Post#2



Posts: 3,403
Joined: 27-February 09



Why not just record a macro to do it? Start the recording, select the 3 columns you want to delete, delete them, then stop recording, and save the macro. Done. =) It's just Access where you can't record macros.
Go to the top of the page
 
catbeasy
post Dec 30 2019, 07:10 PM
Post#3



Posts: 491
Joined: 14-March 05



Yeah, but i believe you have to define workbooks and whatnot in variables in Access though when you are writing VBA control from Access..
Go to the top of the page
 
MadPiet
post Dec 30 2019, 07:26 PM
Post#4



Posts: 3,403
Joined: 27-February 09



Create the majority of the macro in Excel, and then you need to declare an Excel application object variable and a Workbook and maybe worksheet variables so you can point at the right worksheet in the right workbook. Then you just do the delete after that.
Go to the top of the page
 
catbeasy
post Dec 31 2019, 12:11 PM
Post#5



Posts: 491
Joined: 14-March 05



ok, will give it a shot..thanks..
Go to the top of the page
 
MadPiet
post Dec 31 2019, 02:43 PM
Post#6



Posts: 3,403
Joined: 27-February 09



Basically, the only difference between calling the code from Access vs calling it from Excel is that the Excel object model (and objects) aren't available - well, until you create and instantiate an Excel application and/or Workbook object.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th January 2020 - 08:54 PM