Grid Line Issue in Excel Sheet


The Problem is that I have so many sheets in One workbook . I want to set for only one Sheet the gridline .but in Other Sheets Grids needs to be Come Automatically .So let me know the idea .how should I achieve this specific task using Macro ???.any help would be highly appreciated .Kindly see the attachment also .if you will Print Preview to Mamasita Calenderata sheet or Mamasita Tamarind mix . you will see the grid at the time of Print Preview but if you will see the Print Preview to tomato Sauce Sheet .you will not get the Grid in full page .So let me know please !!! .


Sponsored Links:



I need a report to be created as the same in the excel sheet

how to do this




Hi,
On an application i am working on, i am having problem importing an excel sheet to access.
The problem goes as follows:

If the excel sheet contains any blank fields like shown below

dkjhf kjshdfkjh kjhdkjhfjkd hkjhdjkfhkjh kjhskjfghlk
ff f
df df s df

then the sheet imported is access is completely blank.

Pls help if you have any idea how to resolve this problem...

The specific line of code i am using to do so goes as follows

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Rates ", Directory + "Sapp.xls", False




Hello Everyone

I am quite new to the world of VB coding in Access. I have written down a code to Import Excel sheet into Access07.

The table has been made accordingy and it works fine. it imports the worksheet "closing12" to tables 'Fixed_Asset_Listing' and 'NBV_Fixed_Asset_Listing'.

I am having problems writing code for deleting column name "sortfield" from the excel sheet. Here is a twist. the reason I want to do so is because the excel sheet generated from the software can have as many sortfields as user want and will be names as sortfield1, sortfield2, sortfield3 and so on.... it is garbage data and I want to delete all the columns with name sortfield1, sortfield2, sortfield3 and so on.... before i do the import process.

While Mid(xlSheet.Range("A2").Value, 1, 9) = "Sortfield"
xlSheet.Range("A2").EntireColumn.Delete
Wend

Please help me with correcting the added code. Ur help will be really appreicatated.

Whole code is written down.

Thanks and Regards,
Prashant Kumar.





Private Sub Import_Fixed_Asset_Listing_Click()
Dim filename As String
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
sFilter = "Excel Files (*.xls)" & Chr(0) & "*.XLS" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:"
OpenFile.lpstrTitle = "Import Fixed Asset Listing"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "No File Selected. Abort!"
Else
filename = Trim(OpenFile.lpstrFile)
DoCmd.Close acTable, "Fixed_Asset_Listing", acSaveYes
Set xls = CreateObject("Excel.Application")
Set xlWB = xls.workbooks.Open(filename)
xls.Visible = True
Set xlSheet = xlWB.Worksheets("Closing12")
While Mid(xlSheet.Range("A2").Value, 1, 9) = "Sortfield"
xlSheet.Range("A2").EntireColumn.Delete
Wend

xlWB.Save

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"Fixed_Asset_Listing", filename, True, "Closing12!"


DoCmd.OpenQuery "Delete_Querry(data_of_table_NVB_FAL)"
DoCmd.Close acTable, "NBV_Fixed_Asset_Listing", acSaveYes
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"NBV_Fixed_Asset_Listing", filename, True, "Closing12!"

xls.Quit
Set xlWB = Nothing
Set xls = Nothing

DoCmd.Save
End If
'Transfers file using acImportDelim profile previously set through manually importing into table
'DoCmd.TransferSpreadsheet(acImport,acSpreadsheetT ypeExcel8,"temp",filename,0,"A2:v67",1) =
End Sub




I have an Excel sheet that I'd like to bring into an Access Form -- populate the Excel cells with data from textboxes on the Access Form and print it. I tried copy/pasting various things in, but it's pasting even text from a cell as an Excel object (let alone joined cells and images). Where can I find more information about this?

I found http://support.microsoft.com/?kbid=210288
But, when I put that code into the form for saving text from a text box to an Excel cell, it tells me that the word "Set" is a "Compile error: Invalid outside procedure."

Also, how do I refer to a joined cell? I've found that I can copy from a joined cell, paste into a host cell and select "Paste Link". This puts an absolute reference to the joined cell in the host cell (=$A$7, for instance) and when I change the joined cell the host cell is changed. Pasting =$A$7 seems to merely put the face value of "=$A$7 into the host cell.




Dear All,

I need small help from you. In Excel sheet i do have about 1000 records.In that 1000 records, i need to categorize the one particular cell value.

For Ex,

A1 cell value "Password Reset" then b1 cell would be "Related to PWD"
B2 cell value "Access to system:" then b1 cell would be "Related to access".

i do not know the condition for the cell "contains"

If a a1 to a100 contains the word "Password" then automatically it shud fill in b1 to b100 "Password Reset"

tired of filling 1000 records.

if anyone helps on this would be appreciated.

Regards,
Rathi.




Please help! How do you set a checkbox in Excel sheet to True from Access?
Thanks.




Hi All,

In excel sheet i have a chart and table for which I am setting data range property at design time.based on the data i want to set it at run time.

select in chart menu---> Source data --> Data range property we are setting at design time. How to set this property at runtime. Please refer the attachment.

could you please help me.

Thanks & Regards in advance,
Areddy




I am using some code that I have changed up to get to the last row of data on a excel sheet. I can get to the last cell with no problem but I am having an issue going a row beyond it to the next blank row. When I run the following I get a - Run-time error '424: Object required on the intNewRow line. Any ideas on what may be causing this? Thanks for the help!!

Public Function test123()

Const xlCellTypeLastCell = 11

Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open("C:Documents and SettingsTest.xlsx")
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Test123")
ApXL.Sheets("Test123").Select

Set objRange = xlWSh.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate

intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = "A" & intNewRow

End Function




Does anybody know how to select all the blank lines in an Excel spreadsheet that come after the data and delete them? This may seem like a stupid question (probably is) but here's the story. I'm importing sheet2 of the spreadsheet into Access, deleting certain rows and then exporting back to sheet2 in the spreadsheet, all works fine except when I try to run a DTS package in SQL Server, I get an error. But, if I open the spreadsheet manually and mark up all rows beneath the data and delete them (even though they're blank), the DTS package runs fine

Very grateful for any help, it's driving me nuts!! Below is the code I'm currently using...

	Code:
	Sub sCopyRS()

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Shipping Details"
Const conWKB_NAME = "H:BIDataLoadSupplier Orders.xls"
  Set db = CurrentDb
  Set objXL = New Excel.Application
  Set rs = db.OpenRecordset("Shipping Details", dbOpenSnapshot)
  With objXL
    .Visible = False
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    intLastCol = objSht.UsedRange.Columns.Count
    With objSht
      .Range(.Cells(2, 1), .Cells(conMAX_ROWS, _
          intLastCol)).ClearContents
      .Range(.Cells(1, 1), _
        .Cells(1, rs.Fields.Count)).Font.Bold = False
      .Range("A2").CopyFromRecordset rs
      
        objXL.ActiveWorkbook.Save
        objXL.ActiveWorkbook.Close
        objXL.Quit
          
    End With
  End With
  Set objSht = Nothing
  Set objWkb = Nothing
  Set objXL = Nothing
  Set rs = Nothing
  Set db = Nothing
End Sub

Thanks
IMO




Hi All,
I hope you could help with this.

I am having a querry in access with 131000 lines, which obviously is too much for excel and for what I need, I would like a process which can cut it every 5000 or 4000 lines (if I can choose the amout better) the access doc and divide it in differents excel sheets.

Does anyone have any application/process/methode to run in access?

Thanks




Hello all,

I am having a querry in access with 131000 lines, which obviously is too much for excel and for what I need, I would like a process which can cut it every 5000 or 4000 lines (if I can choose the amout better) the access doc and divide it in differents excel sheets.

Does anyone have any application/process/methode to run in access?

Thanks




Hi
May somebody help me, I want to write sheet name in excel file with the item was choosed in combo box, when running docmd.outputTo

Please help
thanks




Hello

Is there an easy way to add grid lines to my report ?? (like in excel) I'm running Access 2003

Thank you

Alfred67




hi.. friends...

I m access user... i have made some queries.. & want to transfer data from different queries to on excel file in different sheets.. I have tried transfersheet macro... but... it transfers with one inverted coma in text column.. due to this the data exported to excel is no use to me.. as i m not able to upload the same to another application or even apply vlook in excel which is my basic purpose for doing this entire thing....

Can any body help me in this...




Hi all, hope everybody is cool

I am trying to extract each line of a memo field in an Access table into cells in excel.

Let's assume the text is in the format:

Status - For follow up []
Assigned To - Mr, Me []
Company - Wewkesbury Ltd []
Company Reg No - 5183464561 []

Where "[]" is a carriage return. How would I loop through each part of the text body? I would then increment a counter to output each line to a new cell.

Hope this makes sense!

Thanks.




Can any body point me in the right direction? I'm completely out of ideas.

I import an excel sheet with the following command

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "data", root & "tmp_Query_Output.xls", True

Works fine but for one issue.
I have 6 dates with time in this sheet. 4 come over fine 2 come over with only the date.

I do format the excel sheet seperately before the inputs to the proper date format. The database that receives the data is also set to general date.
But still 4 ok 2 only the date no time.

If I however attach the excel sheet to the db and then use an append query to the same table it works fine.
However I still would like the Transferspreadsheet cmd or at least understand why it doesn't work.

Any body??




Why does the following not delete the listed sheets in excel?

Private Sub cmdbtn_crewebbasexcgra_Click()
Dim xlSheet As Object, myfield As Variant, xlApp As Object, xlWork As Object, Strfile As String
Dim MyDb As Database, MyQuery As QueryDef, strSQL As String
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim Response

Strfile = "c:Documents and SettingsIce RhinoMy DocumentsWork RelatedD2KLiteWebGraphs.xls"

Set xlApp = CreateObject("Excel.Application")
Set xlWork = xlApp.Workbooks.Open(Strfile)
Set xlSheet = xlWork.Sheets("Date_Info")
myfield = Me!txt_dateheader
xlSheet.Cells(1, 1).Value = myfield


xlWork.Worksheets(4).Delete
xlWork.Worksheets(5).Delete
xlWork.Worksheets(6).Delete
xlWork.Worksheets(7).Delete

xlWork.Save
xlApp.Quit
Set xlSheet = Nothing
Set xlApp = Nothing
Set xlWork = Nothing


Anybody got any ideas?

IR




Originally posted under Macros and now I realize the answer is probably to use VBA. My problem is I have little to no knowledge of creating code.

Situation:
I have an export function that exports the contents of 11 different forms to 11 different Excel Spreadsheets on a regular basis. I use a Macro to do this repetitive function. I have turned off all the messages in Access but Excel puts up a Yes/No warning that the sheet already exists and asks if I want to overwrite it. Yes I do.

Issue:
How can I turn off these warnings from Excel in an Access 10 Macro?

I have little skill with VB but assume we could ask the Macro to run a piece of code to turn off all Messages in Excel and turn them back on at the end of the process. Further could I run a piece of code to start a specific excel Spreadsheet from the Macro as well. The path would be C:LDP_DatabaseSeneca_Period_Analysis_ExportsCom parative_Analysis.xlsx.

Any input would be greatly appreciated.

Jim




I am trying to place 3 stacked column charts into one single chart sheet, i have been able to do this using excel itself but i would like to automate the process from access.

has anyone done this before?

currently i am able to create the the required charts, but not able to add them into one single chart sheet here is some of my current code:

Set container = Charts.add
Set graph = Charts.add
graph.ChartType = xlColumnStacked
graph.SetSourceData Source:=ws.Range("$B$" & int_start & ":$E$" & int_end)
graph.PlotBy = xlColumns
graph.location Where:=xlLocationAsObject, Name:=container.Name

the last line constantly gives me an error, i recorded a macro in excel with the required process to have a look at the code but this did not help!

any help will be much appreciated...