VBA, Excel Selection Object

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • nneka
    New Member
    • Sep 2006
    • 2
    #1

    VBA, Excel Selection Object

    Hello, this gives me an Object variable not set error when I try to call it using a method for the second time, do you know what I have missed out?
    Tags: excel selection, vba selection
    • PEB
      Recognized Expert Top Contributor
      • Aug 2006
      • 1418
      #2
      Originally posted by nneka
      Hello, this gives me an Object variable not set error when I try to call it using a method for the second time, do you know what I have missed out?
      Hi there is no code?

        Comment

        • nneka
          New Member
          • Sep 2006
          • 2
          #3
          Originally posted by PEB
          Hi there is no code?
          Hello, yes this is the sub routine I call, I've highlighted the bit of code that is giving trouble:

          Sub createTempCSV(i nputPth As Variant, fileName1 As Variant, dbpath As Variant, linkName As Variant, company As Variant, ParamArray flds() As Variant)
          Dim inputFile As String, xls As Excel.Applicati on, requiredField As Boolean, xls2 As Excel.Applicati on
          Dim i As Long, j As Long, outputFile As String, db As DAO.Database, tbl As DAO.TableDef
          Dim u As Long, x As Long, destPth As String, colCount As Long

          destPth = "\\leh\eu\fid\g roups\fidshare1 \mortgage\Mortg age Platforms\SPML\ other\DWH\"

          Set e = DAO.DBEngine
          e.SystemDB = "\\leh\eu\fid\g roups\fidshare1 \mortgage\Mortg age Platforms\SPML\ Data for model\DB\MrtggS ecurity.mdw"
          Set w = DAO.CreateWorks pace("MDB automazione" & Int(Timer), "agaviani", "tittnl", dbUseJet)

          Set xls = New Excel.Applicati on
          outputFile = "\\leh\eu\fid\g roups\fidshare1 \mortgage\Mortg age Platforms\SPML\ other\DWH\Temp_ " & fileName1

          On Error Resume Next
          Kill outputFile
          On Error GoTo 0
          FileCopy inputPth & fileName1, outputFile
          xls.Workbooks.O pen fileName:=outpu tFile, ReadOnly:=False

          i = xls.Workbooks(1 ).Worksheets(1) .UsedRange.Colu mns.count
          GoBack:
          For j = 1 To i
          requiredField = False
          u = UBound(flds, 1)
          For x = 0 To u
          If InStr(flds(x), xls.Workbooks(1 ).Worksheets(1) .Range(colLette r(j) & 1)) > 0 Then
          requiredField = requiredField Or True
          End If
          Next x
          If not requiredField and not InStr("Loan Account Number", xls.Workbooks(1 ).ActiveSheet.R ange(colLetter( j) & 1)) > 0 Then
          xls.Workbooks(1 ).Worksheets(1) .Columns(colLet ter(j) & ":" & colLetter(j)).S elect
          Selection.Entir eColumn.Hidden = True
          Selection.Delet e
          i = i - 1
          GoTo GoBack
          End If
          Next j

          xls.Workbooks(1 ).Close (True)
          xls.Quit
          Set xls = Nothing

          Set db = w.OpenDatabase( dbpath, False, False)
          On Error Resume Next
          db.TableDefs.De lete linkName
          On Error GoTo 0
          Set tbl = db.CreateTableD ef(linkName)
          tbl.Connect = "Text;DATABASE= " & destPth & ";TABLE=Tem p_" & fileName1
          tbl.SourceTable Name = "Temp_" & fileName1
          db.TableDefs.Ap pend tbl
          db.TableDefs.Re fresh
          db.Close

          Set db = Nothing
          Set tbl = Nothing

          End Sub

            Comment

            • PEB
              Recognized Expert Top Contributor
              • Aug 2006
              • 1418
              #4
              Originally posted by nneka
              Hello, yes this is the sub routine I call, I've highlighted the bit of code that is giving trouble:

              Sub createTempCSV(i nputPth As Variant, fileName1 As Variant, dbpath As Variant, linkName As Variant, company As Variant, ParamArray flds() As Variant)
              Dim inputFile As String, xls As Excel.Applicati on, requiredField As Boolean, xls2 As Excel.Applicati on
              Dim i As Long, j As Long, outputFile As String, db As DAO.Database, tbl As DAO.TableDef
              Dim u As Long, x As Long, destPth As String, colCount As Long

              destPth = "\\leh\eu\fid\g roups\fidshare1 \mortgage\Mortg age Platforms\SPML\ other\DWH\"

              Set e = DAO.DBEngine
              e.SystemDB = "\\leh\eu\fid\g roups\fidshare1 \mortgage\Mortg age Platforms\SPML\ Data for model\DB\MrtggS ecurity.mdw"
              Set w = DAO.CreateWorks pace("MDB automazione" & Int(Timer), "agaviani", "tittnl", dbUseJet)

              Set xls = New Excel.Applicati on
              outputFile = "\\leh\eu\fid\g roups\fidshare1 \mortgage\Mortg age Platforms\SPML\ other\DWH\Temp_ " & fileName1

              On Error Resume Next
              Kill outputFile
              On Error GoTo 0
              FileCopy inputPth & fileName1, outputFile
              xls.Workbooks.O pen fileName:=outpu tFile, ReadOnly:=False

              i = xls.Workbooks(1 ).Worksheets(1) .UsedRange.Colu mns.count
              GoBack:
              For j = 1 To i
              requiredField = False
              u = UBound(flds, 1)
              For x = 0 To u
              If InStr(flds(x), xls.Workbooks(1 ).Worksheets(1) .Range(colLette r(j) & 1)) > 0 Then
              requiredField = requiredField Or True
              End If
              Next x
              If not requiredField and not InStr("Loan Account Number", xls.Workbooks(1 ).ActiveSheet.R ange(colLetter( j) & 1)) > 0 Then
              xls.Workbooks(1 ).Worksheets(1) .Columns(colLet ter(j) & ":" & colLetter(j)).S elect
              Selection.Entir eColumn.Hidden = True
              Selection.Delet e
              i = i - 1
              GoTo GoBack
              End If
              Next j

              xls.Workbooks(1 ).Close (True)
              xls.Quit
              Set xls = Nothing

              Set db = w.OpenDatabase( dbpath, False, False)
              On Error Resume Next
              db.TableDefs.De lete linkName
              On Error GoTo 0
              Set tbl = db.CreateTableD ef(linkName)
              tbl.Connect = "Text;DATABASE= " & destPth & ";TABLE=Tem p_" & fileName1
              tbl.SourceTable Name = "Temp_" & fileName1
              db.TableDefs.Ap pend tbl
              db.TableDefs.Re fresh
              db.Close

              Set db = Nothing
              Set tbl = Nothing

              End Sub


              Instaed delete method in:
              Selection.Delet e

              why do not try: .ClearContents

              and instaed : .Hidden = True
              .HideSelection =True

              Hope that this will work...

              :)

              Nice day

              :)

                Comment

                Previous template Next
                Working...