Replies

It sounds like the NIC reset your connection settings.

Try this:

Control Panel / Internet Options
Connections Tab
Manually sort out the issue or use the Connection Wizard.

HTH

Try this:

Control Panel / Modems
Is the modem there?
yes Check the Diagnostics tab for port assignment. Try to use the manual modem install in the fax software.
no Add Modem button

HTH

You may be able to backtrack to the code source by looking at the active tasks with Ctl-Alt-Del when your firewall pops. Just a wild guess, but if I wanted to steal your machine, I would start a task that would use standard Windows services to call home.

HTH

By chance, did the problems occur just prior to or on an hour mark?

I have seen a network save operation crash with a swarm of disk I/O. Sometime this locks the file until the operating system recovers. Check for any scheduled but long forgotten autorun network tasks like defrag, backup, etc. that triggered your problem. On a network, you live and die with server ops.

Finished section of code to Set Directory with msgBox

With thanks to all

And a special hats off to Legare Coleman, who provided most of the code.

'Instruct user to GoTo target directory
    Msg = "On the next screen" & (Chr(10)) & (Chr(10)) & _
		 " Browse to the Line Card Directory" & _(Chr(10))  & _
		 "Then click the OPEN Button" & (Chr(10)) & (Chr(10)) & _
		"Are you ready to continue ?"  
		    Style = vbOKCancel + vbInformation + vbDefaultButton2
		    Title = "Capture Data From Line Cards"
		    Response = MsgBox(Msg, Style, Title)
    If Response = vbCancel Then                    'Skip the process
	        GoTo EndSub
    End If
'
'
'============= Heavy Lifing Section ===============
'
'Get current Worksheet Name. This is the sheet that collects the consolidated data
	IamWorkingHere = ActiveWorkbook.Name
'
'Get Directory name from msgBox string
Dim iLast As Integer
    strPathAndFile = Application.GetOpenFilename
        For I = Len(strPathAndFile) To 0 Step -1
            If Mid(strPathAndFile, I, 1) = "" Then
                iLast = I
        Exit For
            End If
        Next I
        strPath = Left(strPathAndFile, iLast)
    strFName = Dir(strPath & "*.xls", vbNormal)
'Cycle through all files in the target directory
    While strFName  ""
        Set oWB = Workbooks.Open(strPath & strFName)
'Feedback to user - "Progress Bar"
        Application.StatusBar = "Please be patient, _
				Processing file# " & NextRow & " " & strFName

    On Error Resume Next
'Turn Off Screen updates
    Application.ScreenUpdating = False

‘
‘
‘Lots of boring code goes here
‘
‘

NextRow is counter of data rows pasted into IamWorkingHere worksheet.
strFName is the current file that is being processed
The combination is quite impressive in action.

Hope this helps someone in a future project.

This is an Excel97 code fragment that may give you some ideas.
“Picture 1” is the Excel shape object. In my case there was only one shape object; a picture.

Sub PictureResizeAndCopy()

On Error GoTo NoPicture
ActiveSheet.Shapes(“Picture 1”).Select
Selection.ShapeRange.ScaleHeight 0.5, True change picture size
Selection.ShapeRange.ScaleWidth 0.5, True
Selection.Copy
||||||<———– Put the destination sheet code here
Application.Goto Reference:=”R1C1″ This is destination position of logo
ActiveSheet.Paste

NoPicture:

End Sub

HTH

In the project I am working on, the user has a series of files in a separate directory that will be processed for data extraction.

Through the msgBox, the user points to the first file in the target directory (not the current directory) to start the data extraction.

ActiveWorkbook.name, etc. does indeed Not get what I want because the msgBox return string is the FullName of a non-active workbook file.

This code is what I was looking for.

Thanks again to all.

“Here, you are measured not by what you keep, but what you give”

These two macros will put the sheet name in B1 and set the worksheet to print as one page.

Sub GetTabName()

Range(“B1”) = ActiveSheet.Name

End Sub

Sub OnePagePrint()

With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub

Drop Down Lists aren’t too hard.

Look in Help | index | drop-down lists, | data validation

Hope this helps.

[indent]

---

Steven Roman Writing Excel Macros, cheap, easy to read and thin! –Sam

---

[/indent]

Thanks,

Amazon actually has a used copy

Thanks for the tip.

The well is so deep and my dipper so small.

Do you have a favorite resource for Excel VBA programming?

Here is a fragment of the final code.

Thanks to all.

‘GoTo Diamonds Section
Range(“A7”).Select

Thanks for the beautiful piece of code but my description of the data wasn’t very good.

Sample sheet attached.

After looking at your code the answer jumped out

Dim xy1 as Range
Find Label1
Set xy1 = Label1
xy1.Offset(0,1) Do work on Data1
xy1.Offset(0,3) Do work on Data2
etc.

Thanks for the inspiration.
Sometimes the simplest solutions are the hardest to see.

Active cell is changed after I grab the first data field because I copy and paste the data field into a new row. I need some way of getting back to the “marching ants” cell to Offset to the next data field.

ActiveCell.Offset(0, 1).Select Offsets from pasted cell, not cell with “marching ants”

There are 1,290 Sheets, each with five general groups of information that loosely follow this format.
.row
.row
Label1 Data1 Label2 Data2 Label3 Data3
Label4 Data4 Label5 Data5
.row
.row ‘Inconsistant number of rows between groups
.row
Next Data Group
.row
etc… for five groups

The consolidation result is a new row that looks like this:

Data1 Data2 Data3 Data4 Data5….Data34

which gets copied to a consolidating worksheet.

Some other way to do this?

A bit of tinkering got me what I needed for the ItemPicture resize, copy, and paste.

Sub PictureResizeAndCopy()
‘
‘ PictureResizeAndCopy Macro
‘
‘
On Error GoTo NoPicture
ActiveSheet.Shapes(“Picture 1”).Select
Selection.ShapeRange.ScaleHeight 0.05, True
Selection.ShapeRange.ScaleWidth 0.05, True
Selection.Copy
Windows(“LineSheetData.xls”).Activate
Application.Goto Reference:=”R1C1″
ActiveSheet.Paste
NoPicture:
End Sub

Success is just a thousand iterations away…

Re:copying them all at once…
The target cells are now laid out to look good as a printed form and are checkerboarded all over the InventoryWorksheet page.

I was so fixated on looping through the files that I overlooked on last obvious problem.

Each InventoryWorksheet has an ItemPicture. This also has to come across to the CompilationWorksheet. And to further twist the knife, I would like to set the SizeProperty to 5% before I copy it making it LineHeight tall.

Ifexist(secret.code), print, else kill.me