Where to save some code...

[gstullo]

I have some code which reformats raw data from a SAS .csv export. I initially saved the code in personal.xlsb however I work on an office computer and a laptop and would need to have access to the code on both machines.

I need it to be available every time I open Excel because:
1) the .csv is newly created and overwrites any existing version of the SAS export
2) the code is run from more code in Access which opens excel, runs the reformat code, pauses for a few seconds to let Excel do its thing, and then imports the result to a table.

Where should I save the code? Is there a way to move the personal.xlsb to a network drive that I can access on both machines?

Thanks,

GST

 

[VoG]

You could make an Add-in http://www.fontstuff.com/vba/vbatut03.htm

 

[gstullo]

I added the add-in but it does not automatically open with the .csv which is opened by Excel... The code worked when it was in personal.xlsb but then I needed to be sure to replace the personal file with the most up-to-date version on each computer.

Thoughts?

 

[gstullo]

I have gotten the add-in to work though I do not see it in the VBA editor...

When I run my code from Access, the .csv opens in Excel and the Excel code stored in the add-in runs properly: it rearranges my data, saves the changes as a normal Excel file, and closes Excel.

The trouble is that I then get an error that I did not see when Excel ran the code from PERSONAL.xlsb:

Run-time error'440':
Method 'Run' of object '_Global' failed

When I debug, the EXCEL.Run bit is highlighted. What do I do?

'Open Excel File
Private Sub Command10_Click()
        
    Dim xlApp As Excel.Application
    
    Set xlApp = CreateObject("Excel.Application")
    
    xlApp.Visible = True
    
    xlApp.Workbooks.Open "H:\SAStoAccessConversionCode.xlam", True, False
    
    xlApp.Workbooks.Open "H:\My Documents\DataFax\Export\Data\Malvisitcrf.csv", True, False
    
    Excel.Run "ConvertSAStoAccessEpisodeTable"
    
    


'Pause to let Excel script run



'Run Access Import

 
End Sub

 

[VoG]

Possibly

xlApp.Run "ConvertSAStoAccessEpisodeTable"

 

[gstullo]

With that, the file opens but the code does not run...