以前用EXCEL做的VBA代码,只能运行在EXCEL95上,现在改用EXCEL2003做,也就是有些代码在EXCEL95上好用,在2003就不能运行的
想用VB,或则C#把以前EXCEL中的VBA代码给修改修改,比如
用程序把标准模块中的全部代码全部替换为我指定的代码
分不多,请知道的兄弟指点一下,呵呵
想用VB,或则C#把以前EXCEL中的VBA代码给修改修改,比如
用程序把标准模块中的全部代码全部替换为我指定的代码
分不多,请知道的兄弟指点一下,呵呵
问题是你要改什么?
或者把模块导出后修改
通过这个模板做出来了大量的文件,手动复制粘贴需要不少时间,所以想做一个程序直接给客户转换他以前的文件,保证现在的VBA能兼容以前的
其实功能很简单,就是打开EXCEL文件,然后到VDA代码编辑区域,进入一个标准模块中
CTRL +a ,CTRL + V 就可以了,吧所有的代码直接替换就OK。只不过这个打开,进入VBA的步骤实在麻烦,啊呀/
Option Explicit
Dim xlApp As Excel.Application
Dim xlBook As Workbook
Dim fso As New FileSystemObject
Dim gExcel1995Folder As String
Dim gExcel2003Folder As String
Dim gCode2003File As String
Dim initialFlag As BooleanPrivate Sub cmdCancel_Click()
End
End SubPrivate Sub cmdChange_Click() Dim excel95FileName As String
Dim excel03FileName As String
Dim iniFileName As String
Dim strCode As String
Dim mfiles As Files
Dim mFile As File
Call LoadInformation("ファイルの切り替えが開始します。", Now())
iniFileName = App.Path & "\setting.ini"
Set xlApp = New Excel.Application
xlApp.Visible = False
xlApp.DisplayAlerts = False
If getIniSetting(iniFileName) = True Then
strCode = getCode(gCode2003File)
Set mfiles = fso.GetFolder(gExcel1995Folder).Files
For Each mFile In mfiles
If changeExcel(gExcel1995Folder + "\" + mFile.Name, strCode, gExcel2003Folder + "\" + mFile.Name) = True Then
Call LoadInformation(mFile.Name + "ファイルの切り替えがが成功します。", Now())
Else
Call LoadInformation(mFile.Name + "ファイルの切り替えが失敗します。", Now())
End If
Next
Call LoadInformation("ファイルの切り替えがしました。", Now())
Else
Call LoadInformation("初期化情報を取得に失敗しました。", Now())
End If
xlApp.Quit
Set xlApp = NothingEnd SubPrivate Sub LoadInformation(strMess As String, mdate As String)
lstInformation.AddItem ("[" & mdate & "]" & strMess)
End Sub
'初期化ファイルを読む
Private Function getIniSetting(iniFilePath As String) As Boolean
Dim intFileNum As Integer 'File NUm Dim strTemp As String
Dim equitPosition As Integer 'position of "="
Dim strKey As String 'Key
Dim strData As String 'Value
If fso.FileExists(iniFilePath) = False Then Exit Function intFileNum = FreeFile
Open iniFilePath For Input As #intFileNum 'ファイルを開ける
Do While Not EOF(intFileNum) '全部読む
Line Input #intFileNum, strTemp 'データを取得
equitPosition = InStr(1, strTemp, "=") '=の位置取得
If equitPosition <> 0 Then '=がある場合
strKey = Mid(strTemp, 1, equitPosition - 1) 'キーの所得
strData = Mid(strTemp, equitPosition + 1) 'データ
Select Case strKey '1995フォルダ
Case "EXCEL1995"
gExcel1995Folder = strData
Case "EXCEL2003" '2003フォルダ
gExcel2003Folder = strData
Case "CODEFILE" 'コード読む
gCode2003File = strData
End Select
End If
Loop
Close #intFileNum
If fso.FolderExists(gExcel1995Folder) = True And _
fso.FolderExists(gExcel2003Folder) = True And _
fso.FileExists(gCode2003File) = True Then
getIniSetting = True
End If
End Function
'コードファイルを読む
Private Function getCode(codeFilePath As String) As String
Dim intFileNum As Integer 'File NUm
Dim strCode As String 'Code string
Dim strTemp As String intFileNum = FreeFile
Open codeFilePath For Input As #intFileNum
Do While Not EOF(intFileNum)
Line Input #intFileNum, strTemp
strCode = strCode & strTemp & vbCrLf
Loop
Close #intFileNum getCode = strCode
End Function'ファイルの変更
Private Function changeExcel(excelFileName As String, strCode As String, newExcelFileName) As Boolean
On Error GoTo ExitPort
Dim iCount As Integer
Set xlBook = xlApp.Workbooks.Open(excelFileName)
If xlBook.Sheets(1).Name = "オープン時設定" And xlBook.Sheets(2).Name = "器具データ" And xlBook.Sheets(3).Name = "接続データ" And _
xlBook.Sheets(4).Name = "布線データ" And xlBook.Sheets(5).Name = "配線図" Then
With xlBook.VBProject
With .VBComponents(1).CodeModule
iCount = .CountOfLines
Call .DeleteLines(1, iCount)
.AddFromString strCode
End With
End With
xlBook.SaveAs newExcelFileName
End If
xlBook.Close (False)
changeExcel = True
Exit Function
ExitPort:
changeExcel = False
End Function需要运用EXCEL 11.0 ,和 FSO,设置EXCEL中的宏安全性(工具-》宏》信赖中的VB信赖
即可修改宏!!
楼主也是在日企呀。呵呵,多多交流。