Macro/VBA Code Open CSV file, save as xls, run excel macro

[elliotth123]

I need to create a module that will open a CSV file from a specific location
Then save it as a xls file over wirtting file in there
Then running a Macro within excel to format the xls file.
Then save it.

Can this be done?

I need the code for it.
Thanks!

 

[elliotth123]

Anyone?

Can anyone help with this?

 

[elliotth123]

Help!

Here is what I have come up with Of course this doesn't work though:


Public Sub Command0_Click()

Shell "excel.exe c:\resourcelibrary\import.csv ", vbNormalFocus ' Opens Specified file


'Format database

'
' Format Macro
' Macro recorded 11/7/2004 by Elliott
'

'
ActiveCell.FormulaR1C1 = "Number"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Title"
Range("C1").Select
ActiveCell.FormulaR1C1 = "ISBN"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Location"
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E1").Select
ActiveCell.FormulaR1C1 = "Level"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Copies"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Author"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Subject"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Summary"
Range("J1").Select
ActiveCell.FormulaR1C1 = "OnLoanTo"
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Range("K1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Publisher"
Columns("B:B").Select
Selection.ColumnWidth = 25.29
Selection.ColumnWidth = 34.57
Columns("C:C").ColumnWidth = 17.14
Columns("F:F").ColumnWidth = 6.43
Columns("G:G").ColumnWidth = 18
Columns("H:H").ColumnWidth = 19.29
ActiveWindow.ScrollColumn = 3
Columns("I:I").ColumnWidth = 18.14
Columns("J:J").ColumnWidth = 18.29
ChDir "C:\resourcelibrary"
ActiveWorkbook.SaveAs FileName:="C:\resourcelibrary\Import.xls", FileFormat _
:=xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

DoCmd.Quit 'Close Database


End Sub

 

[fpendino]

Check out this link. I posted a sample database that shows how to interact Excel and Access. You have to declare Excel as an object. You can't just open/Shell the Excel program.

http://www.access-programmers.co.uk/forums/showthread.php?t=63291

 

[Simplycongy]

You'd probably be better off doing as follows.

1) Using ACCESS, export the CSV file to a set location (e,g, C:\TEXTFILE) ...
2) ...then use the Shell command to open up a .XLS file that has an Autpen Macro that (a) Opens up your saved CSV file and (b) formats it and saves it as a new .xls or text file of your choosing.
3) The Excel file can then return you to Access (using the appActivate command) before shutting itself down.

I've used this methodology to go from excel, to acces and back to excel in the past, so it works nicely. If you ever want to go a few steps further, look into send keys to hit Access buttons from Excel Macros...

 

[fpendino]

I would say that Simply's idea is good, but there are some down sides.

With using Access to 'Shell' Excel, you have less control over Excel programmatically from Access.

With the Send Keys, I try to stay away from that, because when I declare Excel as an Object, I can let my programs run and switch to another application without worrying about which application I'm sending the 'Key Presses' to. Even if you write the Macro in Excel, you can execute the macro from Access and your next line of code won't start until that macro has completed.

I just think it depends what works for you and how you want to go about it. Just keep in mind, that anything you can do in Excel, via Macro or whatever, you can program it into Access, while having more control of whats happening, versus 'Shelling' Access and using 'Send Keys'.

 

[Simplycongy]

I'm flattered, thanx!!!

Have only got into using Access VBA the last week, from a thoroughly Excel based backgorund, so am still likely to fall back to my old trix!

But I'm busy reading all I can and soaking all your guys wisdom in... your Interaction example is v interestingino!!!

 

[fpendino]

Simply,
Don't worry I've been doing this for about 4+ years off and on, and I still learn new things everyday. I only have 50 posts on here now since 2001, because I can usually find what I need by searching for it.

Over the past year, my need to export to Excel has increased a lot, so luckily I was able to find some good info on it. Like I said in the other post, Just remember that anything you can do in Excel, using a Macro or whatever, you can program it in Access. It's so nice!! And has made my job a lot easier.

Good Luck.