Change certain Excel row 1 cell values

[chuckcoleman]

Hi,

I'm writing VBA code to import a spreadsheet. Row 1 has the field names in an Access table. It works fine except there are 6 columns in row 1 where I need to change the name because they either have a period in the cell or the cell is a duplicate from an earlier column. A portion of the code I'm using is:

Set objApp = CreateObject("Excel.Application")
objApp.Visible = False
Set wb = objApp.Workbooks.Open(strExcelPath, True, False)
wb.Sheets(1).Range("A:A").NumberFormat = "dd-mm-yyyy"
wb.Sheets(1).Range("B:B").NumberFormat = "@"
wb.Sheets(1).Range("C:C").NumberFormat = "@"
wb.Sheets(1).Range("D").NumberFormat = "@"
wb.Sheets(1).Range("E:E").NumberFormat = "@"
wb.Sheets(1).Range("F:F").NumberFormat = "@"
wb.Sheets(1).Range("G:G").NumberFormat = "@"
wb.Sheets(1).Range("H:H").NumberFormat = 0
wb.Sheets(1).Range("I:I").NumberFormat = "@"
wb.Sheets(1).Range("J:J").NumberFormat = "@"
wb.Sheets(1).Range("K:K").NumberFormat = 0
wb.Sheets(1).Range("L:L").NumberFormat = 0
wb.Sheets(1).Range("M:M").NumberFormat = "@"
wb.Sheets(1).Range("N:N").NumberFormat = "@"
wb.Sheets(1).Range("O:O").NumberFormat = "@"
wb.Sheets(1).Range("P").NumberFormat = "@"
wb.Sheets(1).Range("Q:Q").NumberFormat = "@"
wb.Sheets(1).Range("R:R").NumberFormat = "@"
wb.Sheets(1).Range("S:S").NumberFormat = "@"
wb.Sheets(1).Range("T:T").NumberFormat = "@"
wb.Sheets(1).Range("U:U").NumberFormat = "@"
wb.Sheets(1).Range("V:V").NumberFormat = "@"
wb.Sheets(1).Range("W:W").NumberFormat = "@"
wb.Sheets(1).Range("X:X").NumberFormat = "@"
wb.Sheets(1).Range("Y:Y").NumberFormat = "@"
wb.Sheets(1).Range("Z:Z").NumberFormat = "@"
wb.Sheets(1).Range("AA:AA").NumberFormat = "@"
wb.Save
wb.Close
Set objApp = Nothing

I believe I can use a .value = "The new cell value/name", but I'm not sure where to put it. For instance, column "C" needs to change from "Cust. ID" to "Cust ID".

Any suggestions are appreciated.

Thanks,

Chuck

 

[pbaldy]

Try this shot in the dark:

wb.Cells(3, 1) = "Cust ID"

 

[theDBguy]

Just in case, here's another shot in the dark.

wb.Sheets(1).Cells(3,1)="Cust ID"

Sent from phone...

 

[pbaldy]

I'll get out of the way.

 

[chuckcoleman]

Thank you Paul and DBG. I guess my question is since I'm using wb.Sheets(1)..... to define what the the formatting of the columns are, where do I use, wb.Sheets(1).Cells(3,1)="Cust ID"? Is that a new line beneath all of the existing wb.Sheets?

Chuck

 

[jdraw]

Chuck,
I'm not an excel person, but I'd try it on a copy of your spreadsheet. I would add it just before the wb.save line.

 

[chuckcoleman]

Thanks everyone. I added 6 lines to the code:

wb.Sheets(1).Cells(3, 1) = "Cust ID"
wb.Sheets(1).Cells(12, 1) = "Ext Sell"
wb.Sheets(1).Cells(22, 1) = "City2"
wb.Sheets(1).Cells(23, 1) = "State2"
wb.Sheets(1).Cells(24, 1) = "Country2"
wb.Sheets(1).Cells(27, 1) = "Rep ID"

I tried them before the first wb.Sheets(1) line and then at the end before the wb.save.

In all cases, it generates an error: 2391 - Field 'Cust# ID' doesn't exist in the destination table, 'TempSalesData'. in the error message, the '#' represents the '.' which is what I'm changing to "Cust ID". In the table the field is "Cust ID' and in the spreadsheet "Cust. ID" is in column 3, (C), and on row 1. So, I'm not sure what's causing the problem.

 

[theDBguy]

Hi. The code you posted manipulates the Excel SpreadSheet, which I think must be working because you didn't get any errors with it, and you can probably verify that the column headers were changed as you wanted, correct?

If so, then the error you're getting now must be in the import process, which you haven't shown to us yet. If you're using code to import the Excel file, please show us that code.

Sent from phone...

 

[chuckcoleman]

Hi DBG,

After the last line in my original posting, it has:

DoCmd.SetWarnings False
DoCmd.OpenQuery "Delete TempSalesData"
DoCmd.Close acQuery, "Delete TempSalesData"
Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel12, "TempSalesData", strExcelPath, True)

The spreadsheet that is selected to import, isn't changed because of the error; the column headings are the same. I believe that's because when the error occurs, the code stops running.

 

[chuckcoleman]

All right, Halloween is over! That means Trick or Treats is over too. In the suggested coding, wb.Sheets(1).Cells(3,1)="Cust ID", I interpeted the 3,1 to mean column 3 and row 1. It turns out they were reversed. I looked that up and reversed them and it works perfectly. It should be Cells(Row, Column).

So, DBG, to the rescue again. Thanks.

 

[theDBguy]

All right, Halloween is over! That means Trick or Treats is over too. In the suggested coding, wb.Sheets(1).Cells(3,1)="Cust ID", I interpeted the 3,1 to mean column 3 and row 1. It turns out they were reversed. I looked that up and reversed them and it works perfectly. It should be Cells(Row, Column).

So, DBG, to the rescue again. Thanks.

Hi. Glad to hear you got it sorted out. Good luck with your project.