I created a VB 6 application (below is the code for the two buttons that I use).
The first button works 100% correctly. The second one opens Excel, but then illegal operations' out.
Any ideas why this wouldn't work? I took the Excel (VBA) code from a macro I wrote to do the import functions.
--------------------------
----------
-
Option Explicit
Private Sub Command1_Click()
'Register DLL's
'VBRegSvr32 "C:\fpexport\#Filename.ext
#"
' Sets the Dialog Title to Open File
CommonDialog1.DialogTitle = "Open File"
' Sets the File List box to Word documents and Excel documents
CommonDialog1.Filter = "Text Files (*.dat)|*.dat"
' Set the default files type to Word Documents
CommonDialog1.FilterIndex = 1
' Sets the flags - File must exist and Hide Read only
CommonDialog1.Flags = cdlOFNFileMustExist + cdlOFNHideReadOnly
' Set dialog box so an error occurs if the dialogbox is cancelled
CommonDialog1.CancelError = True
' Enables error handling to catch cancel error
On Error Resume Next
' display the dialog box
CommonDialog1.ShowOpen
If Err Then
' This code runs if the dialog was cancelled
MsgBox "Dialog Cancelled"
Exit Sub
End If
lblFileStatus.Caption = "File Selected: " & CommonDialog1.FileName
End Sub
Private Sub Command2_Click()
Dim appXl As Excel.Application
Dim wrkFile As Workbooks
' Set object variables.
Set appXl = New Excel.Application
Set wrkFile = appXl.Workbooks
' Open a file.
On Error Resume Next
Set appXl = GetObject(, "Excel.Application")
If appXl Is Nothing Then Set appXl = New Excel.Application
' Display Excel.
appXl.Visible = True
If Err.Number <> 0 Then
MsgBox Err.Description & vbCrLf & Err.Number
End If
'Import the .DAT file to excel and bypass the 'import wizard'.
'Workbooks.OpenText instead of wrkFile.
'********************
'THIS IS WHERE THE PROBLEM BEGINS....
wrkFile.OpenText FileName:=CommonDialog1.Fi
leName, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuo
te, _
ConsecutiveDelimiter:=Fals
e, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1), Array(17, 1)), TrailingMinusNumbers:=True
If Err.Number <> 0 Then
MsgBox Err.Description & vbCrLf & Err.Number
End If
'Autofit the table
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
'Format Columns O through Q as Currency
Columns("O:Q").Select
Selection.NumberFormat = "$#,##0.00"
'Go to A1
Range("A1").Select
'Find and format the bottom items.
Cells.Find(What:="TOTAL COMMISSION", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activ
ate
ActiveCell.Offset(1, 0).Range("A1:B14").Select
Selection.Style = "Currency"
Range("A1").Select
If Err.Number <> 0 Then
MsgBox Err.Description
End If
' Sets the Dialog Title to Save File
CommonDialog1.DialogTitle = "Save File"
' Sets the File List box to Text File and All Files
CommonDialog1.Filter = "Excel Files (*.xls)|*.xls"
' Set the default files type to Text File
CommonDialog1.FilterIndex = 1
' Sets the flags - Hide Read only, prompt to overwrite, and path must exist
CommonDialog1.Flags = cdlOFNHideReadOnly + cdlOFNOverwritePrompt _
+ cdlOFNPathMustExist
' Set dialog box so an error occurs if the dialogbox is cancelled
CommonDialog1.CancelError = True
Form1.Show
CommonDialog1.ShowSave
End Sub
Private Sub Command4_Click()
End
End Sub