Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel export to Access - Run-time error 1004
I have Office 2003 and would like to export cell contents to an Access table
I've created using an Excel worksheet button. The code below works when there are only 14 cells but bigger than that I get "method range of object worksheet failed. Run-time error 1004." arrrg. Private Sub CommandButton5_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim MyCn As ADODB.Connection Dim SQLStr As String Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= C:\temp\QCRData FY2010.mdb" ' Replace actual Access file path here ' Note: If database has userID and password, need to specify them here also ' by appending "; UID=MyUserID; PWD=MyPassword" ' - if trouble accessing the file do a net search for help on Connection ' Strings 'works okay - SQLStr = "INSERT INTO [tblPage1] ([ReviewNo])Values('" & Range("A45").Value & "')" 'works okay - SQLStr = "INSERT INTO [tblPage1] ([ReviewNo],[CaseNo],[StateCode])Values('" & Range("A45").Value & "','" & _ 'Range("B45").Value & "','" & Range("C45").Value & "')" SQLStr = "INSERT INTO [tblPage1] Values( '" & _ Range("A46").Value & "','" & Range("B46").Value & "','" & Range("C46").Value & "','" & Range("D46").Value & "','" & Range("E46").Value & "','" & _ Range("F46").Value & "','" & Range("G46").Value & "','" & Range("H46").Value & "','" & Range("I46").Value & "','" & Range("J46").Value & "','" & _ Range("K46").Value & "','" & Range("L46").Value & "','" & Range("M46").Value & "','" & Range("N46").Value & "','" & Range("046").Value & "','" & Range("P46").Value & "')" 'I broke it off here to see if it would work but need contents from all these cells. ','" & Range("Q45").Value & "','" & Range("R45").Value & "','" & Range("S45").Value & "','" & Range("T45").Value & "','" & _ Range("U45").Value & "','" & Range("V45").Value & "','" & Range("W45").Value & "','" & Range("X45").Value & "','" & Range("Y45").Value & "','" & Range("Z45").Value & "','" & _ Range("AA45").Value & "','" & Range("AB45").Value & "','" & Range("AC45").Value & "','" & Range("AD45").Value & "','" & Range("AE45").Value & "','" & _ Range("AF45").Value & "','" & Range("AG45").Value & "','" & Range("AH45").Value & "','" & Range("AI45").Value & "','" & Range("AJ45").Value & "','" & _ Range("AK45").Value & "','" & Range("AL45").Value & "','" & Range("AM45").Value & "','" & Range("AN45").Value & "','" & Range("A045").Value & "','" & _ Range("AP45").Value & "','" & Range("AQ45").Value & "','" & Range("AR45").Value & "','" & Range("AS45").Value & "','" & Range("AT45").Value & "','" & _ Range("AU45").Value & "','" & Range("AV45").Value & "','" & Range("AW45").Value & "','" & Range("AX45").Value & "','" & Range("AY45").Value & "','" & Range("AZ45").Value & "','" & _ Range("BA45").Value & "','" & Range("BB45").Value & "','" & Range("BC45").Value & "','" & Range("BD45").Value & "','" & Range("BE45").Value & "','" & _ Range("BF45").Value & "','" & Range("BG45").Value & "','" & Range("BH45").Value & "','" & Range("BI45").Value & "','" & Range("BJ45").Value & "','" & _ Range("BK45").Value & "','" & Range("BL45").Value & "','" & Range("BM45").Value & "','" & Range("BN45").Value & "','" & Range("B045").Value & "','" & _ Range("BP45").Value & "','" & Range("BQ45").Value & "','" & Range("BR45").Value & "','" & Range("BS45").Value & "','" & Range("BT45").Value & "','" & _ Range("BU45").Value & "','" & Range("BV45").Value & "','" & Range("BW45").Value & "','" & Range("BX45").Value & "','" & Range("BY45").Value & "','" & Range("BZ45").Value & "','" & _ Range("CA45").Value & "','" & Range("CB45").Value & "','" & Range("CC45").Value & "','" & Range("CD45").Value & "','" & Range("CE45").Value & "','" & _ Range("CF45").Value & "','" & Range("CG45").Value & "','" & Range("CH45").Value & "','" & Range("CI45").Value & "','" & Range("CJ45").Value & "','" & _ Range("CK45").Value & "','" & Range("CL45").Value & "','" & Range("CM45").Value & "','" & Range("CN45").Value & "','" & Range("C045").Value & "','" & _ Range("CP45").Value & "','" & Range("CQ45").Value & "')" ' NOTE: The above assumes all fields are TEXT data type, that is why ' the "'"s; might have trouble with other data types unless you match ' the format expected by the database. The order I give the values in ' corresponds to their resulting position in the database fields. MyCn.Execute (SQLStr) 'MyCn.Execute SQLStr MyCn.Close Set MyCn = Nothing MsgBox "Successfully updated the table!", vbInformation End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel export to Access - Run-time error 1004
You may want to try something like this to prepare your query
Sub Test() Dim myString As String Dim myRow As Long Dim myCol As Long Dim myCell As Excel.Range Dim aWS As Excel.Worksheet Dim myVal As Long Set aWS = ActiveSheet myString = "" 'gets data from row 45 column A to CQ (I believe) For myRow = 45 To 45 For myCol = 1 To 95 Set myCell = aWS.Cells(myRow, myCol) debug.print myrow,mycol, mycell.address If myString = "" Then myString = myCell.Value & "," Else myString = myString & myCell.Value & "," End If Next myCol Next myRow Debug.Print myString 'Replace last comma myVal = InStrRev(myString, ",") myString = Left(myString, myVal - 1) Debug.Print myString End Sub -- HTH, Barb Reinhardt "Ralph" wrote: I have Office 2003 and would like to export cell contents to an Access table I've created using an Excel worksheet button. The code below works when there are only 14 cells but bigger than that I get "method range of object worksheet failed. Run-time error 1004." arrrg. Private Sub CommandButton5_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim MyCn As ADODB.Connection Dim SQLStr As String Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= C:\temp\QCRData FY2010.mdb" ' Replace actual Access file path here ' Note: If database has userID and password, need to specify them here also ' by appending "; UID=MyUserID; PWD=MyPassword" ' - if trouble accessing the file do a net search for help on Connection ' Strings 'works okay - SQLStr = "INSERT INTO [tblPage1] ([ReviewNo])Values('" & Range("A45").Value & "')" 'works okay - SQLStr = "INSERT INTO [tblPage1] ([ReviewNo],[CaseNo],[StateCode])Values('" & Range("A45").Value & "','" & _ 'Range("B45").Value & "','" & Range("C45").Value & "')" SQLStr = "INSERT INTO [tblPage1] Values( '" & _ Range("A46").Value & "','" & Range("B46").Value & "','" & Range("C46").Value & "','" & Range("D46").Value & "','" & Range("E46").Value & "','" & _ Range("F46").Value & "','" & Range("G46").Value & "','" & Range("H46").Value & "','" & Range("I46").Value & "','" & Range("J46").Value & "','" & _ Range("K46").Value & "','" & Range("L46").Value & "','" & Range("M46").Value & "','" & Range("N46").Value & "','" & Range("046").Value & "','" & Range("P46").Value & "')" 'I broke it off here to see if it would work but need contents from all these cells. ','" & Range("Q45").Value & "','" & Range("R45").Value & "','" & Range("S45").Value & "','" & Range("T45").Value & "','" & _ Range("U45").Value & "','" & Range("V45").Value & "','" & Range("W45").Value & "','" & Range("X45").Value & "','" & Range("Y45").Value & "','" & Range("Z45").Value & "','" & _ Range("AA45").Value & "','" & Range("AB45").Value & "','" & Range("AC45").Value & "','" & Range("AD45").Value & "','" & Range("AE45").Value & "','" & _ Range("AF45").Value & "','" & Range("AG45").Value & "','" & Range("AH45").Value & "','" & Range("AI45").Value & "','" & Range("AJ45").Value & "','" & _ Range("AK45").Value & "','" & Range("AL45").Value & "','" & Range("AM45").Value & "','" & Range("AN45").Value & "','" & Range("A045").Value & "','" & _ Range("AP45").Value & "','" & Range("AQ45").Value & "','" & Range("AR45").Value & "','" & Range("AS45").Value & "','" & Range("AT45").Value & "','" & _ Range("AU45").Value & "','" & Range("AV45").Value & "','" & Range("AW45").Value & "','" & Range("AX45").Value & "','" & Range("AY45").Value & "','" & Range("AZ45").Value & "','" & _ Range("BA45").Value & "','" & Range("BB45").Value & "','" & Range("BC45").Value & "','" & Range("BD45").Value & "','" & Range("BE45").Value & "','" & _ Range("BF45").Value & "','" & Range("BG45").Value & "','" & Range("BH45").Value & "','" & Range("BI45").Value & "','" & Range("BJ45").Value & "','" & _ Range("BK45").Value & "','" & Range("BL45").Value & "','" & Range("BM45").Value & "','" & Range("BN45").Value & "','" & Range("B045").Value & "','" & _ Range("BP45").Value & "','" & Range("BQ45").Value & "','" & Range("BR45").Value & "','" & Range("BS45").Value & "','" & Range("BT45").Value & "','" & _ Range("BU45").Value & "','" & Range("BV45").Value & "','" & Range("BW45").Value & "','" & Range("BX45").Value & "','" & Range("BY45").Value & "','" & Range("BZ45").Value & "','" & _ Range("CA45").Value & "','" & Range("CB45").Value & "','" & Range("CC45").Value & "','" & Range("CD45").Value & "','" & Range("CE45").Value & "','" & _ Range("CF45").Value & "','" & Range("CG45").Value & "','" & Range("CH45").Value & "','" & Range("CI45").Value & "','" & Range("CJ45").Value & "','" & _ Range("CK45").Value & "','" & Range("CL45").Value & "','" & Range("CM45").Value & "','" & Range("CN45").Value & "','" & Range("C045").Value & "','" & _ Range("CP45").Value & "','" & Range("CQ45").Value & "')" ' NOTE: The above assumes all fields are TEXT data type, that is why ' the "'"s; might have trouble with other data types unless you match ' the format expected by the database. The order I give the values in ' corresponds to their resulting position in the database fields. MyCn.Execute (SQLStr) 'MyCn.Execute SQLStr MyCn.Close Set MyCn = Nothing MsgBox "Successfully updated the table!", vbInformation End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel export to Access - Run-time error 1004
Hi Ralph,
Because it worked for 14 cells I decided to test your 15th cell and your posted Range("046").Value is actually zero46 not the alpha O46. Tested by copying and pasting 046 as text into a worksheet and using =CODE(LEFT(A1,1)) and it returns 48. -- Regards, OssieMac "Ralph" wrote: I have Office 2003 and would like to export cell contents to an Access table I've created using an Excel worksheet button. The code below works when there are only 14 cells but bigger than that I get "method range of object worksheet failed. Run-time error 1004." arrrg. Private Sub CommandButton5_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim MyCn As ADODB.Connection Dim SQLStr As String Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= C:\temp\QCRData FY2010.mdb" ' Replace actual Access file path here ' Note: If database has userID and password, need to specify them here also ' by appending "; UID=MyUserID; PWD=MyPassword" ' - if trouble accessing the file do a net search for help on Connection ' Strings 'works okay - SQLStr = "INSERT INTO [tblPage1] ([ReviewNo])Values('" & Range("A45").Value & "')" 'works okay - SQLStr = "INSERT INTO [tblPage1] ([ReviewNo],[CaseNo],[StateCode])Values('" & Range("A45").Value & "','" & _ 'Range("B45").Value & "','" & Range("C45").Value & "')" SQLStr = "INSERT INTO [tblPage1] Values( '" & _ Range("A46").Value & "','" & Range("B46").Value & "','" & Range("C46").Value & "','" & Range("D46").Value & "','" & Range("E46").Value & "','" & _ Range("F46").Value & "','" & Range("G46").Value & "','" & Range("H46").Value & "','" & Range("I46").Value & "','" & Range("J46").Value & "','" & _ Range("K46").Value & "','" & Range("L46").Value & "','" & Range("M46").Value & "','" & Range("N46").Value & "','" & Range("046").Value & "','" & Range("P46").Value & "')" 'I broke it off here to see if it would work but need contents from all these cells. ','" & Range("Q45").Value & "','" & Range("R45").Value & "','" & Range("S45").Value & "','" & Range("T45").Value & "','" & _ Range("U45").Value & "','" & Range("V45").Value & "','" & Range("W45").Value & "','" & Range("X45").Value & "','" & Range("Y45").Value & "','" & Range("Z45").Value & "','" & _ Range("AA45").Value & "','" & Range("AB45").Value & "','" & Range("AC45").Value & "','" & Range("AD45").Value & "','" & Range("AE45").Value & "','" & _ Range("AF45").Value & "','" & Range("AG45").Value & "','" & Range("AH45").Value & "','" & Range("AI45").Value & "','" & Range("AJ45").Value & "','" & _ Range("AK45").Value & "','" & Range("AL45").Value & "','" & Range("AM45").Value & "','" & Range("AN45").Value & "','" & Range("A045").Value & "','" & _ Range("AP45").Value & "','" & Range("AQ45").Value & "','" & Range("AR45").Value & "','" & Range("AS45").Value & "','" & Range("AT45").Value & "','" & _ Range("AU45").Value & "','" & Range("AV45").Value & "','" & Range("AW45").Value & "','" & Range("AX45").Value & "','" & Range("AY45").Value & "','" & Range("AZ45").Value & "','" & _ Range("BA45").Value & "','" & Range("BB45").Value & "','" & Range("BC45").Value & "','" & Range("BD45").Value & "','" & Range("BE45").Value & "','" & _ Range("BF45").Value & "','" & Range("BG45").Value & "','" & Range("BH45").Value & "','" & Range("BI45").Value & "','" & Range("BJ45").Value & "','" & _ Range("BK45").Value & "','" & Range("BL45").Value & "','" & Range("BM45").Value & "','" & Range("BN45").Value & "','" & Range("B045").Value & "','" & _ Range("BP45").Value & "','" & Range("BQ45").Value & "','" & Range("BR45").Value & "','" & Range("BS45").Value & "','" & Range("BT45").Value & "','" & _ Range("BU45").Value & "','" & Range("BV45").Value & "','" & Range("BW45").Value & "','" & Range("BX45").Value & "','" & Range("BY45").Value & "','" & Range("BZ45").Value & "','" & _ Range("CA45").Value & "','" & Range("CB45").Value & "','" & Range("CC45").Value & "','" & Range("CD45").Value & "','" & Range("CE45").Value & "','" & _ Range("CF45").Value & "','" & Range("CG45").Value & "','" & Range("CH45").Value & "','" & Range("CI45").Value & "','" & Range("CJ45").Value & "','" & _ Range("CK45").Value & "','" & Range("CL45").Value & "','" & Range("CM45").Value & "','" & Range("CN45").Value & "','" & Range("C045").Value & "','" & _ Range("CP45").Value & "','" & Range("CQ45").Value & "')" ' NOTE: The above assumes all fields are TEXT data type, that is why ' the "'"s; might have trouble with other data types unless you match ' the format expected by the database. The order I give the values in ' corresponds to their resulting position in the database fields. MyCn.Execute (SQLStr) 'MyCn.Execute SQLStr MyCn.Close Set MyCn = Nothing MsgBox "Successfully updated the table!", vbInformation End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel export to Access - Run-time error 1004
Hi again Ralph,
I decided to do a search for where you have zeros instead of alpha O and found quite a few. In your VBA editor, select the section of code and use the Edit - Find and enter a zero and you will find them all. Note that I think that Barbara's answer is the better solution. However, personally I would modify the following so that I did not have to work out the column number of CQ. It then becomes self documenting for the column Id and easy to modify if required. lastCol = Columns("CQ").Column 'Then use in following For myCol = 1 To lastCol -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel export to Access - Run-time error 1004
thank you, I am not sure how this works to replace the detail in - SQLStr =
"INSERT INTO [tblPage1] ([ReviewNo],[CaseNo],[StateCode])Values('" & Range("A45").Value & "','" Range("B45").Value & "','" & Range("C45").Value & "')" Does the code you suggest get inserted ahead of this part? I was able to get things to work after making the fields in the Access table all text fields. Some of the Excel cells are text, others are dates and numbers. Will your code handle that? I am thinking I can reformate as necessary in Access. thanks again "Barb Reinhardt" wrote: You may want to try something like this to prepare your query Sub Test() Dim myString As String Dim myRow As Long Dim myCol As Long Dim myCell As Excel.Range Dim aWS As Excel.Worksheet Dim myVal As Long Set aWS = ActiveSheet myString = "" 'gets data from row 45 column A to CQ (I believe) For myRow = 45 To 45 For myCol = 1 To 95 Set myCell = aWS.Cells(myRow, myCol) debug.print myrow,mycol, mycell.address If myString = "" Then myString = myCell.Value & "," Else myString = myString & myCell.Value & "," End If Next myCol Next myRow Debug.Print myString 'Replace last comma myVal = InStrRev(myString, ",") myString = Left(myString, myVal - 1) Debug.Print myString End Sub -- HTH, Barb Reinhardt "Ralph" wrote: I have Office 2003 and would like to export cell contents to an Access table I've created using an Excel worksheet button. The code below works when there are only 14 cells but bigger than that I get "method range of object worksheet failed. Run-time error 1004." arrrg. Private Sub CommandButton5_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim MyCn As ADODB.Connection Dim SQLStr As String Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= C:\temp\QCRData FY2010.mdb" ' Replace actual Access file path here ' Note: If database has userID and password, need to specify them here also ' by appending "; UID=MyUserID; PWD=MyPassword" ' - if trouble accessing the file do a net search for help on Connection ' Strings 'works okay - SQLStr = "INSERT INTO [tblPage1] ([ReviewNo])Values('" & Range("A45").Value & "')" 'works okay - SQLStr = "INSERT INTO [tblPage1] ([ReviewNo],[CaseNo],[StateCode])Values('" & Range("A45").Value & "','" & _ 'Range("B45").Value & "','" & Range("C45").Value & "')" SQLStr = "INSERT INTO [tblPage1] Values( '" & _ Range("A46").Value & "','" & Range("B46").Value & "','" & Range("C46").Value & "','" & Range("D46").Value & "','" & Range("E46").Value & "','" & _ Range("F46").Value & "','" & Range("G46").Value & "','" & Range("H46").Value & "','" & Range("I46").Value & "','" & Range("J46").Value & "','" & _ Range("K46").Value & "','" & Range("L46").Value & "','" & Range("M46").Value & "','" & Range("N46").Value & "','" & Range("046").Value & "','" & Range("P46").Value & "')" 'I broke it off here to see if it would work but need contents from all these cells. ','" & Range("Q45").Value & "','" & Range("R45").Value & "','" & Range("S45").Value & "','" & Range("T45").Value & "','" & _ Range("U45").Value & "','" & Range("V45").Value & "','" & Range("W45").Value & "','" & Range("X45").Value & "','" & Range("Y45").Value & "','" & Range("Z45").Value & "','" & _ Range("AA45").Value & "','" & Range("AB45").Value & "','" & Range("AC45").Value & "','" & Range("AD45").Value & "','" & Range("AE45").Value & "','" & _ Range("AF45").Value & "','" & Range("AG45").Value & "','" & Range("AH45").Value & "','" & Range("AI45").Value & "','" & Range("AJ45").Value & "','" & _ Range("AK45").Value & "','" & Range("AL45").Value & "','" & Range("AM45").Value & "','" & Range("AN45").Value & "','" & Range("A045").Value & "','" & _ Range("AP45").Value & "','" & Range("AQ45").Value & "','" & Range("AR45").Value & "','" & Range("AS45").Value & "','" & Range("AT45").Value & "','" & _ Range("AU45").Value & "','" & Range("AV45").Value & "','" & Range("AW45").Value & "','" & Range("AX45").Value & "','" & Range("AY45").Value & "','" & Range("AZ45").Value & "','" & _ Range("BA45").Value & "','" & Range("BB45").Value & "','" & Range("BC45").Value & "','" & Range("BD45").Value & "','" & Range("BE45").Value & "','" & _ Range("BF45").Value & "','" & Range("BG45").Value & "','" & Range("BH45").Value & "','" & Range("BI45").Value & "','" & Range("BJ45").Value & "','" & _ Range("BK45").Value & "','" & Range("BL45").Value & "','" & Range("BM45").Value & "','" & Range("BN45").Value & "','" & Range("B045").Value & "','" & _ Range("BP45").Value & "','" & Range("BQ45").Value & "','" & Range("BR45").Value & "','" & Range("BS45").Value & "','" & Range("BT45").Value & "','" & _ Range("BU45").Value & "','" & Range("BV45").Value & "','" & Range("BW45").Value & "','" & Range("BX45").Value & "','" & Range("BY45").Value & "','" & Range("BZ45").Value & "','" & _ Range("CA45").Value & "','" & Range("CB45").Value & "','" & Range("CC45").Value & "','" & Range("CD45").Value & "','" & Range("CE45").Value & "','" & _ Range("CF45").Value & "','" & Range("CG45").Value & "','" & Range("CH45").Value & "','" & Range("CI45").Value & "','" & Range("CJ45").Value & "','" & _ Range("CK45").Value & "','" & Range("CL45").Value & "','" & Range("CM45").Value & "','" & Range("CN45").Value & "','" & Range("C045").Value & "','" & _ Range("CP45").Value & "','" & Range("CQ45").Value & "')" ' NOTE: The above assumes all fields are TEXT data type, that is why ' the "'"s; might have trouble with other data types unless you match ' the format expected by the database. The order I give the values in ' corresponds to their resulting position in the database fields. MyCn.Execute (SQLStr) 'MyCn.Execute SQLStr MyCn.Close Set MyCn = Nothing MsgBox "Successfully updated the table!", vbInformation End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel export to Access - Run-time error 1004
thanks, I've written Barbara with a few questions. I was able to get my
method to work after fixing the "O"s and a few other changes to the cell data. I did have to change everything to text because I don't the skills to handle dates and numbers which some of the fields are. Is that a relatively simple thing? I'll be transmitting the data once collected in the Access table and need to conform to predefined field values. I can formate the data once in Access but it might be easier to keep as much of it as I can when uploading it from Excel to Access. Thanks again, Ralph "OssieMac" wrote: Hi again Ralph, I decided to do a search for where you have zeros instead of alpha O and found quite a few. In your VBA editor, select the section of code and use the Edit - Find and enter a zero and you will find them all. Note that I think that Barbara's answer is the better solution. However, personally I would modify the following so that I did not have to work out the column number of CQ. It then becomes self documenting for the column Id and easy to modify if required. lastCol = Columns("CQ").Column 'Then use in following For myCol = 1 To lastCol -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel export to Access - Run-time error 1004
Hi again Ralph,
As you are having problems with the various cell contents (dates etc) I would consider copying all of the data to a separate worksheet and then simply import that to an Access table. All of the numberformats should then work fine. -- Regards, OssieMac "Ralph" wrote: thanks, I've written Barbara with a few questions. I was able to get my method to work after fixing the "O"s and a few other changes to the cell data. I did have to change everything to text because I don't the skills to handle dates and numbers which some of the fields are. Is that a relatively simple thing? I'll be transmitting the data once collected in the Access table and need to conform to predefined field values. I can formate the data once in Access but it might be easier to keep as much of it as I can when uploading it from Excel to Access. Thanks again, Ralph "OssieMac" wrote: Hi again Ralph, I decided to do a search for where you have zeros instead of alpha O and found quite a few. In your VBA editor, select the section of code and use the Edit - Find and enter a zero and you will find them all. Note that I think that Barbara's answer is the better solution. However, personally I would modify the following so that I did not have to work out the column number of CQ. It then becomes self documenting for the column Id and easy to modify if required. lastCol = Columns("CQ").Column 'Then use in following For myCol = 1 To lastCol -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export a chart in a GIF file. Run-time error '1004': Application-defined or object-defined error; | Excel Programming | |||
Run-time error '1004'; Method 'Export' of object'_Chart' failed | Excel Programming | |||
ASP & EXCEL2003 - RUN-TIME ERROR 1004 in Export method | Excel Programming | |||
excel chart export error runtime 1004 | Charts and Charting in Excel | |||
excel chart export error runtime 1004 | Excel Programming |