473,322 Members | 1,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

VBA, Excel Selection Object

2
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?
Sep 9 '06 #1
3 23694
PEB
1,418 Expert 1GB
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?
Sep 9 '06 #2
nneka
2
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(inputPth 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.Application, requiredField As Boolean, xls2 As Excel.Application
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\groups\fidshare1\mortgage\Mortga ge Platforms\SPML\other\DWH\"

Set e = DAO.DBEngine
e.SystemDB = "\\leh\eu\fid\groups\fidshare1\mortgage\Mortga ge Platforms\SPML\Data for model\DB\MrtggSecurity.mdw"
Set w = DAO.CreateWorkspace("MDB automazione" & Int(Timer), "agaviani", "tittnl", dbUseJet)

Set xls = New Excel.Application
outputFile = "\\leh\eu\fid\groups\fidshare1\mortgage\Mortga ge Platforms\SPML\other\DWH\Temp_" & fileName1

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

i = xls.Workbooks(1).Worksheets(1).UsedRange.Columns.c ount
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(colLetter(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.Range(colLetter(j) & 1)) > 0 Then
xls.Workbooks(1).Worksheets(1).Columns(colLetter(j ) & ":" & colLetter(j)).Select
Selection.EntireColumn.Hidden = True
Selection.Delete

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.Delete linkName
On Error GoTo 0
Set tbl = db.CreateTableDef(linkName)
tbl.Connect = "Text;DATABASE=" & destPth & ";TABLE=Temp_" & fileName1
tbl.SourceTableName = "Temp_" & fileName1
db.TableDefs.Append tbl
db.TableDefs.Refresh
db.Close

Set db = Nothing
Set tbl = Nothing

End Sub
Sep 10 '06 #3
PEB
1,418 Expert 1GB
Hello, yes this is the sub routine I call, I've highlighted the bit of code that is giving trouble:

Sub createTempCSV(inputPth 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.Application, requiredField As Boolean, xls2 As Excel.Application
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\groups\fidshare1\mortgage\Mortga ge Platforms\SPML\other\DWH\"

Set e = DAO.DBEngine
e.SystemDB = "\\leh\eu\fid\groups\fidshare1\mortgage\Mortga ge Platforms\SPML\Data for model\DB\MrtggSecurity.mdw"
Set w = DAO.CreateWorkspace("MDB automazione" & Int(Timer), "agaviani", "tittnl", dbUseJet)

Set xls = New Excel.Application
outputFile = "\\leh\eu\fid\groups\fidshare1\mortgage\Mortga ge Platforms\SPML\other\DWH\Temp_" & fileName1

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

i = xls.Workbooks(1).Worksheets(1).UsedRange.Columns.c ount
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(colLetter(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.Range(colLetter(j) & 1)) > 0 Then
xls.Workbooks(1).Worksheets(1).Columns(colLetter(j ) & ":" & colLetter(j)).Select
Selection.EntireColumn.Hidden = True
Selection.Delete

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.Delete linkName
On Error GoTo 0
Set tbl = db.CreateTableDef(linkName)
tbl.Connect = "Text;DATABASE=" & destPth & ";TABLE=Temp_" & fileName1
tbl.SourceTableName = "Temp_" & fileName1
db.TableDefs.Append tbl
db.TableDefs.Refresh
db.Close

Set db = Nothing
Set tbl = Nothing

End Sub


Instaed delete method in:
Selection.Delete

why do not try: .ClearContents

and instaed : .Hidden = True
.HideSelection =True

Hope that this will work...

:)

Nice day

:)
Sep 10 '06 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: ghanley | last post by:
I have searched the web all day for a lead on this. I have found how to control the Graph object mut not the embedded excel unbound object frame. I am trying to chart the data below on one...
3
by: Dent2 | last post by:
I wrote a nice C# excel routine to automate the formatting of some raw CSV data. I wrote the routine on a WinXP computer with Visual Studio .NET 2003 and Office XP installed. It compiled and ran...
4
by: msnnews.msn.com | last post by:
hi there, i've got a form that populates a datagrid, and a button that calls a function to export to an excel file. All is well with the export, no errors are returned, but the Excel instance...
2
by: Larry Jones | last post by:
I want to place information from an active Excel spreadsheet to a VB.net form. I just want to place numbers from an Excel cell in a standard VB.net text box. Does anyone have sample code to...
5
by: RJN | last post by:
Hi I'm invoking the excel object from ASP.Net application. My development machine is Windows 2000 and MS Office is installed on my m/c. I have added reference to the Excel COM object, I have...
4
by: rjn | last post by:
Hi I'm invoking the excel object from ASP.Net application. My development machine is Windows 2000 and MS Office is installed on my m/c. I have added reference to the Excel COM object, I have...
0
by: thunderscripts | last post by:
Well, I have a number of input type text fields and I created a drop down menu using the combobox component to allow the user to change the font of the text. To do this, I think I need two things:...
4
by: greenman | last post by:
Hello, Is there a chance to serialize a selection/range object (holding the selected by the user text fragment) on a webpage, so that I could deserialize it later on this site and make this...
3
by: dorlesky | last post by:
I've tried numerous times to get Excel to recognise the Excel Chart Object inserted into an Access Report. No Luck. I can get Access to start an instance of Excel, I can get Access to send the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.